如何用SQL Server的OPENJSON提取JSON列指定字段并保留原类型返回

需求：从SQL Server的JSON列中提取特定字段，同时保留JSON数据类型返回结果。由于JSON字段结构动态，无法提前确定字段类型来使用显式架构，示例中需要获取ConfigID和仅包含appName、isEnabled、domains、maxUsers字段的Data列（JSON格式）。

输入数据 #

期望输出 #

实现查询 #

可以通过单个SQL查询结合OPENJSON、JSON_VALUE、JSON_QUERY和FOR JSON来实现，无需依赖应用代码处理：

SELECT 
    ConfigID,
    JSON_QUERY((
        SELECT 
            -- 检查字段是否存在，存在则取值，不存在则不生成该键
            CASE WHEN EXISTS(SELECT 1 FROM OPENJSON(Data) WHERE [key] = 'appName') 
                 THEN JSON_VALUE(Data, '$.appName') 
            END AS appName,
            CASE WHEN EXISTS(SELECT 1 FROM OPENJSON(Data) WHERE [key] = 'isEnabled') 
                 THEN JSON_VALUE(Data, '$.isEnabled') 
            END AS isEnabled,
            CASE WHEN EXISTS(SELECT 1 FROM OPENJSON(Data) WHERE [key] = 'domains') 
                 THEN JSON_QUERY(Data, '$.domains') 
            END AS domains,
            CASE WHEN EXISTS(SELECT 1 FROM OPENJSON(Data) WHERE [key] = 'maxUsers') 
                 THEN JSON_VALUE(Data, '$.maxUsers') 
            END AS maxUsers
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )) AS Data
FROM YourTableName; -- 替换为你的实际表名

逻辑说明 #

1. 子查询中通过EXISTS(SELECT 1 FROM OPENJSON(Data) WHERE [key] = '字段名')判断目标字段是否存在于当前行的JSON中；
2. 普通字段用JSON_VALUE提取值，数组类型字段用JSON_QUERY避免被转义成字符串；
3. FOR JSON PATH, WITHOUT_ARRAY_WRAPPER将筛选后的字段重新组合为单个JSON对象（而非数组）；
4. 外层用JSON_QUERY确保返回的Data列是JSON数据类型，而非字符串类型。

这种方式会自动忽略原JSON中不存在的目标字段，完全适配动态结构的需求。

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