PostgreSQL中无需展开或全表过滤，能否筛选JSONB内数组？

可以直接使用PostgreSQL的jsonb_path_query_array函数实现需求，无需展开数组或全表过滤，性能比展开重组的方式更高。

原表结构 #

期望结果 #

实现SQL #

SELECT
  id,
  jsonb_build_object(
    'names',
    CASE
      WHEN jsonb_path_query_array(jsnob, '$.names[*] ? (@ like_regex "^a")') = '[]'::jsonb THEN NULL
      ELSE jsonb_path_query_array(jsnob, '$.names[*] ? (@ like_regex "^a")')
    END
  ) AS jsnob
FROM your_table;

关键说明 #

• jsonb_path_query_array：通过JSON路径表达式直接筛选names数组中以a开头的元素，返回筛选后的数组。路径表达式$.names[*] ? (@ like_regex "^a")的含义是遍历names数组的所有元素，匹配以a开头的项。
• CASE分支：处理筛选后为空数组的场景，将names字段设为null，与需求一致。
• jsonb_build_object：重新构造包含处理后names的JSONB对象，保持原字段的结构。

这种方式避免了展开数组带来的行数据膨胀和后续的分组聚合操作，在数据量较大时能显著提升查询效率。

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