PostgreSQL 12 如何从json列筛选指定嵌套键并保留原有结构

实现方案 #

你需要先遍历JSON的一级键，为每个一级键构造仅包含目标嵌套键的子对象，再聚合为最终结构即可，PostgreSQL 12 可直接运行以下查询：

方案1：固定保留嵌套键（写法更简洁）

SELECT
  id,
  (
    SELECT json_object_agg(
      outer_key,
      json_build_object(
        'nested_3', outer_val->'nested_3',
        'nested_5', outer_val->'nested_5'
      )
    )
    FROM json_each(json_col) AS t(outer_key, outer_val)
  ) AS json_col
FROM my_table;

如果你的json_col是jsonb类型，将对应函数替换为jsonb_each、jsonb_object_agg、jsonb_build_object可获得更高执行效率。

方案2：动态保留嵌套键（扩展性更强）

如果后续需要调整保留的嵌套键，可使用内层子查询过滤的写法，仅修改IN后的键列表即可：

SELECT
  id,
  (
    SELECT json_object_agg(
      outer_key,
      (
        SELECT json_object_agg(inner_key, inner_val)
        FROM json_each(outer_val) AS it(inner_key, inner_val)
        WHERE inner_key IN ('nested_3', 'nested_5')
      )
    )
    FROM json_each(json_col) AS t(outer_key, outer_val)
  ) AS json_col
FROM my_table;

原写法问题说明 #

1. 结构反转是因为你最初的查询将nested_3/nested_5作为顶层聚合键，把所有一级键的对应值聚合到了这两个键下
2. 嵌套聚合报错是因为PostgreSQL不允许同一查询层级嵌套调用聚合函数，将内层聚合放到独立子查询中即可规避该限制。

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