Postgres 14 动态值构造JSONB查询JSON数组返回0结果问题

你写的动态查询语句返回0条结果，核心有两个问题：

1. to_jsonb()接收文本类型输入时，会将输入值整体转换为JSON字符串类型，而不会把文本内容解析为JSON结构。你可以单独执行测试语句验证：

   SELECT to_jsonb('[{"search":"findthis"}]'::text);
   

   执行结果为"[{\"search\":\"findthis\"}]"——这是一个带转义的JSON字符串，不是你预期的「包含search字段对象的数组」结构，和左侧的JSON数组做包含匹配时自然永远无法命中。
2. 直接字符串拼接构造JSON本身存在隐患：如果getText()返回值包含双引号、反斜杠等特殊字符，会直接导致JSON语法错误，还存在SQL注入风险。

你之前硬编码的写法能正常运行，是因为单引号包裹的JSON字面量会被Postgres隐式解析为jsonb类型的数组结构，和动态拼接后传参给to_jsonb(text)的逻辑完全不同。

推荐使用Postgres内置的JSON构造函数生成匹配条件，完全规避类型错误、转义错误和注入风险，写法如下：

SELECT 1
FROM test
WHERE data->'haystack' @> jsonb_build_array(
  jsonb_build_object('search', getText())
);

这种写法下，Postgres会自动处理getText()返回值的转义，生成结构完全正确的jsonb数组作为匹配条件，和你硬编码值的查询逻辑完全一致。

如果你的查询场景更复杂，也可以使用PG12及以上版本支持的JSON路径查询语法实现相同逻辑，可读性更强：

SELECT 1
FROM test
WHERE jsonb_path_exists(
  data->'haystack',
  '$[*] ? (@.search == $target)',
  jsonb_build_object('target', getText())
);

不建议使用任何手动字符串拼接构造JSON的写法，即使通过::jsonb强制类型转换能临时跑通，遇到特殊字符时也会出现不可预期的错误。

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