Комбинации запросов с вложенным массивом записей в типе данных JSON
Я работаю над приложением Rails, которое использует Postgres JSON тип данных. У меня есть столбец JSON под названием data в таблице под названием reports, Допустим, у меня есть несколько записей, как это:
Entry 1: {"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 2, "src":"barB.png", "pos": "top"}], "background":"background.png"}
Entry 2: {"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 2, "src":"barC.png", "pos": "top"}], "background":"bacakground.png"}
Entry 3: {"objects":[{"album": 1, "src":"fooA.png", "pos": "middle"},{"album": 2, "src":"barB.png", "pos": "middle"}],"background":"background.png"}
Entry 4: {"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 3, "src":"barB.png", "pos": "top"}], "background":"backgroundA.png"}
То, что я хотел бы сделать, это вернуть различные комбинации записей, которые имеют один и тот же альбом, src и фон (ПРИМЕЧАНИЕ: в пределах objects узел, порядок элементов массива не имеет значения). Например, запрос должен соответствовать записям 1,3 как одной группе, записи 2 как другой и т. Д. Цель состоит в том, чтобы найти 3 наиболее распространенные комбинации. Я знаю, как сделать это с помощью Ruby, но мне нужно было бы запросить большую выборку записей, а затем перебрать их все. Кажется, более эффективно использовать Postgres, если он может справиться с этой задачей. Мне не хватает специалиста по SQL, чтобы понять, возможно ли это.
Это результат, который я ищу. В objects, записи 1 и 3 содержат {"album": 1, "src":"fooA.png"}, {"album": 2, "src":"barB.png"}, а также оба имеют соответствующие backgrounds, Я хотел бы сгруппировать их как одну комбинацию со счетом 2.
Поскольку запись 2 не соответствует ни одной записи по этому критерию, то это еще одна комбинация со счетом 1. Запись 4 также считается другой комбинацией со счетом 1. Таким образом, результат, который я получу, будет следующим:
ids | count
--------------
1,3 | 2
2 | 1
4 | 1
или же
combinations | count
---------------------------------------------------------------------------------------------------------------------------------------------------
{"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":"bacakground.png"} | 1
{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 3, "src":"barB.png", "pos": "top"}], "background":"backgroundA.png"} | 1
Что легче достичь.
В моих фактических данных у меня есть значения, отличные от album а также src в массиве JSON в пределах objects узел. Вы заметите, что я включил pos чтобы показать это дело. Я забочусь только об использовании album, src, а также background значения, соответствующие комбинациям. Я надеялся игнорировать любые другие ценности.
Заметка
Когда я тестировал решение Эрвина, я продолжал получать эту ошибку, и я знаю, почему:
ERROR: cannot call json_populate_recordset on a nested object
Мои значения json на самом деле немного сложнее. Например:
{"objects":[{"album": 1, "src":"fooA.png", "pos": "top", filters: []}, {"album": 2, "src":"barB.png", "pos": "top", filters: []}
Очевидно, что filters является вложенным объектом и не поддерживается json_populate_recordset, Тем не менее, я думаю, что могу обойти это, если нет простой альтернативы. Опять я предполагаю, что это возможно?
ОБНОВИТЬ
Из-за опечатки в моих данных выше (которая была моей ошибкой), это решение немного неполное. Когда опечатка исправлена, решение не работает. Найдите ответ на эту ситуацию здесь. Но решение Эрвина по- прежнему является ответом на случаи, аналогичные описанным выше.
1 ответ
Учитывая эту таблицу (которую вы должны были предоставить в такой форме):
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":"bacakground.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"}')
;
JSON-записи хорошо известного переводимого типа
использование json_populate_recordset() для отмены набора записей "objects", Функция требует зарегистрированного типа строки для определения имен и типов данных результирующих столбцов. В целях этой демонстрации или, как правило, для специальных запросов, временная таблица, смоделированная после "objects" обеспечивает то же самое:
CREATE TEMP TABLE obj(album int, src text, pos text);
Найти the top 3 most common combinations... of entries that have the same album, src, and background:
SELECT array_agg(r.rep_id) AS ids, count(*) AS ct
FROM reports r
, json_populate_recordset(null::obj, r.data->'objects') o
GROUP BY r.data->>'background'
, o.album
, o.scr
ORDER BY count(*) DESC
LIMIT 3;
Каждый объект имеет значение независимо от того, находится ли он в одной строке или нет. Вы не определили, как именно с этим справиться. Как следствие, rep_id может появиться несколько раз в массиве ids, добавлять DISTINCT в array_agg() сложить возможные дубликаты. Счет ct может быть больше длины массива ids в этом случае.
Требуется Postgres 9.3 для функций и операторов JSON и неявного JOIN LATERAL,
JSON-записи неизвестного или непереводимого типа
json_array_elements() просто выводит массив json без преобразования результата в строку SQL. Получите доступ к отдельным полям с помощью операторов JSON соответственно.
SELECT array_agg(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->>'scr'
ORDER BY count(*) DESC
LIMIT 3;