Transform JSON element to SQL Table Format

Question

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

Now I need to access columns and values of the Tag "GetCustomReportResult" and transform into a SQL Table format.

The JSON String is actually stored in a column in a SQL Table as seen below and I am trying to transform the elements in the tag "GetCustomReportResult" in a table format with columns and values for each of the "ApplicationID":

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.

Please give a minimal reproducible example which should include at least one of the JSON rows as text not pictures. Please also show your full SQL query, and your expected results, again as text — Charlieface
– Charlieface, Commented Oct 23, 2021 at 18:38

Charlieface · Accepted Answer · 2021-10-23 18:49:32Z

1

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.

answered Oct 23, 2021 at 18:49

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transform JSON element to SQL Table Format

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related