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?

Answer 1

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.