Преобразовать записи строк/таблицы в документ json на сервере sql

ниже приведены примеры данных, которые являются входными данными

if object_id('tempdb.dbo.#store_data') is not null
drop table #store_data

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100)
)

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

select * from #store_data

требуется следующий результат

[{
"sid" = "1",
"bid" = "3",
"time" = "2019-01-01"
"que" = "apple"},
{"sid" = "2",
"bid" = "5",
"hrs" = "6",
"dat" = "pine"
}]

следующий запрос, который я пробовал

select [key],[value] from #store_data for json path

ожидаемые результаты не были достигнуты.

Следует учитывать тот факт, что нет неявного порядка сортировки! Без самого внешнего ORDER BY это может сто раз сработать, пройти все внутренние тесты, но в продакшене будет возвращать мусор. Ничего случайного, если bid=3 появляется в первом или во втором объекте JSON.

— 29.01.2019 10:28

Я обновил свой ответ @Shnugo... данный PSK... Я использовал циклы для получения набора результатов... но я чувствовал, что есть другой способ получения в том же порядке

— 29.01.2019 11:39

Цикл или нет: SQL-сервер не сортируется так, как вы вставляете свои данные. Любой SELECT без самого внешнего ORDER BY может возвращаться в любом случайном порядке сортировки. Если вы хотите сохранить порядок сортировки, проще всего добавить столбец IDENTITY.

— 29.01.2019 12:54

sql json sql-server tsql sql-server-2017

29.01.2019 09:22

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

427

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Попробуй это:

drop table if exists #store_data;

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100)
)

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@ColumnNames NVARCHAR(MAX);

SELECT @ColumnNames = STUFF
(
    (
        SELECT DISTINCT ',[' + [key] + ']'
        FROM #store_data
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1
   ,1
   ,''
);

SET @DynamicTSQLStatement = N'
select ' + @ColumnNames + '
from #store_data
PIVOT 
(
    MAX([value]) FOR [key] IN (' + @ColumnNames + ')
) PVT
FOR JSON PATH;
';


EXEC sp_executesql @DynamicTSQLStatement;

Измените на SELECT *, чтобы получить storeid и в объекте JSON. Кроме того, в SQL Server 2017 вы можете использовать STRING_AGG для объединения ключей.

Запрос помечен SQL Server 2017

— 29.01.2019 09:48

Да, поэтому я и говорю, что он может использовать STRING_AGG.

— 29.01.2019 09:49

Зачем тогда публиковать метод конкатенации XML? Он скрывает решение действительный, которое заключается в PIVOT данных перед преобразованием их в JSON.

— 29.01.2019 09:50

Потому что он будет работать в более ранних версиях, и другие пользователи, возможно, увидят этот вопрос и не будут иметь функцию STRING_AGG.

— 29.01.2019 09:51

Вы можете прочитать мой комментарий под вопросом о несуществующий неявный порядок сортировки..

— 29.01.2019 10:29

@Shnugo Да, ты прав. Вот почему я указал, что мы можем использовать SELECT *, чтобы получить storeid в каждом объекте. Кажется нормальным иметь его там, потому что он используется для группировки данных.

— 29.01.2019 10:32

Вы правы, я добавил комментарий к другому ответу. +1 и здесь с моей стороны

— 29.01.2019 10:43

29.01.2019 09:40

Ответ принят как подходящий

Обратите внимание, что вы не можете получить с помощью прямого запроса, вы должны сначала PIVOT на storeid.

Вы можете попробовать выполнить следующий запрос.

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
for json auto

Полный пример

 if object_id('tempdb.dbo.#store_data') is not null
 drop table #store_data

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100))

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
for json auto

Вывод:

[
  {
    "sid": "1",
    "bid": "3",
    "time": "2019-01-01",
    "que": "apple"
  },
  {
    "sid": "2",
    "bid": "5",
    "hrs": "6",
    "dat": "pine"
  }
]

Онлайн-демонстрация:

Здесь

Редактировать:

PIVOT автоматически размещает заказы на stroeidASC без указания порядка. Если вы хотите изменить его на другой порядок, скажем, DESC stroeid, в этом случае вы можете изменить запрос следующим образом.

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
order by storeid desc
for json auto

Привет, Хотя это может быть правильным подходом, мы не устаем указывать на тот факт, что нет неявного порядка сортировки! Без самого внешнего ORDER BY это может работать сто раз, проходить все внутренние тесты, но возвращать мусор в производстве. Ничего случайного, если bid=3 появляется в первом или во втором объекте JSON.

— 29.01.2019 10:27

Привет @Shnugo, я не уверен, что понимаю. Ваш комментарий выше моего понимания. Не могли бы вы объяснить это немного больше или некоторые примеры данных, где он может воспроизвести сценарий

— 29.01.2019 10:36

Ладно, я немного поторопился, потому что с первого взгляда не увидел storeid, который входит в состав SELECT *. Вместе с этой информацией объекты получат правильные значения (как группы). Без этого storeid как части окончательного JSON все еще верно, что у вас нет гарантии, какой JSON-объект появится первым, а какой вторым. Но это не совсем против вашего подхода (+1 с моей стороны)

— 29.01.2019 10:42

Я рад, что вы определили пункт «порядок отображения» @Shnugo ..... Я запрашиваю PSK, и Shnugo присутствует в запросе без использования цикла while для отображения результатов в том же порядке.

— 29.01.2019 11:36

@ Smart003, я отредактировал ответ, можешь посмотреть.

— 29.01.2019 11:49

@PSK, хотя PIVOT будет неявно сортировать ваш набор, учитывается только самый внешний SELECT. Это вернет, как и ожидалось, 1 миллион раз, но самый внешний SELECT (формирующий JSON) может в любое время решить вернуться по-другому. Если вы хотите быть уверены в порядке сортировки, вы всегда должны добавлять последний ORDER BY...

— 29.01.2019 12:56

29.01.2019 09:45