PostgreSQL中计算JSONB数组内分数平均值的查询方法

核心查询语句 #

SELECT
  gameid,
  AVG((score_item ->> 'score')::numeric) AS averagescore
FROM
  games,
  jsonb_array_elements(gameinfo -> 'scores') AS score_item
GROUP BY
  gameid
ORDER BY
  gameid;

语句说明 #

• jsonb_array_elements(gameinfo -> 'scores')：将gameinfo字段里的scores JSONB数组拆分成单行记录，每个数组元素对应一行，别名为score_item。
• score_item ->> 'score'：提取每个评分项中的score值（返回文本类型），再通过::numeric转换为数值类型，确保能参与平均值计算。
• AVG(...)：对每个游戏的所有评分计算平均值。
• GROUP BY gameid：按游戏ID分组，保证每个游戏仅返回一条平均结果。
• ORDER BY gameid：按游戏ID排序，与期望输出结构一致。

兼容空数组的版本 #

如果存在scores数组为空的游戏，可使用左连接确保这类游戏也出现在结果中，并用COALESCE将空平均值替换为0：

SELECT
  g.gameid,
  COALESCE(AVG((score_item ->> 'score')::numeric), 0) AS averagescore
FROM
  games g
LEFT JOIN
  jsonb_array_elements(g.gameinfo -> 'scores') AS score_item ON true
GROUP BY
  g.gameid
ORDER BY
  g.gameid;

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