Transform JSON element to SQL Table Format-CodePudding

This question is in relation to my previous problem which was solved.

Here is what I was trying to access the columns and values within the Tag "GetCustomReportResult":

SELECT 
y.cijreport,
y.ApplicationId,
JSON_VALUE(x.value, '$.CIP') as CIP,
JSON_VALUE(x.value, '$.CIQ') as CIQ
--other fields
FROM table as y
CROSS APPLY OPENJSON (cijreport) as x
where cijreport is not null

I now get this error when I execute:

Msg 13609, Level 16, State 2, Line 1 JSON text is not properly formatted. Unexpected character 'o' is found at position 0.

CodePudding user response：

Firstly, you are missing the JSON path '$.data.response'.

Next, you can't use JSON_VALUE on a whole object, it's only good for scalar values. You can either use JSON_QUERY:

SELECT 
  y.cijreport,
  y.ApplicationId,
  JSON_QUERY(x.value, '$.CIP') as CIP,
  JSON_QUERY(x.value, '$.CIQ') as CIQ, x.VALUE
--other fields
FROM YourTable as y
CROSS APPLY OPENJSON (cijreport, '$.data.response') as x;

Or you can specify property names in OPENJSON

SELECT 
  y.cijreport,
  y.ApplicationId,
  x.CIP,
  x.CIQ
--other fields
FROM YourTable as y
CROSS APPLY OPENJSON (cijreport, '$.data.response')
  WITH (
    CIP nvarchar(max) AS JSON,
    CIQ nvarchar(max) AS JSON
  ) AS x;

db<>fiddle

Note that the where cijreport is not null filter is not necessary, because CROSS APPLY OPENJSON will return 0 rows in such a case, and CROSS APPLY acts like an inner join.