I am new to SQL Server querying and need some help with querying the following JSON file in T-SQL.
This is my JSON:
{
"12": "GUARDIAN COURT",
"13": "",
"14": "",
"15": "FERRES STREET",
"16": "HEREFORD",
"17": "",
"18": "HR1 2LP",
"19": "20001",
"20": {
"20[1_Asbestos_ID]": "2000131/2",
"20[<Row_2>2<Col>Asbestos_ID]": "2000132/3",
"20[<Row_3>3<Col>Asbestos_ID]": "2000132/1",
"20[<Row_4>4<Col>Asbestos_ID]": "2000135/3",
"20[<Row_5>5<Col>Asbestos_ID]": "2000135/1",
"20[<Row_6>6<Col>Asbestos_ID]": "2000136/3",
"20[<Row_7>7<Col>Asbestos_ID]": "2000136/1",
"20[<Row_8>8<Col>Asbestos_ID]": "2000137/3",
"20[<Row_9>9<Col>Asbestos_ID]": "2000137/1",
"20[<Row_10>10<Col>Asbestos_ID]": "2000138/3",
"20[<Row_11>11<Col>Asbestos_ID]": "2000138/1",
"20[<Row_12>12<Col>Asbestos_ID]": "2000130/2",
"20[<Row_13>13<Col>Asbestos_ID]": "2000133/2",
"20[<Row_14>14<Col>Asbestos_ID]": "2000134/2",
"20[<Row_15>15<Col>Asbestos_ID]": "20001/6",
"20[<Row_16>16<Col>Asbestos_ID]": "20001/5",
"20[<Row_17>17<Col>Asbestos_ID]": "20001/4",
"20[<Row_18>18<Col>Asbestos_ID]": "20001/3",
"20[1_Location_Level]": "Ground Floor",
"20[<Row_2>2<Col>Location_Level]": "Ground Floor",
"20[<Row_3>3<Col>Location_Level]": "Ground Floor",
"20[<Row_4>4<Col>Location_Level]": "First Floor",
"20[<Row_5>5<Col>Location_Level]": "First Floor",
"20[<Row_6>6<Col>Location_Level]": "Second Floor",
"20[<Row_7>7<Col>Location_Level]": "Second Floor",
"20[<Row_8>8<Col>Location_Level]": "Second Floor",
"20[<Row_9>9<Col>Location_Level]": "Second Floor",
"20[<Row_10>10<Col>Location_Level]": "Second Floor",
"20[<Row_11>11<Col>Location_Level]": "Second Floor",
"20[<Row_12>12<Col>Location_Level]": "Ground Floor",
"20[<Row_13>13<Col>Location_Level]": "First Floor",
"20[<Row_14>14<Col>Location_Level]": "First Floor",
"20[<Row_15>15<Col>Location_Level]": "First Floor",
"20[<Row_16>16<Col>Location_Level]": "Second Floor",
"20[<Row_17>17<Col>Location_Level]": "Ground Floor",
"20[<Row_18>18<Col>Location_Level]": "Exterior to the Building",
"20[1_Material_Type]": "Textured coatings and paints",
"20[<Row_2>2<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_3>3<Col>Material_Type]": "Boxing around pipes/ducting",
"20[<Row_4>4<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_5>5<Col>Material_Type]": "Boxing around pipes/ducting",
"20[<Row_6>6<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_7>7<Col>Material_Type]": "Boxing around pipes/ducting",
"20[<Row_8>8<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_9>9<Col>Material_Type]": "Boxing around pipes/ducting",
"20[<Row_10>10<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_11>11<Col>Material_Type]": "Boxing around pipes/ducting",
"20[<Row_12>12<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_13>13<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_14>14<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_15>15<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_16>16<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_17>17<Col>Material_Type]": "Textured coatings and paints",
"20[<Row_18>18<Col>Material_Type]": "Cement pipes in risers",
"20[1_Location_Description]": "Flat",
"20[<Row_2>2<Col>Location_Description]": "Flat",
"20[<Row_3>3<Col>Location_Description]": "Kitchen",
"20[<Row_4>4<Col>Location_Description]": "Flat",
"20[<Row_5>5<Col>Location_Description]": "Kitchen",
"20[<Row_6>6<Col>Location_Description]": "Flat",
"20[<Row_7>7<Col>Location_Description]": "Loft",
"20[<Row_8>8<Col>Location_Description]": "Flat",
"20[<Row_9>9<Col>Location_Description]": "Loft",
"20[<Row_10>10<Col>Location_Description]": "Flat",
"20[<Row_11>11<Col>Location_Description]": "Loft",
"20[<Row_12>12<Col>Location_Description]": "Flat",
"20[<Row_13>13<Col>Location_Description]": "Flat",
"20[<Row_14>14<Col>Location_Description]": "Flat",
"20[<Row_15>15<Col>Location_Description]": "Landing (first floor)",
"20[<Row_16>16<Col>Location_Description]": "Landing (second floor)",
"20[<Row_17>17<Col>Location_Description]": "Entrance",
"20[<Row_18>18<Col>Location_Description]": "Extenal",
"20[1_Press_To_Open]": {
"20[3]": "Ground Floor",
"20[4]": "Flat",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "45",
"20[150]": "Number or Count",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "2"
},
"20[<Row_2>2<Col>Press_To_Open]": {
"20[3]": "Ground Floor",
"20[4]": "Flat",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "45",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "2"
},
"20[<Row_3>3<Col>Press_To_Open]": {
"20[3]": "Ground Floor",
"20[4]": "Kitchen",
"20[6]": "Boxing around pipes/ducting",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "4",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "2-Asbestos Insulating Boards etc.",
"20[155]": "0-Good Condition",
"20[159]": "1-Enclosed sprays and lagging etc.",
"20[17]": "1-Enclosed sprays and lagging etc.",
"20[18]": "2-Amphibole asbestos excluding crocidolite",
"20[164]": "5"
},
"20[<Row_4>4<Col>Press_To_Open]": {
"20[3]": "First Floor",
"20[4]": "Flat",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "45",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "2"
},
"20[<Row_5>5<Col>Press_To_Open]": {
"20[3]": "First Floor",
"20[4]": "Kitchen",
"20[6]": "Boxing around pipes/ducting",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "4",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "2-Asbestos Insulating Boards etc.",
"20[155]": "0-Good Condition",
"20[159]": "1-Enclosed sprays and lagging etc.",
"20[17]": "1-Enclosed sprays and lagging etc.",
"20[18]": "2-Amphibole asbestos excluding crocidolite",
"20[164]": "5"
},
"20[<Row_6>6<Col>Press_To_Open]": {
"20[3]": "Second Floor",
"20[4]": "Flat",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "45",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "2"
},
"20[<Row_7>7<Col>Press_To_Open]": {
"20[3]": "Second Floor",
"20[4]": "Loft",
"20[6]": "Boxing around pipes/ducting",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "2",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "1-Enclosed sprays and lagging etc.",
"20[17]": "1-Enclosed sprays and lagging etc.",
"20[18]": "2-Amphibole asbestos excluding crocidolite",
"20[164]": "4"
},
"20[<Row_8>8<Col>Press_To_Open]": {
"20[3]": "Second Floor",
"20[4]": "Flat",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "45",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "2"
},
"20[<Row_9>9<Col>Press_To_Open]": {
"20[3]": "Second Floor",
"20[4]": "Loft",
"20[6]": "Boxing around pipes/ducting",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "4",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "2-Asbestos Insulating Boards etc.",
"20[155]": "0-Good Condition",
"20[159]": "1-Enclosed sprays and lagging etc.",
"20[17]": "1-Enclosed sprays and lagging etc.",
"20[18]": "2-Amphibole asbestos excluding crocidolite",
"20[164]": "5"
},
"20[<Row_10>10<Col>Press_To_Open]": {
"20[3]": "Second Floor",
"20[4]": "Flat",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "45",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "2"
},
"20[<Row_11>11<Col>Press_To_Open]": {
"20[3]": "Second Floor",
"20[4]": "Loft",
"20[6]": "Boxing around pipes/ducting",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "2",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "1-Enclosed sprays and lagging etc.",
"20[17]": "1-Enclosed sprays and lagging etc.",
"20[18]": "2-Amphibole asbestos excluding crocidolite",
"20[164]": "4"
},
"20[<Row_12>12<Col>Press_To_Open]": {
"20[3]": "Ground Floor",
"20[4]": "Flat",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "50",
"20[150]": "Other, see notes",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "2"
},
"20[<Row_13>13<Col>Press_To_Open]": {
"20[3]": "First Floor",
"20[4]": "Flat",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "50",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "2"
},
"20[<Row_14>14<Col>Press_To_Open]": {
"20[3]": "First Floor",
"20[4]": "Flat",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "45",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "0-Good Condition",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "2"
},
"20[<Row_15>15<Col>Press_To_Open]": {
"20[3]": "First Floor",
"20[4]": "Landing (first floor)",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "6",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "1-Low Damage",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "3"
},
"20[<Row_16>16<Col>Press_To_Open]": {
"20[3]": "Second Floor",
"20[4]": "Landing (second floor)",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "30",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "1-Low Damage",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "3"
},
"20[<Row_17>17<Col>Press_To_Open]": {
"20[3]": "Ground Floor",
"20[4]": "Entrance",
"20[6]": "Textured coatings and paints",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "4",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "1-Low Damage",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "3"
},
"20[<Row_18>18<Col>Press_To_Open]": {
"20[3]": "Exterior to the Building",
"20[4]": "Extenal",
"20[6]": "Cement pipes in risers",
"20[7]": "Identified",
"20[8]": "Accessible for inspection",
"20[10]": "",
"20[11]": "0.5",
"20[150]": "Metres Square",
"20[12]": "",
"20[165]": "1-Asbestos reinforced composites",
"20[155]": "1-Low Damage",
"20[159]": "0-Composite materials etc.",
"20[17]": "0-Composite materials etc.",
"20[18]": "1-Chrysotile",
"20[164]": "3"
}
}
}
And this is my T-SQL code:
DECLARE @RecId BIGINT = 523237
DECLARE @FilledFormData nvarchar(MAX) = (SELECT 'JsonData'
FROM 'TableName'
WHERE 'FName' = ''
AND 'ID' = @RecId);
DECLARE @nonGridConfigTable TABLE
( answerLabel NVARCHAR(500)
, answer NVARCHAR(MAX)
, RowNumber INT
)
INSERT @nonGridConfigTable
SELECT
CASE
WHEN v.[Key] LIKE '%Asbestos_ID%' THEN 'Asbestos_ID'
WHEN v.[Key] LIKE '%Location_Level%' THEN 'Location_Level'
WHEN v.[Key] LIKE '%Material_Type%' THEN 'Material_Type'
WHEN v.[Key] LIKE '%Location_Description%' THEN 'Location_Description' ELSE v.[Key]
END AS [AnswerKey],
v.[Value] AS [Answer],
ROW_NUMBER() OVER (PARTITION BY v.[Value] ORDER BY v.[Key]) AS [RowNum]
FROM
OPENJSON(@FilledFormData) ffd
CROSS APPLY
OPENJSON(ffd.[Value]) AS v
WHERE
ffd.[Key] IN ('20')
SELECT *
FROM @nonGridConfigTable
Using this query, I can get the values from the non-nested section.
I am struggling with nested section "20[1_Press_To_Open]": nested section any help would be highly appreciated.
CodePudding user response：
It soesn' differ from that what you already have. You must add another branch.
DECLARE @json varchar(100); SET @json = '{"20[1_Press_To_Open]": { "20[3]": "Ground Floor", "20[4]": "Flat", "20[6]": "Textured coatings and paints", "20[7]": "Identified", "20[8]": "Accessible for inspection", "20[10]": "", "20[11]": "45", "20[150]": "Number or Count", "20[12]": "", "20[165]": "1-Asbestos reinforced composites", "20[155]": "0-Good Condition", "20[159]": "0-Composite materials etc.", "20[17]": "0-Composite materials etc.", "20[18]": "1-Chrysotile", "20[164]": "2" } }'; SELECT JSON_VALUE(@json,'$."20[1_Press_To_Open]"."20[3]"') AS Name GO| Name | | :----------- | | Ground Floor |
db<>fiddle here
CodePudding user response：
This this dyn sql with your @FilledFormData variable containing input JSON
DECLARE @Columns NVARCHAR(MAX)
, @SQL NVARCHAR(MAX)
SELECT @Columns = STRING_AGG(QUOTENAME(s.[key]), ',') WITHIN GROUP (ORDER BY s.[key])
FROM (
SELECT DISTINCT t.[key]
FROM OPENJSON(@FilledFormData, '$."20"') ffd
CROSS APPLY OPENJSON(ffd.value) t
WHERE ffd.type = 5
) s
SET @SQL = N'
SELECT p.*
FROM (
SELECT ffd.[key] AS parent
, t.[key]
, t.value
FROM OPENJSON(@FilledFormData, ''$."20"'') ffd
CROSS APPLY OPENJSON(ffd.value) t
WHERE ffd.type = 5
) s
PIVOT (
MAX(s.value)
FOR s.[key] IN (' @Columns ')
) p'
EXEC dbo.sp_executesql @SQL, N'@FilledFormData NVARCHAR(MAX)', @FilledFormData