sql - 查询 JSON 的组合返回奇怪的结果

Question

这是此 question 的跟进.我似乎遇到了一个边缘案例，我不明白为什么我会得到错误的结果。使用链接问题中的数据，我可以将它们组合成使用相同相册、源代码和背景的组合。

例如，使用这个数据:

CREATE TABLE reports (rep_id int primary key, data json);
INSERT INTO reports (rep_id, data)
VALUES 
  (1, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 2, "src":"barB.png", "pos": "top"}],   "background":"background.png"}'),
  (2, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 2, "src":"barC.png", "pos": "top"}],   "background":"background.png"}'),
  (3, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "middle"},{"album": 2, "src":"barB.png", "pos": "middle"}],"background":"background.png"}'),
  (4, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"},   {"album": 3, "src":"barB.png", "pos": "top"}],   "background":"backgroundA.png"}')
;

这是查询:

SELECT distinct array_agg(distinct r.rep_id) AS ids, count(*) AS ct
FROM   reports r
  , json_array_elements(r.data->'objects') o
GROUP  BY r.data->>'background'
   , o->>'album'
   , o->>'src'
ORDER  BY count(*) DESC
LIMIT  5;

我得到了这些不正确的结果:

  ids    | ct 
---------+----
 {1,2,3} |  3
 {1,3}   |  2
 {2}     |  1
 {4}     |  1

我要的是这个

  ids    | ct 
---------+----
 {1,3}   |  2
 {2}     |  1
 {4}     |  1

如果我更改 background 值以使它们不同，那么它确实按预期工作但计数仍然关闭。所以我收集到的是按 background 分组可能是问题的原因。但我不知道为什么。我可以不用计数，我只需要将 ID 分组以匹配使用相同文件、相册和背景的组合。

编辑我不得不编辑我的问题。事实证明我的示例数据有错误，我从未得到正确的结果。因此，我正在寻找一个尽可能有效的查询。

Answer 1

来自 Postgresql 的 IRC channel 的好心人帮助找到了答案并设计了正确的查询。功劳其实是他的，不是我的。

他帮助意识到专辑和 src 应该添加到数组中以进行比较。例如:

SELECT array_agg(rep_id), count(*) AS ct
FROM (SELECT rep_id, 
             data->>'background' as background, 
             array_agg(o->>'album' order by o->>'album') as albums, 
             array_agg(o->>'src' order by o->>'album') as srcs  
           FROM reports r, 
           json_array_elements(r.data->'objects') o 
           GROUP BY rep_id) s 
GROUP BY background, albums, srcs
ORDER BY count(*) DESC
LIMIT 5;

我不知道这是否是最好的方法，但它确实有效。欢迎提出建议。