在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

- tjg92

---

注意：{ "arr": ["str1 - str2"] } 是一个包含单个字符串的数组。{ "arr": ["str1","str2"] } 是一个包含两个字符串的数组。你是指后者，还是前者正确？ - JohnLBevan

SQL Server 中没有 SPLIT 函数。但在 SQL Server 2016 及以上版本中有 STRING_SPLIT 函数。不过，您应该修复 JSON 内容。如果您想要一个字符串数组，为什么不使用 ["str1","str2"] 而不是 ["str1 - str2"] 呢？ - Panagiotis Kanavos

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 

提示

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],'-','","') + '"]')中进行一些简单的替换，即可将您的CSV字符串创建为JSON数组，该数组可以在OPENJSON中打开。

- Shnugo

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://learn.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

- JohnLBevan

---

我更喜欢使用OPENJSON而不是STRING_SPLIT()，因为它可以按照给定的顺序返回结果，而STRING_SPLIT()不能保证顺序。将CSV转换为JSON数组非常容易，我的答案中有一个示例。 - Shnugo

啊，好聪明的技巧；在解析 JSON 之前操纵字符串；真狡猾。 - JohnLBevan