如何将含复杂对象数组的JSON解析到SQL Server表中？

我正在尝试将包含对象数组的JSON文档解析到SQL Server表中，但卡在了复杂对象数组的解析环节。曾尝试使用subproject.id但未能成功，平时适用的常规JSON数组解析代码在此次场景中无法生效。

JSON示例 #

{
    "Data": [        
        {
            "name": "ABC",
            "date": "2020-01-20",
            "subproject": [
                {
                    "id": "123",
                    "projectname": "new1",
                    "refnum": "123:new1"
                },
                {
                    "id": "456",
                    "projectname": "new2",
                    "refnum": "456:new2"
                }
            ],
            "projectid": "1234",
            "projectdate": "2020-01-27"
        },
        {
            "name": "DEF",
            "date": "2020-01-30",
            "subproject": [
                {
                    "id": "789",
                    "projectname": "new3",
                    "refnum": "789:new3"
                },
                {
                    "id": "901",
                    "projectname": "new4",
                    "refnum": "901:new4"
                }
            ],
            "projectid": "4567",
            "projectdate": "2020-02-07"
        }
    ]
}   

现有SQL代码 #

DECLARE @JSON VARCHAR(MAX)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'c:/data/project.json', SINGLE_CLOB) X

SELECT *
FROM OPENJSON (@JSON)
WITH (
     [name] NVARCHAR(256),
     [date] DATE,
     [projectid] INT,
     [projectdate] DATE
     )

原代码存在两个核心问题：

1. 未定位到JSON根节点下的Data数组，直接解析根节点无法读取内层业务数据
2. subproject是嵌套数组，不能通过subproject.id直接读取，需要用CROSS APPLY搭配OPENJSON做嵌套解析

以下是修正后的完整SQL代码，可同时解析外层Data对象和内层subproject数组的所有字段：

DECLARE @JSON VARCHAR(MAX)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'c:/data/project.json', SINGLE_CLOB) X

-- 先解析外层Data数组，再关联解析内层subproject数组
SELECT 
    o.[name],
    o.[date],
    o.projectid,
    o.projectdate,
    s.id AS subproject_id,
    s.projectname AS subproject_name,
    s.refnum AS subproject_refnum
FROM OPENJSON (@JSON, '$.Data') -- 精准定位到Data数组节点
WITH (
     [name] NVARCHAR(256),
     [date] DATE,
     projectid INT,
     projectdate DATE,
     subproject NVARCHAR(MAX) AS JSON -- 将subproject标记为JSON类型，供后续嵌套解析
     ) o
CROSS APPLY OPENJSON (o.subproject) -- 展开每个外层对象对应的subproject数组
WITH (
     id NVARCHAR(256),
     projectname NVARCHAR(256),
     refnum NVARCHAR(256)
     ) s

关键说明 #

• OPENJSON (@JSON, '$.Data')：指定解析根节点下的Data数组，这是读取内层业务对象的前提
• subproject NVARCHAR(MAX) AS JSON：把subproject字段标记为JSON类型，确保后续可以用OPENJSON解析其内部结构
• CROSS APPLY OPENJSON (o.subproject)：将每个外层对象的subproject数组展开，与外层字段关联生成扁平化结果集，直接适配SQL Server表的插入需求

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