Разбор массива JSON в T-SQL
В нашей таблице SQL Server у нас есть объект json, хранящийся с массивом строк. Я хочу программно разбить эту строку на несколько столбцов. Однако я не могу заставить его работать, или даже если это возможно.
Это возможность создать несколько столбцов в предложении WITH или это более разумный шаг, чтобы сделать это в операторе select?
Я урезал часть кода, чтобы дать упрощенное представление о том, что дано.
Пример JSON похож на { "arr": ["str1 - str2"] }
SELECT b.* FROM [table] a
OUTER APPLY
OPENJSON(a.value, '$.arr')
WITH
(
strSplit1 VARCHAR(100) SPLIT('$.arr', '-',1),
strSplit2 VARCHAR(100) SPLIT('$.arr', '-',2)
) b
В SQL Server нет SPLIT. В SQL Server 2016+ есть STRING_SPLIT. Однако вам, вероятно, следует исправить содержимое JSON. Если вам нужен массив строк, зачем использовать ["str1 - str2"] вместо ["str1","str2"]?
Ответы 2
Из-за тега [tsql] и использования OPENJSON я предполагаю, что это SQL-Server. Но может ошибаться ... Всегда указывайте вашу СУБД (с версией).
Ваш JSON довольно странный ... Я думаю, вы перестарались, пытаясь упростить это для краткости ...
Попробуй это:
DECLARE @tbl TABLE(ID INT IDENTITY,YourJSON NVARCHAR(MAX));
INSERT INTO @tbl VALUES(N'{ "arr": ["str1 - str2"] }') --weird example...
,(N'{ "arr": ["a","b","c"] }'); --array with three elements
SELECT t.ID
,B.[value] AS arr
FROM @tbl t
CROSS APPLY OPENJSON(YourJSON)
WITH(arr NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.arr) B;
Довольно короткий подход (но подходящий только для этого простого примера) заключался в следующем:
SELECT t.ID
,A.*
FROM @tbl t
OUTER APPLY OPENJSON(JSON_QUERY(YourJSON,'$.arr')) A
Намекать
Поддержка JSON появилась в SQL-Server 2016.
ОБНОВЛЕНИЕ: если содержимое JSON представляет собой странную CSV-строку ...
Есть трюк для преобразования CSV в массив JSON. Попробуй это
DECLARE @tbl TABLE(ID INT IDENTITY,YourJSON NVARCHAR(MAX));
INSERT INTO @tbl VALUES(N'{ "arr": ["str1 - str2"] }') --weird example...
,(N'{ "arr": ["a","b","c"] }') --array with three elements
,(N'{ "arr": ["x-y-z"] }'); --array with three elements in a weird CSV format
SELECT t.ID
,B.[value] AS arr
,C.[value]
FROM @tbl t
CROSS APPLY OPENJSON(YourJSON)
WITH(arr NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.arr) B
CROSS APPLY OPENJSON('["' + REPLACE(B.[value],'-','","') + '"]') C;
Некоторые простые замены в OPENJSON('["' + REPLACE(B.[value],'-','","') + '"]') создадут массив JSON из вашей CSV-строки, который можно открыть в OPENJSON.
Я не знаю, как разделить строку в JSON. Интересно, связана ли проблема с вашим JSON, содержащим одну строку, а не несколько значений?
В приведенном ниже примере показано, как извлечь каждую строку из массива; и если вы хотите пойти дальше и разделить эти строки по дефису, покажет, как это сделать, используя обычные функции SQL SUBSTRING и CHARINDEX.
create table [table]
(
value nvarchar(max)
)
insert [table](value)
values ('{ "arr": ["str1 - str2"] }'), ('{ "arr": ["1234 - 5678","abc - def"] }')
SELECT b.value
, rtrim(substring(b.value,1,charindex('-',b.value)-1))
, ltrim(substring(b.value,charindex('-',b.value)+1,len(b.value)))
FROM [table] a
OUTER APPLY OPENJSON(a.value, '$.arr') b
Если вы хотите, чтобы все значения были в одном столбце, вы можете использовать функцию string_split: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017
SELECT ltrim(rtrim(c.value))
FROM [table] a
OUTER APPLY OPENJSON(a.value, '$.arr') b
OUTER APPLY STRING_SPLIT(b.value, '-') c
Я предпочитаю OPENJSON, а не STRING_SPLIT(), поскольку он возвращается в указанном порядке, что не гарантируется STRING_SPLIT(). CSV легко преобразовать в массив JSON. В моем ответе есть пример.
Ах, хороший трюк; манипулировать строкой перед синтаксическим анализом JSON; подлый
NB:
{ "arr": ["str1 - str2"] }- это массив, содержащий одну строку.{ "arr": ["str1","str2"] }- это массив с двумя строками. Вы имели в виду последнее или первое верно?