PostgreSQL 13查询嵌套JSONB列保留结构删除冗余键的实现方案

方案1：使用JSONB操作符实现（结构兼容性好） #

这种方式不需要硬写完整JSON结构，只需要指定要删除的键，原JSON其他结构变动时兼容度更高，容错性也更好，就算指定要删除的键不存在也不会报错：

SELECT
  data 
  -- 删除顶层不需要的键
  - 'something' - 'something_else'
  -- 替换处理后的video数组：删除每个元素的type字段
  || jsonb_build_object(
    'video', (
      SELECT jsonb_agg(elem - 'type')
      FROM jsonb_array_elements(data->'video') elem
    )
  )
  -- 替换处理后的image.candidates数组：删除每个元素的width、height字段
  || jsonb_build_object(
    'image', jsonb_build_object(
      'candidates', (
        SELECT jsonb_agg(elem - 'width' - 'height')
        FROM jsonb_array_elements(data->'image'->'candidates') elem
      )
    )
  ) AS processed_data
FROM your_table;

上述代码里的your_table替换为你的表名，data替换为你的JSONB列名即可直接使用。

方案2：直接重构JSON（执行效率更高） #

如果你的JSON结构是固定的，直接按需要保留的字段重构JSON的执行性能会更好，逻辑也更直观：

SELECT
  jsonb_build_object(
    'id', data->'id',
    'foo', data->'foo',
    'video', (
      SELECT jsonb_agg(
        jsonb_build_object(
          'id', elem->'id',
          'width', elem->'width',
          'height', elem->'height'
        )
      )
      FROM jsonb_array_elements(data->'video') elem
    ),
    'image', jsonb_build_object(
      'candidates', (
        SELECT jsonb_agg(
          jsonb_build_object('scans_profile', elem->'scans_profile')
        )
        FROM jsonb_array_elements(data->'image'->'candidates') elem
      )
    )
  ) AS processed_data
FROM your_table;

两种方案都仅作用于查询逻辑，不会修改原表存储的数据。如果需要保留的字段少、需要删除的字段多，优先选方案2；如果需要删除的字段少、原JSON其他字段可能会新增变动，优先选方案1。

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