L'interrogation à l'intérieur de Postgres tableaux JSON

Comment feriez-vous à la recherche d'un élément dans un tableau stocké dans un json de la colonne? (Mise à jour: voir Aussi la mise à jour 9.4 réponse pour jsonb colonnes.)

Si j'ai un document JSON comme ça, stockée dans un json colonne nommée blob:

{"name": "Wolf",
 "ids": [185603363281305602,185603363289694211]}

ce que j'aimerais être capable de faire quelque chose comme:

SELECT * from "mytable" WHERE 185603363289694211 = ANY("blob"->'ids');

et obtenir toutes les lignes correspondantes de sortir. Mais cela ne fonctionne pas parce que "blob"->'ids' renvoie du JSON valeurs, pas Postgres tableaux.

J'aimerais aussi créer un index sur la personne de l'IDs, si c'est possible.

InformationsquelleAutor Joe Shaw | 2013-09-16
  1. 14

    Suivantes réponse originale à cette question ne s'applique que pour Postgresql 9.3. Pour un Postgresql 9.4 réponse, voir la mise à Jour ci-dessous.

    Cela s'appuie sur Erwin est référencé réponses, mais est un peu plus explicite à cette question.

    Les Id dans ce cas sont bigints, donc créer une fonction d'aide pour la conversion d'un tableau JSON pour une Postgres bigint tableau:

    CREATE OR REPLACE FUNCTION json_array_bigint(_j json)
  RETURNS bigint[] AS
$$
SELECT array_agg(elem::text::bigint)
FROM json_array_elements(_j) AS elem
$$
  LANGUAGE sql IMMUTABLE;

    Nous pourrions simplement avoir facilement (et peut-être plus re-usably) a retourné une text tableau ici à la place. Je soupçonne l'indexation sur bigint est beaucoup plus rapide que text mais je vais avoir un moment difficile de trouver des preuves en ligne pour accompagner cela.

    Pour la construction de l'index:

    CREATE INDEX "myindex" ON "mytable" 
  USING GIN (json_array_bigint("blob"->'ids'));

    De l'interrogation, de ce qui fonctionne et utilise l'index:

    SELECT * FROM "mytable" 
  WHERE '{185603363289694211}' <@ json_array_bigint("blob"->'ids');

    Faisant cela fonctionnera aussi pour les interroger, mais il n'utilise pas l'index:

    SELECT * FROM "mytable" 
  WHERE 185603363289694211 = ANY(json_array_bigint("blob"->'ids'));

    Mise à jour de 9,4

    Postgresql 9.4 introduit la jsonb type. C'est un bon AFIN de répondre à propos de jsonb et quand vous devriez l'utiliser sur json. En bref, si jamais vous êtes en interrogeant le JSON, vous devez utiliser jsonb.

    Si vous construisez votre colonne comme jsonb, vous pouvez utiliser cette requête:

    SELECT * FROM "mytable"
  WHERE blob @> '{"ids": [185603363289694211]}';

    La @> est Postgres' opérateur contient, documenté pour jsonb ici.
    Grâce à Alain répondre pour avoir porté à mon attention.

    • +1 très Beau travail. Remarque, si des éléments dans le JSON tableaux ne sont pas uniques que vous pouvez obtenir les doublons de lignes dans le résultat. Et oui, le traitement bigint est généralement plus rapide que le traitement de la text. Fait aussi pour un petits index, mais de simplifier elem::text::bigint à elem::bigint. BTW, pas besoin de guillemets autour de parfaitement identificateurs valides dans Postgres.
    • Merci pour l'aide! Malheureusement casting de json directement à bigint ne fonctionne pas. Je dois passer par text premier.
    • ANY(json_array_bigint("blob"->'ids'); --> il y a un ) manquant à la fin
    • merci, fixe.
    InformationsquelleAutor Joe Shaw
  2. 5

    D'abord, essayez de l'opérateur ->> au lieu de -> à la bande JSON couche à partir du tableau de la valeur.

    Prochain, le requête peut fonctionner comme ceci:

    Comment faire j'ai une requête à l'aide de champs à l'intérieur de la nouvelle PostgreSQL JSON type de données?

    Et indexation pourrait fonctionner comme ceci:

    Indice pour trouver un élément dans un tableau JSON

    InformationsquelleAutor Erwin Brandstetter
  3. 4

    Je sais, ça fait un moment...

    Dans postgresql-9.5, il est maintenant possible de faire des requêtes facilement.

    select '{"name": "Wolf",
         "ids": [185603363281305602,185603363289694211]}'::jsonb
       @> '{"ids":[185603363281305602]}'

    Je pense que vous devriez utiliser un jsonb champ au lieu et vous pouvez l'index par la suite.

    CREATE INDEX idx_gin_ids ON mytable USING gin ((blob -> 'ids'));
    • Cela semble également fonctionner dans Postgresql 9.4. Mais le problème, c'est que @> ne fonctionne que sur jsonb colonnes, pas json colonnes. (C'est probablement pas un problème, je ne peux pas penser à des situations dans lesquelles vous choisissez json sur jsonb, mais cette question a été initialement demandé Postgresql 9.3, qui n'a pas encore jsonb)
    • Aussi je pense qu'un meilleur exemple serait SELECT * from "mytable" WHERE blob @> '{"ids": [185603363281305602]}';
    InformationsquelleAutor Alain Gilbert