Parse JSON document into table-CodePudding

I'm working on parsing JSON document with an object array into a SQL Server table. I'm stuck with how to parse the complex object array.

I tried subproject.id and that did not work. I usually use the below code to parse a JSON array which works just fine but in this case it does not.

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
     )

CodePudding user response：

You need to use AS JSON clause in the first OPENJSON() call (to specify that the $.subproject property contains an inner JSON array) and a combination of another OPENJSON() call and APPLY operator:

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