如何在SQL Server 2016中解析JSON值数组生成多行数据？

原始JSON字符串：

[
    {
        "name": "foo",
        "address": [
            "address1",
            "address2"
        ]
    },
    {
        "name": "bar",
        "address": [
            "address3"
        ]
    }
]

希望转换为以下多行格式（用于插入表中）：

Name    Address
foo     address1
foo     address2
bar     address3

已尝试的代码及结果 #

我尝试了以下代码：

DECLARE @json nvarchar(max) = '[{"name":"foo","address":["address1","address2"]},{"name":"bar","address":["address3"]}]'

SELECT 
    [name],
    [address]
FROM OpenJson(@json) WITH
(
    [name] nvarchar(20),
    [address] nvarchar(max) as json
)

但返回的是包含数组的2行数据：

name address
foo ["address1","address2"]
bar ["address3"]

我见过处理对象数组的方案，但处理值数组时不适用。

解决方案 #

你需要使用CROSS APPLY配合OPENJSON来展开address数组，因为address是值类型数组，直接用OPENJSON解析时可以获取其值：

DECLARE @json nvarchar(max) = '[{"name":"foo","address":["address1","address2"]},{"name":"bar","address":["address3"]}]'

SELECT 
    j.[name],
    a.[value] AS Address
FROM OpenJson(@json) WITH
(
    [name] nvarchar(20),
    [address] nvarchar(max) as json
) j
CROSS APPLY OPENJSON(j.[address]) a

说明： #

1. 外层OPENJSON先解析JSON数组，提取每个对象的name和作为JSON类型的address数组。
2. 通过CROSS APPLY调用OPENJSON解析每个address数组，数组中的每个值会被拆分为单独的行，[value]字段就是数组中的具体地址值。
3. 最终将name和对应的每个地址值关联，得到需要的3行数据。

内容的提问来源于stack exchange，提问作者Mr. Blonde