SQL TABLE to JSON without SQL Column Information

Question

I am trying to Parse Table data into JSON and in the process want to skip the Table Column Names in the Post conversion to JSON. Below is the sample Snippet.

DECLARE @table1 TABLE (k1 nvarchar(max), v1 nvarchar(max))

-- Note k1 is unique
INSERT INTO @table1 (k1,v1) VALUES( 'Apple', 'One')
INSERT INTO @table1 (k1,v1) VALUES( 'Banana', 'Two')
INSERT INTO @table1 (k1,v1) VALUES( 'Orange', 'Three')

SELECT k1, v1 FROM @table1
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

--output: {"k1":"Apple","v1":"One"},{"k1":"Banana","v1":"Two"},{"k1":"Orange","v1":"Three"}
-- Expected: {"Apple","One"},{"Banana","Two"},{"Orange","Three"}

Appreciate direction!

Does this answer your question? Query response in JSON format without column name — Hasan Fathi
– Hasan Fathi, Commented Nov 27, 2020 at 15:34

lptr · Accepted Answer · 2020-11-27 13:02:47Z

1

--string concatenation&aggregation (string_agg() for sql2017&later or for xml path() for sql2016)

select string_agg(concat('{"', string_escape(k1, 'json'), '":"', string_escape(v1, 'json'), '"}'), ',') /*within group (order by k1)*/
from @table1

edited Nov 27, 2020 at 13:02

answered Nov 27, 2020 at 12:51

lptr

6,8582 gold badges10 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

SQL TABLE to JSON without SQL Column Information

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related