在PostgreSQL中聚合JSON

Question

我有一个包含以下内容的json列:

{
  "pages": "64",
  "stats": {
    "1": { "200": "55", "400": "4" },
    "2": { "200": "1" },
    "3": { "200": "1", "404": "13" },
  }
}

“stats”是包含http状态码和计数的集合（大小不一）。

我想将这些统计信息聚合到两个计算列中 - 一个用于200响应的总数，另一个用于所有响应的总数（包括200）。

- Will Jenkins

Answer 1

您可以使用两个侧向连接来展开内部对象，然后进行条件聚合：

select 
    sum(z.cnt::int) no_responses,
    sum(z.cnt::int) filter(where z.code::int = 200) no_200_responses
from mytable t
cross join lateral jsonb_each(t.data -> 'stats') as x(kx, obj)
cross join lateral jsonb_each_text(x.obj) as z(code, cnt)

DB Fiddle上的演示：

无响应 | 非200响应
-----: | -------:
   74  |     57

- GMB

---

那真的很接近 - 謝謝！- 但我不需要聚合所有行的結果，我需要每行的總計作為一個計算列。 - Will Jenkins

@WillJenkins：好的，我在想。如果你的表的主键是id，那么你只需要在查询结束时按id进行分组（你可能还想将此列添加到select列表中）。 - GMB

boom! that's it, nice one - Will Jenkins