Запрос комбинации JSON возвращает странные результаты

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 может быть причиной проблемы. Но я не знаю почему. Я могу обойтись без счета, мне просто нужны идентификаторы, сгруппированные для соответствующих комбинаций, которые используют один и тот же файл, альбом и фон.

Редактировать Я должен был отредактировать свой вопрос. Оказывается, в моих данных пробы произошла ошибка, и я никогда не получал правильных результатов. Поэтому я ищу запрос, который работает, если это возможно.

Answer 1

Добрый человек из IRC-канала Postgresql помог найти ответ и выработать правильный запрос. Кредит на самом деле его, а не мой.

Он помог понять, что альбомы и srcs должны быть добавлены в массивы для сравнения. Например:

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;

Я не знаю, является ли это лучшим способом сделать это, но это работает. Предложения приветствуются.

Answer 2

Прежде всего, у вас есть опечатка, изменить 'scr' в 'src', Но ваш запрос верный, просто посмотрите на ваш запрос без группировки:

select
     r.rep_id, r.data->>'background' as background, o->>'album' as album, o->>'src' as src
from reports r, json_array_elements(r.data->'objects') o;

------------------------------------------------------------
REP_ID  BACKGROUND      ALBUM   SRC
1       background.png      1   fooA.png
1       background.png      2   barB.png
2       background.png      2   barB.png
2       background.png      2   barB.png

Answer 3

Если вы рассчитываете разные значения для rep_id, вы получите количество строк, в которых произошла уникальная комбинация.

SELECT distinct array_agg(distinct r.rep_id) AS ids, count(distinct r.rep_id) AS ct, array[r.data->>'background', o->>'album', o->>'src'] as combination
FROM   reports r
  , json_array_elements(r.data->'objects') o
GROUP  BY r.data->>'background'
   , o->>'album'
   , o->>'src'
ORDER  BY 2 DESC

Результат по первому набору данных:

ids     ct  combination
{1,2,3} 3   {background.png,1,fooA.png}
{1,3}   2   {background.png,2,barB.png}
{2}     1   {background.png,2,barC.png}
{4}     1   {backgroundA.png,1,fooA.png}
{4}     1   {backgroundA.png,3,barB.png}

Результат на втором наборе данных:

ids     ct  combination
{1,2}   2   {background.png,2,barB.png}
{1}     1   {background.png,1,fooA.png}