如何在PostgreSQL中从JSON数组计算2xx状态码文档计数占比

要实现你需要的计算，可以通过PostgreSQL的JSON函数先展开数组、筛选目标状态，再进行聚合计算，具体SQL语句如下：

SELECT 
  SUM((status->>'doc_count')::integer)::numeric / SUM(total_access)::numeric AS success_rate
FROM 
  my_table,
  json_array_elements(http_status) AS status
WHERE 
  status->>'key' LIKE '2%';

语句说明： #

• json_array_elements(http_status)：将每行的http_status JSON数组拆分为单独的行记录，每个数组元素对应一行，用别名status指代拆分后的单个JSON对象。
• status->>'key' LIKE '2%'：筛选出状态码以2开头的记录（即所有2xx系列状态）。
• SUM((status->>'doc_count')::integer)：将筛选出的doc_count字段转换为整数后求和，得到所有2xx状态的总访问数。
• SUM(total_access)：计算所有行的total_access总和。
• 转换为numeric类型是为了避免PostgreSQL的整数除法取整问题，确保得到精确的小数结果。

执行该查询后，就能得到你预期的0.9728454092197281这类精确结果。

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