I have the following json , when i am trying to extract it to sql i get empty.

I need CusP.id, CusP.custldId ,cusfield.id, value

DECLARE @json NVARCHAR(MAX);
    
SELECT  @json = '{
  "includ": {
    "cusP": {
      "542310": {
        "id": 542310,
        "custldId": 155,
        "cusfield": {
          "id": 155,
          "type": "custfi"
        },
        "projectId": 17435,
        "project": {
          "id": 17435,
          "type": "projects"
        },
        "value": "META DATA",
        "createdAt": "2022-01-16T05:11:20Z",
        "createdBy": 222222
      },
      "21000": {
        "id": 21000,
        "custldId": 426,
        "cusfield": {
          "id": 426,
          "type": "custfi"
        },
        "projectId": 786044,
        "project": {
          "id": 786044,
          "type": "projects"
        },
        "value": "delta55",
        "createdAt": "2022-01-17T10:03:07Z",
        "createdBy": 333333
      }
    }
  }
}'

This is what i am trying:

SELECT
    D.cusPid,
    d.[value],
    c.cusfieldid,
    cd.projectId 
FROM OPENJSON(@json, '$.includ.cusP')
WITH ( 
    cusPid NVARCHAR(max) '$.id',
    [value] NVARCHAR(max) '$.value'
) D 
CROSS APPLY OPENJSON(@json, '$.includ.cusP.custfi') 
WITH (
    cusfieldid VARCHAR(100) '$.id'
) C 
CROSS APPLY OPENJSON(@json, '$.includ.cusP.project') 
WITH ( 
    projectId VARCHAR(100) '$.id' 
) Cd;

that is the result i expect

CodePudding user response：

The problem is that the ID is also itself used as the key for a sub-property and OPENJSON does not allow variable paths (beyond arrays), so you need an extra level:

SELECT P.id AS cuspID, P.[value], P.cusfieldid, [projectId]
FROM OPENJSON(@json, '$.includ.cusP') J
CROSS APPLY OPENJSON(J.[value]) WITH ( 
    id INT,
    [value] NVARCHAR(MAX),
    [projectId] INT,
    cusfieldid INT '$.cusfield.id'
) P