How to construct single JSON where column1 is property name and column2 is value

Question

In SQL Server 2016 +, how can i convert a table looking like this:

+---------+----------+
|   Kee   |   Val    |
+---------+----------+
| aaaaaa  | 11111111 |
| bbbbbbb |  2222222 |
+---------+----------+

into an object looking like this:

{
  "aaaaaa": "11111111",
  "bbbbbbb": "2222222"
}

This is what I've tried:

CREATE TABLE #tmp
(
    Kee VARCHAR(100),
    Val VARCHAR(100)
)
INSERT INTO #tmp
(
    Kee,
    Val
)
VALUES
('aaaaaa', '11111111'),
('bbbbbbb', '2222222')


SELECT t.Kee,
       t.Val
FROM #tmp AS t
FOR JSON AUTO
DROP TABLE #tmp

But it gives:

[
  {
    "Kee": "aaaaaa",
    "Val": "11111111"
  },
  {
    "Kee": "bbbbbbb",
    "Val": "2222222"
  }
]

What you're asking for is not valid JSON.

JAAulde
– JAAulde

2019-07-16 15:25:28 +00:00
Commented Jul 16, 2019 at 15:25 — JAAulde
– JAAulde, Commented Jul 16, 2019 at 15:25
@JAAulde yikes, typo, fixed, thanks!

Sajjan Sarkar
– Sajjan Sarkar

2019-07-16 15:34:02 +00:00
Commented Jul 16, 2019 at 15:34 — Sajjan Sarkar
– Sajjan Sarkar, Commented Jul 16, 2019 at 15:34

Zohar Peled · Accepted Answer · 2019-07-16 15:34:12Z

2

Unfortunately, SQL Server's Json support is not that flexible.
You will have to manually construct that json, but it's quite simple using basic string concatenation techniques.

Prior to 2017 version use for xml path with stuff:

SELECT STUFF(
    (
        SELECT '","'+ t.Kee +'":"'+ t.Val
        FROM #tmp AS t
        FOR XML PATH('')
    ), 1, 2, '{') + '}' As JsonResult

In 2017 SQL Server finally introduced a built-in string_agg function, making the code required to get that result much simpler:

SELECT '{"' + STRING_AGG(t.Kee +'":"'+ t.Val, '","') +'"}'
FROM #tmp As t

Result (in both cases):

{"aaaaaa":"11111111","bbbbbbb":"2222222"}

edited Jul 16, 2019 at 15:34

answered Jul 16, 2019 at 15:23

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

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

7 Comments

JAAulde Over a year ago

What you have produced is what the OP asked for, but is not valid JSON.

JAAulde Over a year ago

The structure, ["key":"value"], is not JSON. {"key":"value"} is JSON

Zohar Peled Over a year ago

@JAAulde You are correct - but that, as you wrote, what the OP has asked for. An easy change in the code will provide a valid json, if that's needed. (simply change the [ to { and ] to }.)

Sajjan Sarkar Over a year ago

Guys, sorry i mis-typed my output JSON {"aaaaaa":"11111111","bbbbbbb":"2222222"} is what Im looking for. Thanks!

Zohar Peled Over a year ago

@JAAulde after the OP have edited the question, I've reedited the answer.

|

Collectives™ on Stack Overflow

How to construct single JSON where column1 is property name and column2 is value

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related