在T-SQL中解析JSON数组

Question

在我们的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

Answer 1

考虑到标记[tsql]和OPENJSON的用法，我假设这是SQL-Server。但可能是错的..。请始终指定您的RDBMS (带版本)。

你的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 

提示

SQL-Server 2016引入了JSON支持

更新:如果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],'-','","') + '"]')中的一些简单替换将从CSV-string创建一个JSON数组，该数组可以在OPENJSON中打开。

Answer 2

我不知道有什么方法可以在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