SQL中如何从多元素JSON数组根据指定tagType获取对应tagValue？

问题场景 #

给定如下格式的JSON字符串（注意：该JSON使用单引号，不符合标准JSON规范）：

[
    {
        'tagType': 'Author',
        'tagValue': 'Anne Author'
    },
    {
        'tagType': 'Business',
        'tagValue': 'the bidness'
    },
    {
        'tagType': 'Geography',
        'tagValue': 'US'
    },
    {
        'tagType': 'Subscription',
        'tagValue': 'A subscription'
    }
]

需求是根据指定的tagType（例如Geography）获取对应的tagValue（例如US），已有可行但不够简洁的查询语句，需要更优雅的实现方式。

当前可行的子查询写法：

[Geography] = (SELECT tagValue 
                FROM OPENJSON(REPLACE(p.tags, '''', '"')) 
                WITH (tagType VARCHAR(255) '$.tagType', tagValue VARCHAR(255) '$.tagValue')
                WHERE tagType = 'Geography')

优化方案 #

方案1：使用JSON_VALUE直接过滤（简洁单值查询） #

如果使用SQL Server 2016及以上版本，可以利用JSON路径的过滤功能，直接通过路径定位目标值，避免子查询：

[Geography] = JSON_VALUE(REPLACE(p.tags, '''', '"'), '$[?(@.tagType == "Geography")].tagValue')

该写法会返回第一个匹配到的tagValue，和原查询行为一致。

方案2：CROSS APPLY批量处理（多标签或高效查询） #

如果需要同时获取多个标签值，或者想避免重复解析JSON提升效率，可以用CROSS APPLY配合条件聚合：

SELECT 
    MAX(CASE WHEN tagType = 'Author' THEN tagValue END) AS Author,
    MAX(CASE WHEN tagType = 'Geography' THEN tagValue END) AS Geography,
    MAX(CASE WHEN tagType = 'Business' THEN tagValue END) AS Business
FROM your_table p
CROSS APPLY OPENJSON(REPLACE(p.tags, '''', '"')) 
WITH (tagType VARCHAR(255) '$.tagType', tagValue VARCHAR(255) '$.tagValue') AS tags
GROUP BY p.id -- 替换为你的表主键或分组字段

这种方式只需解析一次JSON，就能批量提取多个标签值，适合复杂查询场景。

额外建议 #

原始JSON使用单引号不符合标准JSON语法，建议在数据入库时就将其转换为标准双引号格式，这样查询时可以省去REPLACE(p.tags, '''', '"')的转换步骤，既提升查询效率，也能避免格式转换带来的潜在问题。

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