如何在PostgreSQL中按ID汇总JSON数据内指定国家的观看时长？

别把JSON列展开成250多列，这种方案既不灵活又浪费存储和维护成本。PostgreSQL提供了原生的JSON处理函数，可以动态提取目标国家的观看时长并汇总，以下是具体实现方法：

前提说明 #

假设你的帖子表（posts）包含page_id字段（用于关联目标国家表target_countries），如果原表没有，需要先通过post_id关联到对应的page_id。

方法一：动态拆分JSON并关联求和 #

利用json_each_text()函数将JSON列的键值对拆分为行数据，再关联目标国家表过滤出每个page_id对应的目标国家，最后分组求和：

SELECT
  t.page_id,
  SUM(j.value::bigint) AS total_watchtime_ms
FROM
  posts p
  -- 关联目标国家表，获取每个page_id对应的目标国家
  JOIN target_countries t ON p.page_id = t.page_id
  -- 拆分JSON列：key是国家代码，value是毫秒时长
  JOIN json_each_text(p.watchtime_per_country) j ON j.key = t.target_country
-- 可选：只查询指定的page_id
WHERE
  t.page_id IN ('P01', 'P02')
GROUP BY
  t.page_id;

代码解释： #

• json_each_text(p.watchtime_per_country)：将JSON对象拆分为多行数据，每行包含key（国家双字母缩写）和value（观看时长字符串）。
• j.value::bigint：将字符串类型的时长转换为整数类型，避免求和时出现类型错误。
• 关联target_countries表后，自动过滤出每个page_id需要汇总的目标国家，最后按page_id分组求和得到总观看时长。

方法二：直接提取目标国家值求和（固定场景专用） #

如果只是针对少数固定国家临时求和，也可以直接用->>运算符提取JSON中的值，手动计算总和：

SELECT
  p.page_id,
  -- 用COALESCE处理NULL值，避免无数据时求和出错
  COALESCE((p.watchtime_per_country ->> 'DE')::bigint, 0) +
  COALESCE((p.watchtime_per_country ->> 'JP')::bigint, 0) AS total_watchtime_ms
FROM
  posts p
WHERE
  p.page_id = 'P01'
GROUP BY
  p.page_id;

这种方法适合单次固定查询，但灵活性较差，目标国家变化时需要修改SQL。

为什么不建议展开JSON为250列？ #

• 维护成本极高：新增或移除国家时，需要频繁修改表结构，扩展性极差。
• 查询不灵活：每次更换目标国家都要修改SQL中的列名，无法适配动态需求。
• 存储浪费：大部分国家的数据可能很少用到，占用不必要的存储空间。

性能优化建议 #

如果需要频繁执行这类查询，可以做以下优化：

1. 改用jsonb类型：将watchtime_per_country字段从json改为jsonb，jsonb在查询和索引上的性能更优。
2. 创建GIN索引：对jsonb列创建GIN索引，加速JSON键值查询：

   CREATE INDEX idx_posts_watchtime_jsonb ON posts USING GIN (watchtime_per_country);
   

3. 表达式索引（固定国家场景）：如果经常查询特定国家的时长，可以创建表达式索引：

   CREATE INDEX idx_posts_watchtime_de_jp ON posts ((watchtime_per_country ->> 'DE')::bigint, (watchtime_per_country ->> 'JP')::bigint);
   

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