如何用SQL为Templates表JSON列的嵌套对象添加新属性

针对不同主流SQL数据库，提供对应的修改方案：

SQL Server #

利用OPENJSON解析JSON结构，FOR JSON重新生成修改后的JSON：

UPDATE Templates
SET JsonContent = (
    SELECT 
        original.Name,
        CASE 
            WHEN original.Options IS NOT NULL THEN (
                SELECT 
                    opt.Name,
                    opt.Value,
                    CAST('false' AS BIT) AS IsDeleted
                FROM OPENJSON(original.Options)
                WITH (
                    Name NVARCHAR(100),
                    Value INT
                ) AS opt
                FOR JSON PATH
            )
            ELSE NULL
        END AS Options
    FROM OPENJSON(JsonContent)
    WITH (
        Name NVARCHAR(100),
        Options NVARCHAR(MAX) AS JSON
    ) AS original
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
WHERE ISJSON(JsonContent) = 1; -- 仅处理有效JSON数据

MySQL #

借助JSON_TABLE拆解JSON数组，JSON_ARRAYAGG和JSON_OBJECT重组修改后的内容：

-- 假设表有主键id用于关联更新
UPDATE Templates t
JOIN (
    SELECT 
        t.id,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'Name', j.Name,
                'Options', 
                CASE 
                    WHEN j.Options IS NOT NULL THEN (
                        SELECT JSON_ARRAYAGG(
                            JSON_OBJECT(
                                'Name', opt.Name,
                                'Value', opt.Value,
                                'IsDeleted', FALSE
                            )
                        )
                        FROM JSON_TABLE(
                            j.Options,
                            '$[*]' COLUMNS(
                                Name VARCHAR(100) PATH '$.Name',
                                Value INT PATH '$.Value'
                            )
                        ) AS opt
                    )
                    ELSE NULL
                END
            )
        ) AS NewJsonContent
    FROM Templates t
    JOIN JSON_TABLE(
        t.JsonContent,
        '$[*]' COLUMNS(
            Name VARCHAR(100) PATH '$.Name',
            Options JSON PATH '$.Options'
        )
    ) AS j
    GROUP BY t.id
) AS updated ON t.id = updated.id
SET t.JsonContent = updated.NewJsonContent;

PostgreSQL #

使用jsonb_array_elements拆分JSON数组，通过||操作符给对象追加属性：

UPDATE Templates
SET JsonContent = (
    SELECT jsonb_agg(
        jsonb_build_object(
            'Name', elem->>'Name',
            'Options',
            CASE
                WHEN elem->'Options' IS NOT NULL AND elem->'Options' != 'null'::jsonb THEN (
                    SELECT jsonb_agg(opt || jsonb_build_object('IsDeleted', false))
                    FROM jsonb_array_elements(elem->'Options') AS opt
                )
                ELSE elem->'Options'
            END
        )
    )
    FROM jsonb_array_elements(JsonContent::jsonb) AS elem
)::json;
-- 若原列类型为jsonb，可去掉末尾的::json转换

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