How Can I use OpenJson to access double nested arrays IN T-SQL?

Question

I have a JSON file listed below and I am having issues using OPENJSON to get access to the Misc field when the CheckboxValue is true.

So the example below would return a table:

Company ReferenceDuplicate  Type    ReferenceNumber
----------------------------------------------------    
TTT     1                    CD     1034036

So far I have tried to use a loop to generate the query then use a PIVOT to get it into the rows. But it gives me an Incorrect syntax near @query. Also I still have the issue of not being able to get values where the "CheckboxValue" is true

[
  [
    {
      "ControlType": "60",
      "ControlCaption": "1034036",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "19/10/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "CD",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "1034036",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "18/11/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "373.64",
      "ControlStyle": "width:100%; padding:0px; text-align: right;",
      "IsTableItem": true
    },
    {
      "ControlType": "20",
      "LabelStyle": "display:none",
      "CheckboxValue": true,
      "ControlStyle": "text-align:left",
      "IsTableItem": true,
      "CheckboxIconSize": "15px",
      "Misc": {
        "Company": "TTT",
        "ReferenceDuplicate": "1",
        "Type": "CD",
        "ReferenceNumber": "1034036"
      }
    }
  ],
  [
    {
      "ControlType": "60",
      "ControlCaption": "1035375",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "27/10/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "CD",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "1035375",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "26/11/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "20.24",
      "ControlStyle": "width:100%; padding:0px; text-align: right;",
      "IsTableItem": true
    },
    {
      "ControlType": "20",
      "LabelStyle": "display:none",
      "CheckboxValue": false,
      "ControlStyle": "text-align:left",
      "IsTableItem": true,
      "CheckboxIconSize": "15px",
      "Misc": {
        "Company": "TTT",
        "ReferenceDuplicate": "1",
        "Type": "CD",
        "ReferenceNumber": "1035375"
      }
    }
  ],
  [
    {
      "ControlType": "60",
      "ControlCaption": "1036326",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "02/11/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "CD",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "1036326",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "02/12/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "57.24",
      "ControlStyle": "width:100%; padding:0px; text-align: right;",
      "IsTableItem": true
    },
    {
      "ControlType": "20",
      "LabelStyle": "display:none",
      "CheckboxValue": false,
      "ControlStyle": "text-align:left",
      "IsTableItem": true,
      "CheckboxIconSize": "15px",
      "Misc": {
        "Company": "TTT",
        "ReferenceDuplicate": "1",
        "Type": "CD",
        "ReferenceNumber": "1036326"
      }
    }
  ]
]

score 2 · Accepted Answer · answered Mar 30 '22 at 07:20

Use an open_json to get a table of arrays and then cross apply against those values.

declare @S nvarchar(max) = 'JSON GOES HERE ...';


select json_value(A2.value, '$.Misc.Company') as Company,
       json_value(A2.value, '$.Misc.ReferenceDuplicate') as ReferenceDuplicate,
       json_value(A2.value, '$.Misc.Type') as Type,
       json_value(A2.value, '$.Misc.ReferenceNumber') as ReferenceNumber
from openjson(@S) as A1
  cross apply openjson(A1.value) as A2
where json_value(A2.value, '$.CheckboxValue') = 'true';

Or you can do the same using a with clause to specify the datatypes.

select A2.Company,
       A2.ReferenceDuplicate,
       A2.Type,
       A2.ReferenceNumber
from openjson(@S) as A1
  cross apply openjson(A1.value)
    with (
           CheckboxValue bit,
           Company varchar(100) '$.Misc.Company',
           ReferenceDuplicate int '$.Misc.ReferenceDuplicate',
           Type varchar(100) '$.Misc.Type',
           ReferenceNumber int '$.Misc.ReferenceNumber'
         ) as A2
where A2.CheckboxValue = 1;

How Can I use OpenJson to access double nested arrays IN T-SQL?

1 Answers1