Concatenate/aggregate strings with JSON in SQL Server

Question

This might be a simple question for those who are experienced in working with JSON in SQL Server. I found this interesting way of aggregating strings using FOR XML in here.

create table #t (id int, name varchar(20))

insert into #t
values (1, 'Matt'), (1, 'Rocks'), (2, 'Stylus')

select  id
    ,Names = stuff((select ', ' + name as [text()]
    from #t xt
    where xt.id = t.id
    for xml path('')), 1, 2, '')
from #t t
group by id

How can I do the same using JSON instead of XML?

I'm baffled. Why would you want to do that? If you are using the more recent versions of SQL Server, then forget this and use `string_agg()`. Aggregate string concatenation should really be a basic function in SQL. Oh wait. It *is* in the standard, but SQL Server has chosen a different name. — Gordon Linoff, Dec 11 '19 at 17:17
In SQL Server 2017 and later, use STRING_AGG. In previous versions, using JSON is little better than using XML. Why do you want JSON specifically? PS: there are duplicates for this already — Panagiotis Kanavos, Dec 11 '19 at 17:19
@GordonLinoff can you point to the standard? (Intentional pun, knowing you have to pay to get the standard, but who knows) — Panagiotis Kanavos, Dec 11 '19 at 17:20
@GordonLinoff based on [this article](https://modern-sql.com/feature/listagg) it looks like LISTAGG is one of those functions with really bad conformance across all databases. Which isn't that rare with SQL — Panagiotis Kanavos, Dec 11 '19 at 18:00
@GordonLinoff I'm using 2016. It doesn't have the STRING_AGG function but has JSON. I want to know which one performs better and I guess JSON would be the answer. — TheEsnSiavashi, Dec 11 '19 at 18:50
@TheEsnSiavashi . . . Although you could -- no doubt -- adapt the solution for JSON, why bother? The XML solution has always been a work-around. It was developed before SQL Server supported JSON. Eventually, you'll upgrade the database and you can then use the correct solution, the built-in function designed for this purpose. — Gordon Linoff, Dec 11 '19 at 18:55

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

You cannot replace the XML approach with JSON. This string concatenation works due to some XML inner peculiarities, which are not the same in JSON.

Starting with SQL Server 2017 onwards you can use STRING_AGG(), but with earlier versions, the XML approach is the way to go.

Some background and a hint

First the hint: The code you showed is not safe for the XML special characters. Check my example below.

First I declare a simple XML

DECLARE  @xml XML=
N'<a>
  <b>1</b>
  <b>2</b>
  <b>3</b>
  <c>
    <d>x</d>
    <d>y</d>
    <d>z</d>
  </c>
</a>';

--The XPath . tells the XML engine to use the current node (and all within)
--Therefore this will return any content within the XML

SELECT @xml.value('.','varchar(100)')

--You can specify the path to get 123 or xyz

SELECT @xml.query('/a/b').value('.','varchar(100)')
SELECT @xml.query('//d').value('.','varchar(100)')

Now your issue to concatenate tabular data:

DECLARE @tbl TABLE(SomeString VARCHAR(100));
INSERT INTO @tbl VALUES('This'),('will'),('concatenate'),('magically'),('Forbidden Characters & > <');

--The simple FOR XML query will tag the column with <SomeString> and each row with <row>:

SELECT SomeString FROM @tbl FOR XML PATH('row');

--But we can create the same without any tags:
--Attention: Look closely, that the result - even without tags - is XML typed and looks like a hyper link in SSMS.

SELECT SomeString AS [*] FROM @tbl FOR XML PATH('');

--Now we can use as a sub-select within a surrounding query.
--The result is returned as string, not XML typed anymore... Look at the forbidden chars!

SELECT
 (SELECT SomeString FROM @tbl FOR XML PATH('row'))
,(SELECT SomeString AS [*] FROM @tbl FOR XML PATH(''))

--We can use ,TYPE to enforce the sub-select to be treated as XML typed itself
--This allows to use .query() and/or .value()

SELECT
 (SELECT SomeString FROM @tbl FOR XML PATH('row'),TYPE).query('data(//SomeString)').value('.','nvarchar(max)')
,(SELECT SomeString AS [*] FROM @tbl FOR XML PATH(''),TYPE).value('.','nvarchar(max)')

XQuery's .data() can be used to concatenate named elements with blanks in between.
XQuery's .value() must be used to re-escpae forbidden characters.

Good explanation. But I'm confused as whats the answer to my question. Because u say we cannot do the same with JSON but @GordonLinoff says "you could -- no doubt -- adapt the solution for JSON" in his above comment. — TheEsnSiavashi, Dec 12 '19 at 05:46
@TheEsnSiavashi There is no Json without item names. The rest would be ugly string manipulations... — Shnugo, Dec 12 '19 at 05:59
This is a great explanation, but it doesn't work if `SomeString` contains characters that are invalid in XML. The `, TYPE` conversion fails with an error. — GPHemsley, Jul 31 '20 at 17:19
@GPHemsley In this case you can wrap `SomeString` with `select some string as [*] for xml path('') `. This will do all the escaping implicitly. — Shnugo, Jul 31 '20 at 18:48

Concatenate/aggregate strings with JSON in SQL Server

1 Answers1

Some background and a hint