PostgreSQL如何查询jsonb数组字段并仅保留指定字段输出

以下两种方案均兼容PostgreSQL 12及以上版本，可实现jsonb数组仅保留指定字段返回：

方案1：显式构造需要的字段（推荐，不受多余字段变动影响） #

先将jsonb数组拆分为单个元素，提取需要的字段构造新对象，再聚合回数组，自动兼容空数组场景：

SELECT 
  u.id,
  COALESCE(
    jsonb_agg(
      jsonb_build_object(
        'title', elem->'title',
        'description', elem->'description'
      )
    ),
    '[]'::jsonb
  ) AS posts
FROM Users u
LEFT JOIN jsonb_array_elements(u.posts) elem ON true
GROUP BY u.id;

方案2：删除多余字段（适合待删除字段少的场景） #

使用PostgreSQL 12新增的jsonb数组减号操作符，直接删除不需要的键：

SELECT 
  id,
  COALESCE(posts - 'is_published' - 'some', '[]'::jsonb) AS posts
FROM Users;

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