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

Question

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

Expected output = "MS220"

user17176542
– user17176542

2022-03-12 00:55:28 +00:00
Commented Mar 12, 2022 at 0:55 — user17176542
– user17176542, Commented Mar 12, 2022 at 0:55

Aaron Bertrand · Accepted Answer · 2022-03-12 01:06:30Z

1

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

Example db<>fiddle

answered Mar 12, 2022 at 1:06

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related