SQL解析嵌套JSON：获取所有ID对应key1值（无则返回NULL）

原始嵌套JSON数据 #

[
    {
        "id": 1,
        "meta": [
            { "key": "key1", "value": "ValueKey1" },
            { "key": "key2", "value": "ValueKey2" }
        ]
    },
    {
        "id": 2,
        "meta": [ { "key": "key2", "value": "ValueKey2" } ]
    },
    {
        "id": 3,
        "meta": [ { "key": "key1", "value": "ValueKey1" } ]
    }
]

需求 #

查询所有id，返回对应key1的value值；若该id没有key1，则返回NULL。期望结果：

Id   MetaValue
---------------
1    ValueKey1
2    NULL
3    ValueKey1

尝试的SQL及问题 #

用户尝试两种SQL后结果均不符合预期：

• 带WHERE子句的SQL会丢失id=2的记录：

select Id, MetaValue
from openjson('[{"id":1,"meta":[{"key":"key1","value":"ValueKey1"},{"key":"key2","value":"ValueKey2"}]},{"id":2,"meta":[{"key":"key2","value":"ValueKey2"}]},{"id":3,"meta":[{"key":"key1","value":"ValueKey1"}]}]', '$')
with(
    id int '$.id',
    jMeta nvarchar(max) '$.meta' as JSON
    )
outer apply openjson(jMeta)
with(
    cKey varchar(100) '$.key',
    MetaValue varchar(100) '$.value'
    )
where isnull(cKey,'') in ('','Key1')

返回结果：

Id  MetaValue
-------------
1   ValueKey1
3   ValueKey1

• 不带WHERE子句的SQL会返回所有key的记录，不符合仅取key1的要求：

Id  MetaValue
-------------
1   ValueKey1
1   ValueKey2
2   ValueKey2
3   ValueKey1

正确的SQL实现 #

以下两种方法均可满足需求：

方法1：在OUTER APPLY中直接过滤目标Key #

SELECT 
    main.id,
    meta.MetaValue
FROM OPENJSON('[{"id":1,"meta":[{"key":"key1","value":"ValueKey1"},{"key":"key2","value":"ValueKey2"}]},{"id":2,"meta":[{"key":"key2","value":"ValueKey2"}]},{"id":3,"meta":[{"key":"key1","value":"ValueKey1"}]}]', '$')
WITH (
    id INT '$.id',
    jMeta NVARCHAR(MAX) '$.meta' AS JSON
) AS main
OUTER APPLY (
    SELECT MetaValue
    FROM OPENJSON(main.jMeta)
    WITH (
        cKey VARCHAR(100) '$.key',
        MetaValue VARCHAR(100) '$.value'
    )
    WHERE cKey = 'key1' -- 仅匹配key1，无匹配时返回NULL
) AS meta

方法2：使用聚合函数配合条件判断（适用于同一ID可能存在多个key1的场景） #

SELECT 
    main.id,
    MAX(CASE WHEN meta.cKey = 'key1' THEN meta.MetaValue END) AS MetaValue
FROM OPENJSON('[{"id":1,"meta":[{"key":"key1","value":"ValueKey1"},{"key":"key2","value":"ValueKey2"}]},{"id":2,"meta":[{"key":"key2","value":"ValueKey2"}]},{"id":3,"meta":[{"key":"key1","value":"ValueKey1"}]}]', '$')
WITH (
    id INT '$.id',
    jMeta NVARCHAR(MAX) '$.meta' AS JSON
) AS main
OUTER APPLY OPENJSON(main.jMeta)
WITH (
    cKey VARCHAR(100) '$.key',
    MetaValue VARCHAR(100) '$.value'
) AS meta
GROUP BY main.id

两种方法均会返回符合预期的结果：

Id   MetaValue
---------------
1    ValueKey1
2    NULL
3    ValueKey1

内容的提问来源于stack exchange，提问作者hoggar