如何在PostgreSQL函数的JSONPath表达式中替换变量值？

你编写的SQL函数如下：

CREATE OR REPLACE FUNCTION public.new_function1(arg1 text DEFAULT 'Miha'::text)
 RETURNS SETOF declarations
 LANGUAGE sql
 STABLE
AS $function$
select * from declarations where data @? '$.** ? (@ == $1)';
$function$

当前直接在JSONPath里写$1无法正确绑定函数参数arg1，但使用字符串字面量（比如"Sergey"）可以正常运行：

select * from declarations where data @? '$.** ? (@ == "Sergey")';

解决方案 #

这里提供两种可行的方法：

方法1：使用format函数构造JSONPath字符串

通过format函数将参数arg1转成符合JSON格式的字符串，再拼接成完整的JSONPath表达式：

CREATE OR REPLACE FUNCTION public.new_function1(arg1 text DEFAULT 'Miha'::text)
 RETURNS SETOF declarations
 LANGUAGE sql
 STABLE
AS $function$
select * from declarations where data @? format('$.** ? (@ == %L)', arg1)::jsonpath;
$function$

• %L会自动给字符串添加双引号并处理转义字符，确保生成的JSONPath语法正确。
• 最后需要将拼接后的字符串转为jsonpath类型，才能被@?操作符识别。

方法2：使用参数化JSONPath查询（PostgreSQL 12+支持）

利用PostgreSQL的参数化JSONPath功能，通过jsonb_path_match函数传递参数，这种方式更安全，避免SQL注入风险：

CREATE OR REPLACE FUNCTION public.new_function1(arg1 text DEFAULT 'Miha'::text)
 RETURNS SETOF declarations
 LANGUAGE sql
 STABLE
AS $function$
select * from declarations where jsonb_path_match(data, '$.** ? (@ == $val)', jsonb_build_object('val', arg1));
$function$

• JSONPath表达式里的$val是自定义参数名，通过jsonb_build_object将函数参数arg1绑定到该参数上。

说明 #

你原来的写法中，JSONPath字符串里的$1是JSONPath自身的变量，并非SQL函数的参数，因此无法直接关联到arg1。上述两种方法分别通过字符串安全拼接和参数化查询的方式，实现了将函数参数传入JSONPath表达式的需求。

内容的提问来源于stack exchange，提问作者Евгений Дружинин