在T-SQL中解析JSON数组 [英] Parse JSON Array in T-SQL

问题描述

在我们的SQL Server表中，我们有一个存储有字符串数组的json对象.我想以编程方式将该字符串分成几列.但是，即使有可能，我似乎也无法使它正常工作.

In our SQL Server table we have a json object stored with an array of strings. I want to programatically split that string into several columns. However, I cannot seem to get it to work or even if it's possible.

这是可能在WITH子句中创建多个列吗?还是在select语句中这样做更明智?

Is this a possibility to create multiple columns within the WITH clause or it is a smarter move to do it within the select statement?

我整理了一些代码，以简化给出的内容.

I trimmed down some of the code to give a simplistic idea of what's given.

示例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

推荐答案

由于标签[tsql]和OPENJSON的用法，我认为这是SQL Server.但是可能是错误的……请始终指定您的RDBMS(带有版本).

Due to the tag [tsql] and the usage of OPENJSON I assume this is SQL-Server. But might be wrong... Please always specify your RDBMS (with version).

您的JSON有点奇怪...我认为您在为简化起见而简化此代码时已经夸大了它...

Your JSON is rather weird... I think you've overdone it while trying to simplify this for brevity...

尝试一下:

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;

一种相当简短的方法(但仅适合此简单示例):

A rather short approach (but fitting to this simple example only) was this:

SELECT t.ID
      ,A.*
FROM @tbl t
OUTER APPLY OPENJSON(JSON_QUERY(YourJSON,'$.arr')) A 

提示

JSON支持是在SQL-Server 2016中引入的

Hint

JSON support was introduced with SQL-Server 2016

有一个技巧可以将CSV转换为JSON数组.试试这个

There's a trick to transform a CSV into a JSON-array. Try this

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中打开.

Some simple replacements in OPENJSON('["' + REPLACE(B.[value],'-','","') + '"]') will create a JSON array out of your CSV-string, which can be opened in OPENJSON.

这篇关于在T-SQL中解析JSON数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！