如何在PostgreSQL jsonb数组中查询符合多条件的对象字段？

你的问题出在直接对整个orders数组使用->操作符，这种方式无法定位到数组内的单个对象，自然没法确保item_num和catogory（注意你数据里的字段是catogory，拼写要和JSON结构完全一致）属于同一个订单对象。下面提供两种可行的解决方案：

方法一：展开数组后过滤（最直观） #

使用jsonb_array_elements函数将orders数组拆分为独立的行，每行对应一个订单对象，之后就能对单个对象同时校验两个条件：

SELECT
    c.id,
    order_item->>'tracking_num' AS tracking_num
FROM
    customers c,
    jsonb_array_elements(c.jdoc->'orders') AS order_item
WHERE
    order_item->>'item_num' = '15'
    AND order_item->>'catogory' = 'tools'

这种方式会把每个符合条件的订单单独返回一行，如果一个客户有多个符合条件的订单，会生成多行结果。

方法二：使用JSON路径查询（适合复杂场景） #

如果需要更简洁的写法，或者处理更复杂的JSON结构，可以用PostgreSQL的JSON路径功能：

SELECT
    id,
    jsonb_path_query(jdoc, '$.orders[*] ? (@.item_num == "15" && @.catogory == "tools").tracking_num') AS tracking_num
FROM
    customers
WHERE
    jsonb_path_exists(jdoc, '$.orders[*] ? (@.item_num == "15" && @.catogory == "tools")')

• jsonb_path_query负责提取符合条件的订单对象的tracking_num
• jsonb_path_exists用来过滤掉没有符合条件订单的客户，避免返回空的tracking_num结果

关键注意点 #

你原数据中的字段是catogory（拼写缺少字母e），而你的SQL里写的是category，这会导致条件匹配失败，一定要和JSON结构里的字段名完全一致。

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