How to convert JSON to array of strings-CodePudding

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

CodePudding user response：

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