PostgreSQL：如何用表列值过滤jsonb_path_query查询结果

问题场景 #

原有SQL通过固定值"Dog"过滤JSONB数组中的对象，现在需要替换为使用表列mainpet的值作为过滤条件，但直接字符串拼接的写法无法生效。

初始SQL代码：

WITH pets AS (SELECT 'Dog' AS mainpet, '
[
  {
    "petSpecies": "Dog",
    "mainMeal": "Meat"
  },
  {
    "petSpecies": "Cat",
    "mainMeal": "Milk"
  },
  {
    "petSpecies": "Lizard",
    "mainMeal": "Insects"
  }
]'::jsonb AS petfood)
SELECT pets.petfood,
       jsonb_path_query_first(pets.petfood, '$[*]?(@."petSpecies" == "Dog")."mainMeal"') ->> 0 AS mypetmainmeal
FROM pets

尝试的无效写法：

jsonb_path_query_first(pets.petfood, '$[*]?(@."petSpecies" == "' || pets.mainpet || '")."mainMeal"') ->> 0

正确实现方式 #

PostgreSQL的jsonb_path_query_first支持参数绑定来传入列值，避免字符串拼接的语法问题。具体步骤：

1. 在JSON路径表达式中用$变量名指代外部值
2. 通过vars参数传入包含变量名和对应列值的JSONB对象

完整修正后的SQL：

WITH pets AS (SELECT 'Dog' AS mainpet, '
[
  {
    "petSpecies": "Dog",
    "mainMeal": "Meat"
  },
  {
    "petSpecies": "Cat",
    "mainMeal": "Milk"
  },
  {
    "petSpecies": "Lizard",
    "mainMeal": "Insects"
  }
]'::jsonb AS petfood)
SELECT pets.petfood,
       jsonb_path_query_first(
           pets.petfood,
           '$[*]?(@."petSpecies" == $mainpet)."mainMeal"',
           jsonb_build_object('mainpet', pets.mainpet)
       ) ->> 0 AS mypetmainmeal
FROM pets

原理说明 #

• 直接字符串拼接会导致JSON路径表达式的语法解析错误，尤其是当列值包含特殊字符时，还会引入SQL注入风险
• 参数绑定方式让PostgreSQL正确识别外部变量，自动处理类型转换和转义，确保路径表达式的正确性和安全性

内容的提问来源于stack exchange，提问作者Dhruva Sen Gupta