Loading JSON file with nested elements into a T SQL table

Question

I'm not able to load the following JSON file into a T SQL table.

This is an extract of my JSON file:

[ {
  "REPAIR_TREE" : [ {
    "nodeId" : "N-21347",
    "level" : 1,
    "rank" : 1,
    "childNodes" : [ {
      "nodeId" : "N-21348",
      "level" : 2,
      "rank" : 8,
      "childNodes" : [ {
        "nodeId" : "N-21372",
        "childNodes" : [ ],
        "level" : 3,
        "rank" : 2,
        "label" : [ {
          "language" : "CS",
          "longLabel" : "Olej"
        }, {
          "language" : "DA",
          "longLabel" : "Olie"
... CONTINUES

Most likely I'm not referencing the Json elements correctly.

This is my query:

DROP TABLE IF EXISTS tab_ref03

DECLARE @JSON NVARCHAR(MAX)
SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'D:\input\Ref03_tree_structure.json',  
                 CODEPAGE='65001', SINGLE_CLOB) as j

SELECT * 
INTO tab_ref03 
FROM OPENJSON (@JSON) 
WITH
    (nodeId_03 NVARCHAR(20) '$.REPAIR_TREE.nodeId',
     level_03 NVARCHAR(20) '$.REPAIR_TREE.level',
     rank_03 NVARCHAR(20) '$.REPAIR_TREE.rank'
--  ... to be continued  with the other Json elements
    ) as ref03

SELECT * FROM tab_ref03

These are the results:

nodeId_03	level_03	rank_03
NULL	NULL	NULL

While I'm expecting:

nodeId_03	level_03	rank_03
N-21347	1	1

Answer 1

When specifying you want a value from an object, you need to specify what instance of that object you want; you don't here. JSON uses a 0 starting index, so you can achieve this by stating you want index 0 in your path:

DECLARE @YourJSON nvarchar(MAX) = N'[ {
  "REPAIR_TREE" : [ {
    "nodeId" : "N-21347",
    "level" : 1,
    "rank" : 1,
    "childNodes" : [ {
      "nodeId" : "N-21348",
      "level" : 2,
      "rank" : 8,
      "childNodes" : [ {
        "nodeId" : "N-21372",
        "childNodes" : [ ],
        "level" : 3,
        "rank" : 2,
        "label" : [ {
          "language" : "CS",
          "longLabel" : "Olej"
        }, {
          "language" : "DA",
          "longLabel" : "Olie"}] }]}]}]}]'

SELECT *
FROM OPENJSON(@YourJSON)
     WITH (nodeId varchar(10) '$.REPAIR_TREE[0].nodeId',
           level varchar(10) '$.REPAIR_TREE[0].level',
           rank varchar(10) '$.REPAIR_TREE[0].rank');

Alternatively, you can pull all the REPAIR_TREE as JSON and then read inside them:

SELECT RT.nodeId,
       RT.level,
       RT.rank
FROM OPENJSON(@YourJSON)
     WITH (REPAIR_TREE nvarchar(MAX) AS JSON) YJ
     CROSS APPLY OPENJSON(YJ.REPAIR_TREE)
                 WITH (nodeId varchar(10),
                       level varchar(10),
                       rank varchar(10)) RT;