Convert json to comma separated string in SQL Server

Question

I have the following json passed into a prameter in SQL server 2016:

         [
                {
                    "item_name": "Settee"
                },
                {
                    "item_name": "Television"
                },
                {
                    "item_name": "Chair"
                }
        ]

How can I convert the item_name values into a comma separated string so that I get "Settee", "Television", "Chair" which I can then insert into a table column?

This is what I have but that just lists them in rows:

select *
 FROM OPENJSON(@json)
 WITH (
    item_name nvarchar(max) '$.item_name' 
)

*** UPDATE ***

It's ok I've figured it out.

 select items = stuff((
   SELECT ', ' + j.item_name
   FROM OPENJSON(@json)
    WITH (
      item_name nvarchar(max) '$.item_name' 
    ) j  FOR XML PATH('')
   ), 1, 1, '')

Consume the JSON as a data set (there are plenty of examples on how to do this) and then aggregate the string (again, there are plenty of examples how how to do this). What about all the examples didn't you understand? What were *your* attempt(s)? Why didn't they work? — Thom A, Jul 04 '22 at 16:16
What should happen if the strings contain quotes, or newlines, or other things that might break up the comma separation? — Jeroen Mostert, Jul 04 '22 at 16:21
Does this answer your question? [String\_agg for SQL Server before 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-before-2017) — Thom A, Jul 04 '22 at 16:31
Please don't add an answer within your question, add as an answer. — Dale K, Jul 04 '22 at 19:05

Yitzhak Khabinsky · Answer 1 · 2022-07-05T00:40:41.500

Please try the following solution.

It will work starting from MS SQL Server 2016 onwards.

SQL

DECLARE @json NVARCHAR(MAX) =
N'[
    {
        "item_name": "Settee"
    },
    {
        "item_name": "Television"
    },
    {
        "item_name": "Chair"
    }
]';

SELECT STUFF((
    SELECT ',' + QUOTENAME(item_name,'"') AS [text()] 
    FROM OPENJSON(@json)
    WITH (
        item_name nvarchar(max) '$.item_name' 
    )
    FOR XML PATH(''), TYPE
), 1,1,'') AS Result;

Output

+-------------------------------+
|            Result             |
+-------------------------------+
| "Settee","Television","Chair" |
+-------------------------------+

Your solution does not take into account XML escaping. You need `FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)'), 1,1,'')`. — Charlieface, Jul 04 '22 at 19:31

Convert json to comma separated string in SQL Server

1 Answers1