JSON parsing

Transact-SQL
1

We have a table that has a JSON formatted column that contains an array of activities; with the codes and their description: [{"descrtiption":"some_text","code":"a_code"}, {"descrtiption":"some_other_text","code":"another_code"}, ].

The required result is a comma separated denormalised string that only lists the codes. The result of the given example would be 'a_code, another_code'.

We are using SQL Server 2017.

GO
DROP TABLE IF EXISTS dbo.DaTable;
GO 
CREATE TABLE dbo.DaTable(
	id	int		NOT NULL	PRIMARY KEY,
	json_column	NVARCHAR(512)	NOT NULL
);
GO
INSERT INTO dbo.DaTable(id, json_column) VALUES
(1, N'[{"description":"description A","code":"111"},{"description":"description B","code":"209.2.35"}]'),
(2, N'[{"description":"description C","code":"336.0"}]'), 
(3, N'[]')

The expected result for the example given is:

id codes
1 '111, 209.2.35'
2 '336.0'
3 ''

I have limited experience with parsing JSON columns. Here is a (fruitless) attempt to get all the id's and codes in a normalised format.

SELECT DT.id
	, DT.json_column
	, C.*
FROM dbo.DaTable DT
	CROSS APPLY OPENJSON(DT.json_column, '$.code' )
WITH (activity NVARCHAR(255) '$.description',
	code NVARCHAR(255) '$.code'
	) as C
2 
select code	   
  from DaTable c 
 CROSS APPLY OPENJSON(c.json_column)

WITH (code nvarchar(max) '$.code'
	  ) as jsonValues
2 Likes
3

hi Wim

hope this link helps .. if for SQL Server 2016 and later versions

4

Hi Yosiasz,

Oh. The solution is so simple. Thank you!

I used your solution to get the denormalised list business wants. I had to use OUTER APPLY as some arrays are empty '[]'.

WITH activities
AS (
	SELECT c.id
		,jsonvalues.code AS code
	FROM dbo.DaTable c
	OUTER APPLY OPENJSON(c.json_column) WITH (code NVARCHAR(max) '$.code') AS jsonValues
	)
SELECT DT.id 
	,codes = COALESCE(STRING_AGG(ACT.code, ', ') WITHIN GROUP (ORDER BY ACT.code), '')
FROM dbo.DaTable AS DT
LEFT OUTER JOIN activities AS ACT
	ON DT.id = ACT.id
GROUP BY DT.id

This results in:

id	codes
1	111, 209.2.35
2	336.0
3

This solution is far from efficient.

  • take a JSON list
  • convert that list to a normalised table (activities)
  • take the normalised table and denormalise it back to a comma separated list

Do you know how to parse the JSON column go to the required result in one step?

  • take a JSON list eg

[{"descrtiption":"some_text","code":"a_code"}, {"descrtiption":"some_other_text","code":"another_code"}, ]

  • extract only the codes, so the result is a comma separated string

'a_code, another_code'

5

Hey Harish,

The source data was not a comma separated list, but a JSON object.

One can use string function to parse a JSON object, but that soon becomes ugly and complicated. It's far better to use the provided JSON-function (once you know how to use them).

6

Why?

7

Why what?

8

Why take from json to comma delimited

;with src
as
(
select code, id 
  from DaTable c 
 CROSS APPLY OPENJSON(replace(c.json_column,'[]','[{}]'))
-- here above, I had to convert [] which seems like OPENJSON 
--does not recognize as a usable json array 
--(Why are you even saving that I am not sure?)

WITH (code nvarchar(max) '$.code'
	  ) as jsonValues
)
select id, STRING_AGG(CONVERT(nvarchar(max),ISNULL(src.code,'')), ',') 
WITHIN GROUP (ORDER BY code ASC) 
from src
group by id
1 Like