How to get data from json column in SQL Server that starts with array element

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'
            );

Answer

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

Example db<>fiddle

Advertisement

Answer