SQL to JSON - массив объектов в массив значений в SQL 2016

Question

SQL to JSON - массив объектов в массив значений в SQL 2016

В SQL 2016 появилась новая функция, которая преобразует данные на сервере SQL в JSON. У меня возникли трудности в объединении массива объектов в массив значений, т. Е.

ПРИМЕР -

CREATE TABLE #temp (item_id VARCHAR(256))

INSERT INTO #temp VALUES ('1234'),('5678'),('7890')

SELECT * FROM #temp

--convert to JSON

SELECT (SELECT item_id 
FROM #temp
FOR JSON PATH,root('ids'))

РЕЗУЛЬТАТ -

{
    "ids": [{
        "item_id": "1234"
    },
    {
        "item_id": "5678"
    },
    {
        "item_id": "7890"
    }]
}

Но я хочу результат как -

"ids": [
        "1234",
        "5678",
        "7890"
    ]

Может кто-нибудь помочь мне?

54

sql json sql-server-2016

Источник

user6441520 08 июн '16 в 17:00

9 ответов

Решение

Мартин!

Я считаю, что это еще более простой способ сделать это:

    SELECT '"ids": ' + 
    REPLACE( 
      REPLACE( (SELECT item_id FROM #temp FOR JSON AUTO),'{"item_id":','' ),
      '"}','"' )

18

Источник

user5523436 15 окт '16 в 19:29

declare @temp table (item_id VARCHAR(256))

INSERT INTO @temp VALUES ('123"4'),('5678'),('7890')

SELECT * FROM @temp

--convert to JSON

select 
    json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', char(44)))) as [json]
from @temp
for json path

Когда мы хотим объединить строки как массив json, тогда:

1) escape-строка - STRING_ESCAPE

2) объединить строку с разделителем-запятой - STRING_AGG, код запятой ascii равен 44

3) добавить цитату в скобках - QUOTENAME (без параметра)

4) вернуть строку (с массивом элементов) в виде json - JSON_QUERY

17

Источник

user341262 22 ноя '17 в 17:15

Поскольку массивы примитивных значений являются допустимыми JSON, кажется странным, что средство выбора массивов примитивных значений не встроено в функциональность JSON SQL Server. (Если, наоборот, такая функциональность существует, я, по крайней мере, не смог обнаружить ее после долгих поисков).

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

Например, это

DECLARE @BomTable TABLE (ChildNumber dbo.udt_ConMetPartNumber);
INSERT INTO @BomTable (ChildNumber) VALUES (N'101026'), (N'101027');
SELECT N'"Children": ' + REPLACE(REPLACE((SELECT ChildNumber FROM @BomTable FOR JSON PATH), N'{"ChildNumber":', N''), '"}','');

работает, производя:

"Children": ["101026,"101027]

Но, следуя подходу выше, это:

SELECT
    p.PartNumber,
    p.Description,
    REPLACE(REPLACE((SELECT
                        ChildNumber
                     FROM
                        Part.BillOfMaterials
                     WHERE
                        ParentNumber = p.PartNumber
                     ORDER BY
                        ChildNumber
                    FOR
                     JSON AUTO
                    ), N'{"ChildNumber":', N''), '"}', '"') AS [Children]
FROM
    Part.Parts AS p
WHERE
    p.PartNumber = N'104444'
FOR
    JSON PATH

Производит:

[
    {
        "PartNumber": "104444",
        "Description": "ASSY HUB           R-SER  DRIV HP10  ABS",
        "Children": "[\"101026\",\"101027\",\"102291\",\"103430\",\"103705\",\"104103\"]"
    }
]

Где массив Children обернут как строка.

6

Источник

user852956 27 окт '16 в 21:18

Эта версия (основанная на других):

правильно экранирует специальные символы JSON (например, кавычки)
возвращает пустой массив [] без данных

Требуется SQL 2016 или новее (из-за STRING_ESCAPE):

SELECT 
   CONCAT('[', '"' +
        (SELECT STRING_AGG(STRING_ESCAPE(item_id, 'json'), '","') 
         FROM #temp) 
    + '"', ']')

4

Источник

user1278 25 ноя '20 в 18:51

Большинство из этих решений по существу создают CSV, который представляет содержимое массива, и затем помещают этот CSV в окончательный формат JSON. Вот что я использую, чтобы избежать XML:

DECLARE @tmp NVARCHAR(MAX) = ''

SELECT @tmp = @tmp + '"' + [item_id] + '",'
FROM #temp -- Defined and populated in the original question

SELECT [ids] = JSON_QUERY((
    SELECT CASE
        WHEN @tmp IS NULL THEN '[]'
        ELSE '[' + SUBSTRING(@tmp, 0, LEN(@tmp)) + ']'
        END
    ))
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

1

Источник

user7232078 08 фев '17 в 19:04

Мне нравится ответ @massther для SQL Server 2017 и более поздних версий. Однако результирующий JSON заключен в массив. Чтобы избавиться от массива, используйтеWITHOUT_ARRAY_WRAPPERвариант вFOR JSONпункт.

Кроме того, как кто-то упомянул в комментариях,QUOTENAME()функция вызывает проблемы, если какие-либо данные содержат закрывающую квадратную скобку,].

Ниже представлена оригинальная версия Massther и модифицированная версия с этими изменениями.

      declare @temp table (item_id VARCHAR(256))

INSERT INTO @temp VALUES ('1234'),('5678'),('7890'),('[problem]')

SELECT * FROM @temp

--convert to JSON

-- Original version:
select 
    json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', char(44)))) 
        as [json]
from @temp
for json path

-- Modified version: 
--    Replaced QUOTENAME() with '[' + ... + ']'
--    Replaced char(44) as the separator character with ','
--    Added WITHOUT_ARRAY_WRAPPER option.
select 
    json_query('[' + STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', ',') + ']') 
        as [json]
from @temp
for json path, WITHOUT_ARRAY_WRAPPER;

Полученные результаты:

Оригинальная версия:

Обратите внимание, что это массив JSON, а не объект JSON, и двойное «]]» после текста «проблемы».

      [
    {
        "json": [
            "1234",
            "5678",
            "7890",
            "[problem]]"
        ]
    }
]

Модифицированная версия:

Объект JSON, а не массив JSON, и закрывающий "]" после текста "проблемы" обрабатывается правильно.

      {
    "json": [
        "1234",
        "5678",
        "7890",
        "[problem]"
    ]
}

1

Источник

user216440 09 май '23 в 23:05

Вот дикая идея, которая может быть или не быть практичной. Рекурсивно перебирайте свой набор данных и добавляйте вещи в свои массивы JSON, используя JSON_MODIFY:

      with
  d (d) as (select * from (values (1),(2),(3),(4)) t (d)),
  j (d, j) as (
    -- Adapt the recursion to make it dynamic
    select 1, json_modify('[]', 'append $', d)
    from d
    where d = 1
    union all
    select d.d, json_modify(j, 'append $', d.d)
    from d join j on d.d = j.d + 1
  )
select * 
from j;

Я сделал это простым для иллюстрации. Конечно, вы адаптируете его, чтобы сделать его динамичным. Это производит:

      |d  |j        |
|---|---------|
|1  |[1]      |
|2  |[1,2]    |
|3  |[1,2,3]  |
|4  |[1,2,3,4]|

Может даже использоваться для эмуляции стандартного SQL JSON_ARRAYAGG

1

Источник

user521799 03 мар '22 в 15:01

Я думаю, что в SQL Server 2017 было бы проще:

      select
 JSON_QUERY
 (
     '["' + STRING_AGG(t.item_id,'","') + '"]'
 ) as ids
 from #temp t
 for json auto, without_array_wrapper

0

Источник

user1465821 22 фев '23 в 11:01

Другие вопросы по тегам sql json sql-server-2016

user6441520 15 июн '16 в 19:25 2016-06-15 19:25 · Accepted Answer · 2016-06-15 19:25

Спасибо! Душа, которую мы нашли, сначала конвертируется в XML -

SELECT  
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + item_id + '"' 
FROM #temp FOR XML PATH('')),1,1,'') + ']' ) ids  
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER

33

Источник

user6441520 15 июн '16 в 19:25