PostgreSQL 9.6中如何将JSON对象所有数值乘2？

问题分析 #

你的需求是将text列中存储的任意结构JSON里的所有数值（包括数值类型和字符串形式的数字）乘2，之前的两种方法存在以下问题：

• 方法1错误原因：jsonb_object_keys仅能处理JSON对象，遇到标量值（如age:10.0）或数组时直接报错，且硬编码多层嵌套的方式无法适配动态变化的JSON结构。
• 方法2错误原因：regexp_replace的捕获组\1无法在CAST/CONCAT中直接引用（PostgreSQL会将'\1'视为普通字符串），且正则处理JSON极易误替换（比如字符串中的数字），也无法覆盖浮点数、负数等场景。

解决方案：递归遍历+动态更新 #

使用递归CTE遍历JSON的所有节点，识别并更新数值，最后重新构建完整的JSON对象。以下是可直接执行的更新语句：

WITH RECURSIVE json_traversal AS (
    -- 初始化：获取目标行的原始JSON
    SELECT
        values_detail::jsonb AS root_json,
        values_detail::jsonb AS current_json,
        ARRAY[]::text[] AS path
    FROM schema.table
    WHERE id = '1afd3d7e-d05v-4d63-9cef-8fb9f6f9514f'
    
    UNION ALL
    
    -- 递归遍历JSON对象的每个键
    SELECT
        jt.root_json,
        CASE
            WHEN jsonb_typeof(jt.current_json -> key) IN ('object', 'array') THEN jt.current_json -> key
            ELSE '{}'::jsonb
        END AS current_json,
        jt.path || key AS path
    FROM json_traversal jt,
         jsonb_object_keys(jt.current_json) AS key
    WHERE jsonb_typeof(jt.current_json) = 'object'
    
    UNION ALL
    
    -- 递归遍历JSON数组的每个元素（如果存在数组）
    SELECT
        jt.root_json,
        CASE
            WHEN jsonb_typeof(jt.current_json -> idx) IN ('object', 'array') THEN jt.current_json -> idx
            ELSE '{}'::jsonb
        END AS current_json,
        jt.path || idx::text AS path
    FROM json_traversal jt,
         generate_series(0, jsonb_array_length(jt.current_json) - 1) AS idx
    WHERE jsonb_typeof(jt.current_json) = 'array'
),
updated_values AS (
    -- 处理每个节点的值：数值乘2，字符串数字转数值乘2再转回字符串
    SELECT
        path,
        root_json,
        CASE
            -- 处理数值类型（整数、浮点数）
            WHEN jsonb_typeof(root_json #> path) IN ('number') THEN (root_json #> path)::numeric * 2
            -- 处理字符串形式的数字（匹配整数/浮点数格式）
            WHEN jsonb_typeof(root_json #> path) = 'string' AND (root_json #> path)::text ~ '^[0-9]+(\.[0-9]+)?$' THEN ((root_json #> path)::text::numeric * 2)::text
            -- 非数字类型保持原样
            ELSE root_json #> path
        END AS new_value
    FROM json_traversal jt
    WHERE jsonb_typeof(root_json #> path) NOT IN ('object', 'array')
),
build_json AS (
    -- 从最底层节点开始，递归构建更新后的JSON
    SELECT
        path,
        CASE
            WHEN jsonb_typeof(new_value) = 'string' THEN new_value::jsonb
            ELSE to_jsonb(new_value)
        END AS json_part
    FROM updated_values
    WHERE array_length(path, 1) = (SELECT max(array_length(path, 1)) FROM updated_values)
    
    UNION ALL
    
    SELECT
        b.path[1:array_length(b.path, 1)-1] AS path,
        jsonb_set(
            COALESCE(b2.json_part, '{}'::jsonb),
            ARRAY[b.path[array_length(b.path, 1)]],
            b.json_part
        ) AS json_part
    FROM build_json b
    LEFT JOIN build_json b2 ON b.path[1:array_length(b.path, 1)-1] = b2.path
    WHERE array_length(b.path, 1) > 1
)
-- 执行更新，将构建好的JSON转回text类型
UPDATE schema.table
SET values_detail = (SELECT json_part::text FROM build_json WHERE array_length(path, 1) = 0)
WHERE id = '1afd3d7e-d05v-4d63-9cef-8fb9f6f9514f';

关键特性 #

• 适配任意嵌套深度、任意键名的JSON结构
• 同时处理原生数值类型和字符串形式的数字
• 保留非数字类型的内容（如字符串、布尔值）完全不变

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