PostgreSQL：如何用另一列的JSON值替换文本列中的参数？

方法1：递归CTE逐次替换 #

适合临时场景，无需创建函数：

with tmp_table(json_row, message) as (
    values 
    ('{"key1": 1, "key2":"first"}'::jsonb, 'this ${key2} is a test of ${key1}'),
    ('{"key1": 2, "key2":"second"}'::jsonb, 'this ${key2} is a test'),
    ('{"key1": 3, "key2":"third"}'::jsonb, 'this ${key1} is a test of ${key2}')
),
recursive_replace as (
    select 
        json_row,
        message as original_message,
        message as replaced_message,
        regexp_match(message, '\$\{([^}]+)\}') is not null as has_unreplaced
    from tmp_table
    union all
    select 
        r.json_row,
        r.original_message,
        regexp_replace(r.replaced_message, '\$\{([^}]+)\}', r.json_row ->> '\1', 'g'),
        regexp_match(regexp_replace(r.replaced_message, '\$\{([^}]+)\}', r.json_row ->> '\1', 'g'), '\$\{([^}]+)\}') is not null
    from recursive_replace r
    where r.has_unreplaced = true
)
select 
    json_row,
    original_message,
    replaced_message as final_message
from recursive_replace
where has_unreplaced = false;

方法2：自定义函数封装替换逻辑 #

适合重复使用场景，代码更紧凑：

create or replace function replace_json_params(json_data jsonb, template text)
returns text as $$
declare
    matched_key text;
    result text := template;
begin
    loop
        matched_key := regexp_match(result, '\$\{([^}]+)\}');
        if matched_key is null then
            exit;
        end if;
        result := regexp_replace(result, '\$\{' || matched_key || '\}', json_data ->> matched_key, 'g');
    end loop;
    return result;
end;
$$ language plpgsql immutable;

-- 调用函数查询
with tmp_table(json_row, message) as (
    values 
    ('{"key1": 1, "key2":"first"}'::jsonb, 'this ${key2} is a test of ${key1}'),
    ('{"key1": 2, "key2":"second"}'::jsonb, 'this ${key2} is a test'),
    ('{"key1": 3, "key2":"third"}'::jsonb, 'this ${key1} is a test of ${key2}')
)
select 
    json_row,
    message,
    replace_json_params(json_row, message) as final_message
from tmp_table;

关键逻辑说明 #

• 正则\$\{([^}]+)\}精准匹配${key}格式的参数，捕获组提取键名；
• 用jsonb ->> 'key'获取对应键的文本值，自动兼容数字、字符串等JSON类型；
• 递归CTE或循环函数会反复执行替换，直到所有${key}参数都被替换完成。

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