Вывод json в нотации словаря (строковый индексированный список) из SQL Server

Question

У меня есть этот набор результатов на SQL-сервере:

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

Я хочу вывести его как json, который у SQL Server 2016+ много возможностей. Но мне нужен традиционный строковый индексированный список ('словарь'), индексированный по идентификатору, например:

Цель

{
  "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 }
}

Однако типичный select * from table for json path выводит как неиндексированный массив объектов:

Текущее состояние

[
  {"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 }
]

Другой for json модификаторы, такие как root кажутся поверхностно актуальными, но насколько я могу судить, это просто прославленная конкатенация строк для захвата всего объекта во внешнем корневом узле.

Как можно сделать приведенную выше нотацию с использованием собственного (производительного) SQL-сервера json функции?

Answer 1

Я не думаю, что вы можете сгенерировать вывод JSON с именами переменных ключей, используя FOR JSON AUTO или FOR JSON PATH, но если вы можете выполнить обновление до SQL Server 2017, следующий подход, который использует только встроенную поддержку JSON, является возможным вариантом:

Таблица:

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)

Утверждение:

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

Результат:

{"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}}

Заметки:

Использование переменной или выражения вместо значения для path параметр в JSON_MODIFY() доступен в SQL Server 2017+. JSON_QUERY() используется для предотвращения экранирования специальных символов.

Answer 2

К сожалению, вам нужен результат JSON с несколькими значениями - A1, B2 и C3 - полученными из данных. Это означает, что вам нужно агрегировать данные в одну строку. Как обычно,for json path хотел бы создать массив значений, по одному для каждой строки.

Итак, это должно делать то, что вы хотите:

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;

Вот скрипка db<>.

Однако его нелегко обобщить. Для общего решения вам может потребоваться манипуляция со строками.

Answer 3

Вопрос помечен как sql2016, string_agg() не будет работать... (агрегировать с xpath или настраиваемым агрегатом)

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;