Output json in dictionary (string-indexed list) notation from SQL Server

Question

I have this result set in SQL server:

ID   CUSTOMER   PRODUCT   DATE       COUNT
A1   Walmart    Widget    1/1/2020   5
B2   Amazon     Thingy    1/2/2020   10
C3   Target     Gadget    2/1/2020   7

I want to output it as json, which SQL server 2016+ has plenty ability to do. But I want a traditional string-indexed list ('dictionary') indexed by the id, like so:

Goal

{
  "A1": {"Customer":"Walmart", "Product":"Widget", "Date":"1/1/2020", "Count":5 },
  "B2": {"Customer":"Amazon",  "Product":"Thingy", "Date":"1/2/2020", "Count":10},
  "C3": {"Customer":"Target",  "Product":"Gadget", "Date":"2/1/2020", "Count":7 }
}

However, typical select * from table for json path outputs as an unindexed array of objects:

Current State

[
  {"Id":"A1", "Customer":"Walmart", "Product":"Widget", "Date":"1/1/2020", "Count":5 },
  {"Id":"B2", "Customer":"Amazon",  "Product":"Thingy", "Date":"1/2/2020", "Count":10},
  {"Id":"C3", "Customer":"Target",  "Product":"Gadget", "Date":"2/1/2020", "Count":7 }
]

The other for json modifiers such as root seem superficially relevant, but as far as I can tell just does glorified string concatenation of capturing the entire object in an outer root node.

How can the above notation be done using native (performant) SQL server json functions?

Some of that JSON doesn't appear to be valid (certainly the dates should be quoted). I can't think of a way to do with with the JSON functionality, and without the Array wrappers in the inner JSON, my *best* attempt results in all the double quotes (`"`) being escaped. I.e. `"{\"Customer\":\"Walmart\",\"Product\":\"Widget\",\"Date\":\"2020-01-01\",\"Count\":5}"}`. even this, that didn't have the dynamic names for the lists. You *should* easily achieve this with `CONCAT_WS` and `STRING_AGG` though (but `CONCAT_WS` was added in 2017, so you'd have to use `CONCAT`). — Thom A, Feb 03 '20 at 16:33
@Larnu: I hand typed the json in the question, so yes the dates should be quoted. — Devin Burke, Feb 03 '20 at 18:37

Zhorov · Accepted Answer · 2020-02-05T06:56:25.133

I don't think that you can generate JSON output with variable key names using FOR JSON AUTO or FOR JSON PATH, but if you can upgrade to SQL Server 2017, the following approach, that uses only JSON built-in support, is a possible option:

Table:

CREATE TABLE Data (
   Id varchar(2), 
   Customer varchar(50),
   Product varchar(50),   
   [Date] date,       
   [Count] int
)
INSERT INTO Data 
   (Id, Customer, Product, [Date], [Count])
VALUES   
   ('A1', 'Walmart', 'Widget', '20200101', 5),
   ('B2', 'Amazon',  'Thingy', '20200102', 10),
   ('C3', 'Target',  'Gadget', '20200201', 7)

Statement:

DECLARE @json nvarchar(max) = N'{}'   
SELECT @json = JSON_MODIFY(
   @json, 
   CONCAT(N'$."', ID, N'"'), 
   JSON_QUERY((SELECT Customer, Product, [Date], [Count] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
)
FROM Data

SELECT @json

Result:

{"A1":{"Customer":"Walmart","Product":"Widget","Date":"2020-01-01","Count":5},"B2":{"Customer":"Amazon","Product":"Thingy","Date":"2020-01-02","Count":10},"C3":{"Customer":"Target","Product":"Gadget","Date":"2020-02-01","Count":7}}

Notes:

Using a variable or expression instead of value for path parameter in JSON_MODIFY() is available in SQL Server 2017+. JSON_QUERY() is used to prevent the escaping of the special characters.

Indeed, SQL server 2017 is an option and this does work. I'm trying to measure the performance detriment of doing it like this. But it works and seems to be the closest option to not horrible. — Devin Burke, Feb 04 '20 at 23:29

Gordon Linoff · Answer 2 · 2020-02-03T18:54:22.383

Unfortunately, you want a JSON result that has multiple values -- A1, B2, and C3 -- derived from the data. This means that you need to aggregate the data into one row. Normally, for json path would want to create an array of values, one for each row.

So, this should do what you want:

select json_query(max(case when id = 'A1' then j.p end)) as A1,
       json_query(max(case when id = 'B2' then j.p end)) as B2,
       json_query(max(case when id = 'B3' then j.p end)) as B3
from t cross apply
     (select t.customer, t.product, t.date, t.count
      for json path
     ) j(p)
for json path;

Here is a db<>fiddle.

However, it is not easily generalizable. For a general solution, you might need to do string manipulations.

You can get around the escaping issue with `JSON_QUERY`; i.e. `JSON_QUERY(MAX(CASE WHEN ...` Note that this requires applying it to the outermost expression; the "JSON-ness" of a string is not propagated. — Jeroen Mostert, Feb 03 '20 at 16:51

lptr · Answer 3 · 2020-02-03T16:53:46.667

1

The question is tagged sql2016, string_agg() won't work ... (aggregate with xpath or custom aggregate)

declare @t table
(
Id varchar(10),
CUSTOMER varchar(50),
PRODUCT varchar(50),
[DATE] date,
[COUNT] int
);

insert into @t(Id, CUSTOMER, PRODUCT, [DATE], [COUNT])
values
('A1','Walmart','Widget','20200101', 5),
('B2','Amazon','Thingy','20200201', 10),
('C3','Target','Gadget','20200102', 7);


select concat('{', STRING_AGG(thejson, ','), '}')
from 
(
select concat('"', STRING_ESCAPE(Id, 'json'), '":', (select CUSTOMER, PRODUCT, DATE, COUNT for json path, without_array_wrapper )) as thejson
from @t
) as src;

edited Feb 03 '20 at 16:53

answered Feb 03 '20 at 16:41

lptr

1
2
6
16

`STRING_ESCAPE(Id, 'json')` would cover the admittedly unusual but possible case of the `Id` containing characters that need escaping. – Jeroen Mostert Feb 03 '20 at 16:44

Output json in dictionary (string-indexed list) notation from SQL Server

3 Answers3