如何在PostgreSQL中构建引用列的JSONPath查询？

你遇到的错误翻译过来是："语法错误，在jsonpath输入末尾意外遇到IDENT_P"，原因是jsonpath表达式里不能直接写SQL列名（比如c.fruit）——PostgreSQL会把它当成jsonpath语法里的标识符，而不是SQL列的值。

要让JSON属性和SQL列值比较，得用参数绑定的方式，把SQL列的值传入jsonpath表达式：

正确写法示例 #

jsonb_path_query(p.blah::jsonb, '$[*] ? (@.fruit == $fruit)', jsonb_build_object('fruit', c.fruit))

完整查询示例 #

比如关联表查询时的用法：

SELECT 
  p.id,
  jsonb_path_query(p.blah::jsonb, '$[*] ? (@.fruit == $fruit)', jsonb_build_object('fruit', c.fruit)) AS matched_fruit_item
FROM 
  products p
JOIN 
  categories c ON p.category_id = c.id;

原理说明 #

jsonpath的解析逻辑和SQL是分离的，它无法识别SQL中的列名。通过jsonb_build_object把SQL列c.fruit的值包装成JSON对象，作为第三个参数传入jsonb_path_query，jsonpath里的$fruit就会被替换成对应列的实际值，这样就能完成比较。

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