sql server – Convert JSON data to T-SQL

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.

Leave a Comment