如何在PostgreSQL中将JSON列指定字段的特定值移至另一字段？

针对你描述的users表中JSON类型列foo的需求，我们可以利用PostgreSQL的JSONB函数来完成指定元素的移动操作，以下是具体实现方案：

核心SQL语句 #

UPDATE users
SET foo = jsonb_set(
    jsonb_set(
        COALESCE(foo, '{}'::jsonb),
        '{field1}',
        (
            SELECT jsonb_agg(elem)
            FROM jsonb_array_elements(COALESCE(foo->'field1', '[]'::jsonb)) elem
            WHERE elem::text NOT IN ('"bar2"', '"bar3"')
        )
    ),
    '{field2}',
    (
        COALESCE(foo->'field2', '[]'::jsonb) ||
        (
            SELECT jsonb_agg(elem)
            FROM jsonb_array_elements(COALESCE(foo->'field1', '[]'::jsonb)) elem
            WHERE elem::text IN ('"bar2"', '"bar3"')
        )
    )
);

语句解释 #

1. 空值与默认值处理：

   • COALESCE(foo, '{}'::jsonb)：确保foo列为空时，默认按空JSON对象处理
   • COALESCE(foo->'field1', '[]'::jsonb)/COALESCE(foo->'field2', '[]'::jsonb)：当field1或field2不存在时，默认按空数组处理

2. 更新field1：

   • jsonb_array_elements将field1的数组拆分为单个元素行
   • 过滤掉bar2和bar3（注意JSON字符串转文本后会带双引号，所以匹配"bar2"和"bar3"）
   • jsonb_agg将剩余元素重新聚合为数组，作为新的field1值

3. 更新field2：

   • 提取field1中的bar2和bar3元素并聚合为数组
   • 使用||运算符将该数组与原field2数组合并，作为新的field2值

4. 嵌套jsonb_set：通过两次jsonb_set完成对foo列中两个字段的更新

特殊场景验证 #

• 当foo为{}时：执行后foo会变为{"field1": [], "field2": []}
• 当field1不存在时：field1会被设为空数组，field2保持原状态（无元素可移动）
• 当field2不存在时：会将field1中指定元素移动到新创建的field2数组中

如果你的数据库是MySQL，处理逻辑类似但需要使用JSON_REMOVE、JSON_ARRAY_APPEND等函数，由于MySQL的JSON数组操作灵活性较低，实现起来会更繁琐，这里优先提供PostgreSQL的最优解。

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