PostgreSQL 15.2：按jsonb数组键检索行时报错的解决咨询

错误原因 #

你的gates字段是PostgreSQL原生数组类型jsonb[]（即_jsonb），而jsonb_path_query_array函数要求第一个参数是单个jsonb类型值（比如JSON内部的数组），类型不匹配导致了function jsonb_path_query_array(jsonb[], unknown) does not exist错误。同时原WHERE子句的@>操作符也不能直接用于jsonb[]类型。

正确解决方案 #

方案1：展开原生数组后筛选聚合 #

通过unnest展开jsonb[]类型的字段，筛选匹配serial_number的元素，再聚合回JSONB数组：

SELECT 
  l.*,
  jsonb_agg(gate) AS matching_gates
FROM locations l
LEFT JOIN unnest(l.gates) AS gate ON gate @> '{"serial_number": "123"}'
WHERE gate IS NOT NULL
GROUP BY l.id; -- 替换为你的表主键或所有非聚合字段

如果只需要匹配的gates数组：

SELECT jsonb_agg(gate) AS gates
FROM locations, unnest(locations.gates) AS gate
WHERE gate @> '{"serial_number": "123"}'
GROUP BY locations.id;

方案2：转换原生数组为JSONB内部数组后使用路径查询 #

先把jsonb[]类型转成单个jsonb类型的数组对象，再使用jsonb_path_query_array：

SELECT 
  jsonb_path_query_array(to_jsonb(gates), '$[*] ? (@.serial_number == "123")') AS gates
FROM locations
WHERE EXISTS (
  SELECT 1 FROM unnest(gates) g WHERE g @> '{"serial_number": "123"}'
);

这里to_jsonb(gates)将PostgreSQL原生的jsonb[]数组转换为单个JSONB对象（内部是JSON数组），满足jsonb_path_query_array的参数要求；WHERE子句用EXISTS配合unnest判断是否存在匹配元素。

补充说明 #

你的字段示例值{"{\"ip_address\": \"192.168.1.10\", \"serial_number\": \"123\", \"gate_description\": \"Fourth gate\"}"}是PostgreSQLjsonb[]类型的存储形式，每个元素是独立的jsonb对象，并非单个jsonb内部的数组，这是核心的类型差异点。

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