Combinaisons d'interrogation avec un tableau imbriqué d'enregistrements dans le type de données JSON
je travaille sur une application de Rails qui utilise le type de données JSON de Postgres. J'ai une colonne JSON appelée data dans une table appelée reports . Disons que j'ai plusieurs entrées comme ceci:
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"}
ce que je voudrais faire est de retourner les différentes combinaisons d'entrées qui ont le même album, src, et le fond (NOTE: dans le objects noeud, l'ordre des éléments de tableau n'importe pas). Par exemple, la requête doit faites correspondre les entrées 1,3 à un groupe, 2 à un autre, etc. Le but est de trouver les trois combinaisons les plus courantes. Je sais comment faire cela en utilisant Ruby, mais je devrais interroger un grand échantillon d'entrées, puis itérer sur chacun d'eux. Il semble plus efficace d'utiliser Postgres si il peut gérer cette tâche. Je ne suis pas assez expert en SQL pour savoir si c'est possible.
C'est le résultat que je recherche. Dans objects , les entrées 1 et 3 contiennent toutes deux {"album": 1, "src":"fooA.png"}, {"album": 2, "src":"barB.png"} ." , ainsi que les deux correspondants backgrounds . J'aimerais groupe comme une combinaison avec un nombre de 2.
étant donné que l'entrée 2 ne correspond à aucune entrée selon ce critère, il s'agit d'une autre combinaison avec un nombre de 1. La rubrique 4 est également considérée comme une autre combinaison avec un nombre de 1. Donc le résultat que je recherche serait:
ids | count
--------------
1,3 | 2
2 | 1
4 | 1
ou
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
selon ce qui est le plus facile à réaliser.
dans mes données actuelles, j'ai des valeurs autres que juste album et src dans le tableau de JSON dans le noeud objects . Vous remarquerez que j'ai inclus pos pour montrer cette affaire. Je me soucie seulement d'utiliser les valeurs album , src , et background pour correspondre aux combos. J'espérais ignorer d'autres valeurs.
Note
quand je testais Erwin's solution , j'ai continué à obtenir cette erreur et je sais pourquoi:
ERROR: cannot call json_populate_recordset on a nested object
mes valeurs json sont en fait un peu plus complexes. Par exemple:
{"objects":[{"album": 1, "src":"fooA.png", "pos": "top", filters: []}, {"album": 2, "src":"barB.png", "pos": "top", filters: []}
évidemment, filters est un objet imbriqué et n'est pas supporté par json_populate_recordset . Toutefois, je pense que je peux y remédier s'il n'y a pas d'alternative simple. Encore une fois, je suppose que c'est possible?
UPDATE
en raison d'une erreur typographique dans les données de mon échantillon ci-dessus (qui était ma faute), cette solution est un peu incomplète. Lorsque la typographie est corrigée, la solution informatique ne fonctionne pas. Trouver la réponse à cette situation ici . Mais solution D'Erwin est toujours une réponse à des cas similaires à ce qui a été décrit ci-dessus.
1 réponses
donné ce tableau (que vous auriez dû fournir sous une forme comme celle-ci):
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 registres de bien connu, traduisible type
Use json_populate_recordset() pour annuler l'enregistrement "objects" .
La fonction nécessite un type de ligne enregistrée pour définir les noms et les types de données des colonnes résultantes. Pour les besoins de cette démonstration ou généralement pour des requêtes ad hoc, un temp "de table 1519240920" calquée "objects" offre la même:
CREATE TEMP TABLE obj(album int, src text, pos text);
pour trouver 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;
chaque objet compte, qu'il vienne de la même rangée ou non. Vous n'avez pas défini comment gérer cela exactement. Par conséquent, rep_id peut apparaître plusieurs fois dans le tableau ids . Ajouter DISTINCT à array_agg() pour plier les doublons possibles. Le comte ct peut être plus grande que la longueur du tableau ids dans ce cas.
nécessite Postgres 9.3 pour les fonctions et les opérateurs JSON et le implicite JOIN LATERAL .
JSON dossiers de l'inconnu ou de l'intraduisible type
json_array_elements() passe juste inaperçu le tableau json sans transformer le résultat en une ligne SQL. Accéder aux champs individuels avec les opérateurs JSON conséquent.
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;