Home > Back-end >  How to get data from json column in SQL Server that starts with array element
How to get data from json column in SQL Server that starts with array element

Time:03-12

I have a database table that I need to extract data from where the column of interest has json it. What makes this particularly difficult is the most outer elements of the json is '[' & ']' as in the parent element is an array. I need to get the value associated with key 'Name' (which in this case is 'MS220'). However, I'm not able to path correctly to the key I want.

The below JData table is a duplicate copy of the data I need to perform the extract on. Between SELECT OPENJSON, JSON_VALUE, JSON_QUERY etc., how can I retrieve the value I'm looking for?

Below is a couple of selects I've tried but not quite getting it.

CREATE TABLE JData 
(
   JsonData nvarchar(max)
)

INSERT INTO JData (JsonData)
VALUES 
   ('[
    {
        "Categories": [
            {
                "QuerySourceNames": [
                    "QAsset"
                ],
                "Id": "eceae85a-ffc6-49f4-8f6a-78ce2b4b274e",
                "Name": "emsdba"
            }
        ],
        "Id": "525b4f07-0f67-43ac-8070-a0e6c1ceb1b9",
        "Name": "MS220"
    }
]')

SELECT *
FROM OPENJSON (JData,'$.[0]')
WITH (
    [Name] varchar(10) '$.Name'
)

SELECT 
    JSON_VALUE(JData,'$') as v

@AaronBertrand: I had to modify the answer a little since the table also has a column labeled [name] as well. Is there a way to UPDATE ParamName to a new value?

SELECT 
    t.[Name],
    ParamName
FROM 
    [myDB].[dbo].[myTable] t
CROSS APPLY  
    OPENJSON (t.params)
        WITH 
            (
              Categories nvarchar(max) AS json,
              Id uniqueidentifier,
              ParamName varchar(10) '$.Name'
            );

CodePudding user response:

SELECT Name FROM dbo.JData 
CROSS APPLY OPENJSON (JsonData)
WITH 
(
  Categories nvarchar(max) AS json,
  Id uniqueidentifier,
  [Name] varchar(10)
);
  • Related