PG13查询jsonb字段数组元素并返回匹配项的实现方法

现有写法的缺陷 #

你当前使用的双层@>包含判断存在两个问题：

• 仅能校验数组中分别存在带指定location_id、指定practice_area_id的元素，无法保证两个属性属于同一个数组对象，会出现误匹配
• @>操作符仅支持精确等值匹配，无法实现remaining_views > 0这类数值范围判断

推荐实现方案（PG13原生支持，性能最优） #

直接使用PG13内置的JSON路径查询能力，一步完成行过滤、匹配对象提取，写法简洁且可以支持索引优化：

SELECT
  t.*,
  jsonb_path_query(
    t.meta->'subscriptions',
    '$[*] ? (@.location_id == 14555 && @.practice_area_id == 39 && @.remaining_views > 0)'
  ) AS matched_subscription
FROM mytable t
WHERE
  t.meta @? '$.subscriptions[*] ? (@.location_id == 14555 && @.practice_area_id == 39 && @.remaining_views > 0)';

语句说明 #

• @?是jsonb路径存在判断符，只有数组中存在同一个对象同时满足所有筛选条件时，对应行才会被返回，从根源上避免了多条件拆分匹配的误判问题
• jsonb_path_query会把符合条件的数组对象提取为matched_subscription字段返回；如果单行中存在多个匹配的数组对象，会自动拆分为多行展示
• 如果不需要拆分多行，仅需要返回第一个匹配的对象，可以把jsonb_path_query替换为jsonb_path_query_first，无匹配时该字段返回null，不会丢失主表行数据

性能优化 #

如果表数据量较大，可以给jsonb字段创建GIN索引加速查询：

CREATE INDEX idx_mytable_meta_subs ON mytable USING GIN ((meta->'subscriptions') jsonb_path_ops);

兼容写法（无JSON路径依赖） #

如果需要兼容不支持JSON路径的旧版本PostgreSQL，可以通过数组拆分的方式实现，写法相对冗余，性能弱于JSON路径方案：

SELECT
  m.*,
  sub_item AS matched_subscription
FROM mytable m,
     jsonb_array_elements(m.meta->'subscriptions') AS sub_item
WHERE
  (sub_item->>'location_id')::int = 14555
  AND (sub_item->>'practice_area_id')::int = 39
  AND (sub_item->>'remaining_views')::int > 0;

注意：该写法会全表扫描拆分jsonb数组，数据量超过10万行时查询效率会明显下降，优先选择JSON路径方案。

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