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

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

Благодаря! Суть, которую мы обнаружили, сначала преобразуется в XML –

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

Мартин!

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

  SELECT '"ids": ' + REPLACE( REPLACE( (SELECT item_id FROM #temp FOR JSON AUTO),'{"item_id":','' ), '"}','"' ) 
 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 (без param)

4) возвращает строку (с массивом элементов) как json – JSON_QUERY

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

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

Где массив « Дети» завернут в виде строки.