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

   

   

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  November 20, 2023 at 11:53 am

  #4331042

  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, 5 months ago by  Phil Parkin.

  ---

• forumlogger

  SSC Veteran

  Points: 208

  More actions

  November 20, 2023 at 12:31 pm

  #4331076

  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: 247155

  More actions

  November 20, 2023 at 1:38 pm

  #4331107

  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.

  ---