Split json column

Question

Split json column

forumlogger

SSC Journeyman

Points: 98
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: 246437 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 2 weeks, 2 days ago by Phil Parkin.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

forumlogger SSC Journeyman Points: 98 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: 246437 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.