如何使用SQL Server选取JSON对象键值并转换JSON结构

SQL Server 2016及以上版本原生内置JSON解析、构造、修改函数，完全支持你需要的色卡JSON结构调整需求，不需要额外安装插件或依赖外部程序。

转换逻辑 #

你现有数据的colors节点是键值对形式的对象，目标结构是对象数组，转换流程如下：

• 用OPENJSON穿透到$.colors路径，把对象下的所有键值对拆成关系行，每行自动返回[key]（原键名，比如color-1、color-2）、[value]（原颜色值）两个核心字段，自动适配不同记录色号数量不一致的场景
• 把拆出来的[key]映射为目标结构的name字段，需要自定义显示名（比如color-1对应Red）直接用CASE写映射规则即可；[value]直接映射为color字段，需要替换色值也可以在这一步加逻辑
• 用FOR JSON PATH把映射完成的行集组装为JSON数组，再包裹外层的{"colors": ...}结构，得到最终结果

示例代码 #

测试数据准备 #

-- 临时表模拟你的业务表结构
CREATE TABLE #SwatchData (
    RecordId INT IDENTITY(1,1) PRIMARY KEY,
    Swatch NVARCHAR(MAX) NOT NULL
);
-- 插入你给出的示例记录
INSERT INTO #SwatchData (Swatch)
VALUES ('{"colors":{"color-1":"transparent","color-2":"transparent"}}');

查询转换结果 #

SELECT
    RecordId,
    '{"colors":' +
    (
        SELECT
            -- 自定义名称映射，不需要自定义就直接写 [key] AS name
            CASE [key]
                WHEN 'color-1' THEN 'Red'
                WHEN 'color-2' THEN 'Green'
                ELSE [key]
            END AS name,
            [value] AS color
        FROM OPENJSON(Swatch, '$.colors')
        FOR JSON PATH
    ) + '}' AS UpdatedSwatch
FROM #SwatchData;

执行后返回的UpdatedSwatch内容如下，完全匹配目标结构：

{"colors":[{"name":"Red","color":"transparent"},{"name":"Green","color":"transparent"}]}

直接更新原表数据 #

如果需要把转换后的结果直接写回原Swatch列，执行UPDATE即可：

UPDATE #SwatchData
SET Swatch = '{"colors":' +
    (
        SELECT
            CASE [key]
                WHEN 'color-1' THEN 'Red'
                WHEN 'color-2' THEN 'Green'
                ELSE [key]
            END AS name,
            [value] AS color
        FROM OPENJSON(Swatch, '$.colors')
        FOR JSON PATH
    ) + '}';

补充说明 #

• 2014及更早版本的SQL Server没有原生JSON支持，不建议在低版本通过字符串硬拼接实现这类转换，维护成本极高
• 该方案不需要写死色号数量，单条记录不管有多少个color-x键，都能自动完成拆解重组
• 如果需要调整色值，比如把transparent替换为实际十六进制值，直接在[value] AS color位置加CASE或REPLACE逻辑即可

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