PostgreSQL jsonpath中like_regex操作符变量替换报错问题

错误原因 #

你碰到的语法错误并非因为正则中的$字符，而是jsonpath中引用外部变量的语法不正确。PostgreSQL的jsonpath语法里，通过jsonb_build_object传入的变量，需要用$"变量名"的格式引用。

修正后的SQL语句 #

使用正确的变量引用方式，调整后的查询语句如下：

SELECT *
FROM table_data
WHERE jsonb_path_exists(
  fields,
  '$.value[*] ? (@ like_regex $"foo" flag "i")',
  jsonb_build_object('foo', 'ceo')
);

替代方案（更直观） #

如果觉得jsonpath语法繁琐，也可以用jsonb_array_elements展开JSONB数组，结合PostgreSQL原生的正则操作符~*（不区分大小写匹配）来实现：

SELECT DISTINCT td.*
FROM table_data td
JOIN jsonb_array_elements(td.fields) arr ON true
WHERE arr->>'value' ~* 'ceo';

说明 #

• 第一种方案中，flag "i"指定正则匹配为不区分大小写，符合你查询包含ceo值的需求。
• 第二种方案通过展开数组后直接匹配，逻辑更清晰，也更容易调试。
• 注意你的测试数据中有一条JSON格式错误：('[{"value": "Procurement"')，需要补全为('[{"value": "Procurement"}]')才能正常存储和查询。

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