Split json column

Question

Split json column

forumlogger

SSC Veteran

Points: 208
More actions
November 20, 2023 at 10:57 am

#4331002

I have a column which has the following data in it for example
{"changedAttributes":[{"logicalName":"line1","oldValue":"street1","newValue":"street2"},{"logicalName":"city","oldValue":"City1","newValue":"City2"},{"logicalName":"phone","oldValue":"123","newValue":"345"}]}
There maybe 100 logicaname values not just the three here
How do I write in sql a query to extract the logicalname, old value and new value for each logical name that exists in the column
Lets presume the table is as follows
table name: example
columns:
id: int,
jdata: json as above
thanks a lot

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

Something like this?

DROP TABLE IF EXISTS #SomeData;

CREATE TABLE #SomeData
(
    SomeCol NVARCHAR(MAX)
);

INSERT #SomeData
(
    SomeCol
)
VALUES
('{"changedAttributes":[{"logicalName":"line1","oldValue":"street1","newValue":"street2"},{"logicalName":"city","oldValue":"City1","newValue":"City2"},{"logicalName":"phone","oldValue":"123","newValue":"345"}]}');

SELECT sd.SomeCol,oj2.*
FROM #SomeData sd
CROSS APPLY OPENJSON(sd.SomeCol) WITH (changedAttributes NVARCHAR(MAX) AS JSON) oj
CROSS APPLY OPENJSON(oj.changedAttributes) WITH  (logicalName VARCHAR(100), oldValue VARCHAR(100), newValue VARCHAR(100)) oj2

This reply was modified 1 year ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

forumlogger SSC Veteran Points: 208 More actions · Answer 2

Thanks so much.. brilliant !

Is there a way of finding incorrect or badly formatted rows

Its shows most of the 30k rows but then comes up with an error

JSON text is not properly formatted. Unexpected character 's' is found at position 0.

Phil Parkin SSC Guru Points: 246970 More actions · Answer 3

Sure you can. If you add this WHERE clause to my query, it excludes any rows containing invalid JSON:

WHERE ISJSON(sd.SomeCol) = 1;

If you want to identify the invalid rows, just switch it to 0 rather than 1.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel