json - 如何在 SQL Server 中正确解压缩 JSON 数组

Question

我正在尝试使用 JSON，我想将一个用户添加到多个组中：将一个 JSON 数组插入一个表中。

理想情况下，JSON 应该是这样的：

'{
"Email": "[e-mail]",
"Prefix":null,
"FirstName": "Worf",
"MiddleInitial": "",
"LastName": "Mogh",
"Suffix": "Son Of",
"Title" :"Commander",
"Groups": [{"0", "1", "5"}]
"Better_Groups": [{"ID":"0", "ID":"1", "ID":"5"}]
}' 

目前，我可以像这样使用 JSON 来做到这一点：

'{
"Email": "[e-mail]",
"Prefix":null,
"FirstName": "Worf",
"MiddleInitial": "",
"LastName": "Mogh",
"Suffix": "Son Of",
"Title" :"Commander",
"Groups": "1,2,3,4"
}' 

然后用以下小曲“解压”它：

declare @groups varchar(1000)

select @groups = Groups from openjson(@json)
                WITH
        (

        Groups nvarchar(100)        '$.Groups'
        )

        print @groups

select value from string_split(@groups, ',')

它返回一个不错的小表，如下所示：

Value
1
2
3
4

问题 这是糟糕的 JSON，Web 开发人员会取笑我。

问题如何在 SQL Server 中正确解压缩 JSON 数组？

更新：

最终使用的 JSON 如下所示：

    @json =
            '{
            "Email": "[e-mail]",
            "Prefix":null,
            "FirstName": "Worf",
            "MiddleInitial": "",
            "LastName": "Mogh",
            "Suffix": "Son Of",
            "Title" :"Commander",
            "Groups": "1,2,3,4",
            "Better_Groups": ["0", "1", "5"]
            }' 

标签： jsonazure-sql-database

Answer 1

假设“groups”元素是一个数组：

DECLARE @json NVARCHAR(MAX) = 
N'{
"Email": "[e-mail]",
"Prefix":null,
"FirstName": "Worf",
"MiddleInitial": "",
"LastName": "Mogh",
"Suffix": "Son Of",
"Title" :"Commander",
"Better_Groups": ["0", "1", "5"]
}';

SELECT s.value
FROM OPENJSON(JSON_QUERY(@json, '$.Better_Groups')) s;

db<>小提琴演示