How to convert JSON to array of strings

Question

Is it possible to use for json path to format rows in a JSON array format?

I have a column like this

Col1
====
abc
def
ghi
jkl

and I want to format it like this

{"Col1":["abc","def","ghi","jkl"]}

I have so far gotten it to look like this

{["Col1":"abc","Col1":"def","Col1":"ghi","Col1":"jkl"]}

using this code

select col1 from table for json path

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Dec 28, 2021 at 18:44

John Cappelletti · Accepted Answer · 2021-12-28 19:37:51Z

4

With a bit of string manipulation.

Creating a simple array seems like a missed opportunity.

Select Col1 = json_query('["'+string_agg(string_escape(Col1,'json'), '","') +'"]')
 From  YourTable
  For  json path, Without_Array_Wrapper

Results

{"Col1":["abc","def","ghi","jkl"]}

If your version is <2017, string_agg() would not be available. However, you can use the stuff()/xml approach

Select Col1 = json_query('["'+stuff((Select concat('","',string_escape(Col1,'json'))  
                                      From  YourTable 
                                      For XML Path ('')),1,3,'')+'"]')
   For  json path, Without_Array_Wrapper

edited Dec 28, 2021 at 19:37

answered Dec 28, 2021 at 19:01

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Thom A Over a year ago

I won't lie, it feels like there should be a less clunky method, but I can't think of one.

Yitzhak Khabinsky Over a year ago

SQL Server 2022 is promising to enhance JSON API functionality.

Mindstormer Over a year ago

Sad Guess I just have to jank my way through it lol!

Charlieface Over a year ago

For XML Path (''), TYPE).value('text()[1]','nvarchar(max)') would be wise, to avoid XML escaping

Charlieface Over a year ago

@Larnu Two things that are really badly missing about SQL Server's JSON functionality: JSON_AGG as here, and JSON_OBJECT_AGG for property/value pairs

Collectives™ on Stack Overflow

How to convert JSON to array of strings

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related