SQL Server String Split and Agg function

Here is a short example. Let’s say we have this sample JSON output. We would like to split it on “}” characters;

DECLARE @json NVARCHAR(MAX) = '[{
  "text": "Books",
  "nodes": [{
	"text": "Programming Books",
	"rackNumber": "1",
	"moreInfo": [{
			"text": "C# book",
			"price": "$49.99"
		  },
		  {
			"text": "React book",
			"price": "$40.99"
		  }
		]},
		{
	"text": "Database Books",
	"rackNumber": "2",
	"moreInfo": [{
			"text": "SQL Server 2022",
			"price": "$52.99"
  		  },
		  {
			"text": "Maria database",
			"price": "$20.99"
		  }
		]}
	]
}]
'

SQL Server has some handy functions, STRING_Split, to split and to re-create, STRING_AGG. I am going to demo it here;

----split strings to make changes
IF OBJECT_ID('tempdb..#ADDF') IS NOT NULL DROP TABLE #ADDF
;WITH CTE AS
 (
	SELECT value  from STRING_Split(@json, '}')
 )
 SELECT * INTO #ADDF FROM CTE;
 //show me the JSON. The string should be split in two parts
 SELECT * FROM #ADDF

 //TODO..do whatever you want to do with the string JSON array

--return modified string. You should see a single line JSON output here 
SELECT STRING_AGG(value, '}') AS jsonBack
FROM #ADDF;

Happy coding.

Add to favorites