SQL如何从JSON数组中按指定item值提取对应value作为查询列？

解决方案 #

方案1：条件聚合（兼容SQL Server 2016及以上版本，性能最优） #

你可以在展开JSON数组后，通过条件聚合直接行转列得到需要的结构，每个行的JSON仅需解析一次，无需二次查询：

SELECT 
    F.ID,
    MAX(CASE WHEN x.item = '11' THEN x.value END) AS X,
    MAX(CASE WHEN x.item = '12' THEN x.value END) AS Y
FROM tbl F
CROSS APPLY OPENJSON(F.Json, '$.details.detail')
WITH (
    item NVARCHAR(25) '$.item',
    value NVARCHAR(MAX) '$.value'
) AS x
-- 可按需添加过滤条件，例如 WHERE F.ID = 55
GROUP BY F.ID

注：你之前的写法多嵌套了一层不必要的OPENJSON(value)调用，去掉后可以降低JSON解析开销，进一步提升性能。

方案2：JSON路径过滤（兼容SQL Server 2017+、Azure SQL，写法更简洁） #

如果你的数据库版本支持JSON路径过滤谓词，可以直接在JSON_VALUE中按item值匹配，无需显式展开数组：

SELECT
    ID,
    JSON_VALUE(Json, '$.details.detail[?(@.item == "11")].value') AS X,
    JSON_VALUE(Json, '$.details.detail[?(@.item == "12")].value') AS Y
FROM tbl
-- 可按需添加过滤条件，例如 WHERE ID = 55

性能优化建议 #

如果该查询为高频查询，可针对常用的item值创建持久化计算列并建立索引，查询性能可达到和普通结构化列一致的水平：

-- 为item=11的值创建持久化计算列
ALTER TABLE tbl ADD X AS JSON_VALUE(Json, '$.details.detail[?(@.item == "11")].value') PERSISTED
-- 为计算列创建索引
CREATE NONCLUSTERED INDEX IX_tbl_X ON tbl(X)

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