Конвертировать строки / записи таблицы в документ JSON на сервере SQL

Question

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

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

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

Answer 1

Обратите внимание, вы не можете получить с прямым запросом, вы должны сначала 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

Answer 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 для объединения ключей.