将JSON文档解析为表

Question

我正在使用对象数组将JSON文档解析为Server表。我一直在研究如何解析复杂的对象数组。

我试过subproject.id，但这不起作用。我通常使用下面的代码来解析一个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
     )

Answer 1

您需要在第一个AS JSON调用中使用OPENJSON()子句(指定$.subproject属性包含一个内部JSON数组)以及另一个OPENJSON()调用和APPLY操作符的组合：

SELECT *
FROM OPENJSON (@JSON, '$.Data') WITH (
   [name] NVARCHAR(256),
   [date] DATE,
   [projectid] INT,
   [projectdate] DATE,
   [subproject] NVARCHAR(MAX) '$.subproject' AS JSON  
) j1
OUTER APPLY OPENJSON (j1.[subproject]) WITH (
   [subprojectid] INT '$.id',
   [subprojectname] NVARCHAR(256) '$.projectname',
   [subprojectrefnum] NVARCHAR(256) '$.refnum'
) j2