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)
);
- Example db<>fiddle