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"
    ]

Может кто-нибудь, пожалуйста, помогите мне?

Ответы

Ответ 1

Спасибо! Существо, которое мы обнаружили, сначала преобразуется в XML -

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

Ответ 2

Martin!

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

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

Ответ 3

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

Ответ 4

Большинство этих решений по существу создают 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

Ответ 5

Поскольку массивы примитивных значений действительны 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\"]"
    }
]

Если массив "Дети" завернут в виде строки.