Split json column

  • 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

     

     

  • 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.

  • 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.

     

  • 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.

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

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