how to get the values of the each object from the JSON in SQL SERVER

Question

DECLARE @json NVARCHAR(1000);
            
    SELECT @json = N'
       {
        "nodeinid": 1345,
        "data": [
          {
            "nodenametext": "Plan for project",
            "nodedesctext": "plan description for execution",
            "nodetypecode": "PLN",
            "statuscode": "IP",
            "prioritycode": "NI",
            "linkeditemid": 0,
            "linkeditemtypecode": "TSK",
            "startdttm": "2021-07-15T09:53:47.9575771",
            "duedttm": "2021-07-15T09:53:47.9575771",
            "hidefromclientindc": "true",
            "draftindc": "no",
            "inworkspaceid": 5678,
            "parentnodeid": 0,
            "categorytext": [
              {
                "categoryid": 111,
                "categoryidname": "college"
              },
              {
                "categoryid": 222,
                "categoryidname": "office"
              }
            ],
            "assigntotext": [
              {
                "workspaceid": 567,
                "roleid": 4545,
                "accesstypecode": "ass",
                "mailurl": "saram@gmail"
              },
              {
                "workspaceid": 977,
                "roleid": 67,
                "accesstypecode": "ass",
                "mailurl": "sarfdam@gmail"
              }
            ]
          }
        ]
      }
    ';

query used:

    SELECT JSON_Value (c.value, '$.nodeinid') as nodein_id 
       ,JSON_Value (P.Value, '$.workspaceid') as workspace_id
     , JSON_Value (p.value, '$.accesstypecode') as accesstype_code
     , JSON_Value (Q.value, '$.categoryid') as category_id
     , JSON_Value (Q.value, '$.categoryidname') as categoryid_name
      
    FROM OPENJSON (@json, '$.data') as C
    CROSS APPLY OPENJSON (C.Value, '$.assigntotext') as P
    CROSS APPLY OPENJSON (C.Value, '$.categorytext') as Q;

expected sample ouput:

workspace_id accesstype_code
567          ass
977          ass  

Result: query throws the below error

Msg 13609, Level 16, State 4, Line 50
JSON text is not properly formatted. Unexpected character '"' is found at position 998.

if i keep either one object (assigntotext,categorytext)it works fine.
please correct the query to get the values in the rows and columns format."

Answer 1

It seems that nvarchar(1000) is not enough and the input JSON is truncated. Change the data type of the @json variable to nvarchar(max). Also you may try to parse the input JSON with:

DECLARE @json NVARCHAR(max);
SELECT @json = N'... JSON data ...' 

SELECT j2.*
FROM OPENJSON(@json, '$.data') WITH (
   assigntotext nvarchar(max) '$.assigntotext' AS JSON
) j1
OUTER APPLY OPENJSON(j1.assigntotext) WITH (
   workspace_id int '$.workspaceid',
   accesstype_code varchar(10) '$.accesstypecode'
) j2

Result:

workspace_id accesstype_code
----------------------------
567          ass
977          ass