CODEWITHSUNDEEP
jsonsql-server
DanCZ
DanCZ

Reputation: 313

Select JSON from a table using MS SQL FOR JSON

I have a table with 2 columns key value and I want to do a SELECT FOR JSON and get the result in {"key": "value"} format

Any idea how to do it?

thank you

table:

key | value
-----------
foo | 1

expected results:

{"foo": "1"}

what I tried

SELECT key,value FROM table FOR JSON AUTO

what was the result

{"key":"foo","value":"1"}

this solution also works but I am looking for something cleaner

DBfiddle

Upvotes: 0

Views: 130

Answers (1)

Jagjeet
Jagjeet

Reputation: 61

I am fetching two columns EMPLOYEEID and EMPLOYEENAME from EMPLOYEE table. Replace columns and table with your details.

SELECT STUFF
 (
 (
    SELECT CONCAT(',{"', EMPLOYEEID, '": "', EMPLOYEENAME, '"', '}')
    FROM EMPLOYEE
    FOR XML PATH(''), TYPE
 ).value('.', 'varchar(max)')
 ,1
 ,1
 ,''
 ) + '';

Upvotes: 1

Related Questions