PostgreSQL中jsonb_set函数动态路径的变量传递方案问询

核心原理 #

jsonb_set的第二个路径参数要求是**text[]（文本数组）**类型，而非普通字符串，因此需要将逗号分隔的路径字符串转换成数组类型才能适配参数要求。

方法一：用string_to_array转换常规路径 #

适用于路径键不含逗号的常规场景：

1. PL/pgSQL函数中复用 #

创建可接收路径参数的通用更新函数：

CREATE OR REPLACE FUNCTION update_user_data(p_id INT, p_path TEXT, p_new_value JSONB)
RETURNS VOID AS $$
BEGIN
  UPDATE users
  SET data = jsonb_set(data, string_to_array(p_path, ','), p_new_value, FALSE)
  WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

调用示例：

-- 传入路径"user,name"，更新值为"sunil"
SELECT update_user_data(1, 'user,name', '"sunil"'::JSONB);

2. 直接SQL中使用变量（以psql为例） #

在命令行通过变量传递路径：

\set path_str 'user,name'
UPDATE users
SET data = jsonb_set(data, string_to_array(:'path_str', ','), '"sunil"'::JSONB, FALSE)
WHERE id = 1;

方法二：处理含特殊字符的路径 #

如果路径中的键包含逗号（比如键为user,admin），可改用JSON数组格式传递路径，再转换为数组：

示例代码 #

CREATE OR REPLACE FUNCTION update_user_data_special(p_id INT, p_path_json TEXT, p_new_value JSONB)
RETURNS VOID AS $$
BEGIN
  UPDATE users
  SET data = jsonb_set(data, jsonb_array_text(p_path_json::JSONB), p_new_value, FALSE)
  WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

调用示例：

-- 路径对应键为"user,admin"和"name"
SELECT update_user_data_special(1, '["user,admin", "name"]', '"sunil"'::JSONB);

关键注意点 #

• 确保新值为JSONB类型，字符串值需用双引号包裹并转换（如'"sunil"'::JSONB）
• jsonb_set的第四个参数设为FALSE时，路径不存在则跳过更新；设为TRUE会自动创建不存在的路径

内容的提问来源于stack exchange，提问作者Sunil Kumar Regar