查询JSON组合会返回奇数结果 [英] Querying combinations of JSON returns odd results

问题描述

这是此问题的后续操作.我似乎遇到了一个极端情况，我不明白为什么我得到了错误的结果.使用链接问题中的数据， 我可以将它们分为使用相同专辑，src和背景的组合.

This is a followup to this question. I seem to have come across an edge case and I don't understand why I'm getting the wrong results. Using the data from the linked question, I can group them into combinations that use the same album, src, and background.

例如，使用以下数据:

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;

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

I get these results, which are incorrect:

  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分组以匹配使用相同文件，专辑和背景的组合.

If I change the background values so that they are varied, then it does work as expected but the counts are still off. So what I'm gather is the grouping by background may be a cause for the issue. But I don't know why. I can do without the counts, I just mainly need the ids grouped for matching combinations that use the same file, album, and background.

修改 我不得不编辑我的问题.事实证明我的样本数据有错误，并且我从未获得正确的结果.因此，我正在寻找可能的查询.

Edit I had to edit my question. It turns out my sample data had an error and I was never getting the correct results. So I am looking for a query that works if possible.

推荐答案

来自Postgresql的IRC频道的好心人帮助找到了答案并制定了正确的查询.功劳实际上是他的，而不是我的.

A kind person from Postgresql's IRC channel helped find the answer and craft the correct query. The credit is actually his, not mine.

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

He helped realize that the albums and srcs should be added to arrays for comparison. For instance:

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;

我不知道这是否是最好的方法，但是它可以工作.欢迎提出建议.

I don't know if this is the best way of doing it but it works. Suggestions are welcome.

这篇关于查询JSON组合会返回奇数结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！