无法将含嵌套元素的JSON文件加载至T SQL表

无法将带有嵌套元素的JSON文件加载到T-SQL表中，以下是JSON文件片段：

[ {
  "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"
        } ]
      } ]
    } ]
  } ]
} ]

我的查询语句如下：

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

查询结果返回全NULL：

预期结果应该是：

解决方案 #

问题出在JSON路径引用错误：你的JSON最外层是数组，每个元素里的REPAIR_TREE也是数组，直接用$.REPAIR_TREE.nodeId无法定位到具体元素，因为数组需要通过索引或者展开来访问。

方式一：通用解法（支持REPAIR_TREE多元素） #

使用CROSS APPLY先展开外层数组，再解析REPAIR_TREE数组中的每个元素：

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 rt.* 
INTO tab_ref03 
FROM OPENJSON (@JSON) 
CROSS APPLY OPENJSON (value, '$.REPAIR_TREE')
WITH
(
    nodeId_03 NVARCHAR(20) '$.nodeId',
    level_03 NVARCHAR(20) '$.level',
    rank_03 NVARCHAR(20) '$.rank'
) as rt

SELECT * FROM tab_ref03

方式二：针对REPAIR_TREE仅含单个元素的场景 #

直接通过数组索引定位REPAIR_TREE的第一个元素：

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[0].nodeId',
    level_03 NVARCHAR(20) '$.REPAIR_TREE[0].level',
    rank_03 NVARCHAR(20) '$.REPAIR_TREE[0].rank'
) as ref03

SELECT * FROM tab_ref03

两种方式都能正确返回预期结果，推荐使用方式一以兼容REPAIR_TREE包含多个节点的情况。

内容的提问来源于stack exchange，提问作者Luke