Comment interroger des tableaux JSON imbriqués dans PostgreSQL ?

Requête de tableaux JSON imbriqués dans PostgreSQL

Introduction :

Le type de données JSON de PostgreSQL fournit des fonctions complètes de stockage de données et de requête. Un défi courant consiste à interroger les tableaux stockés dans les objets JSON. Cet article fournit un guide étape par étape pour interroger les éléments de tableau dans les types JSON, en se concentrant sur PostgreSQL 9.3 et versions ultérieures.

Interrogation de tableaux JSON dans PostgreSQL 9.3 et supérieur :

1. Fonction json_array_elements() :

   json_array_elements() La fonction renvoie une liste horizontale de tous les éléments du tableau JSON. Il peut être utilisé avec des jointures latérales dans la clause FROM :

   <code class="language-sql">SELECT data::text, obj
   FROM   reports r, json_array_elements(r.data#>'{objects}') obj
   WHERE  obj->>'src' = 'foo.png';</code>

   Veuillez noter que l'opérateur #> est utilisé pour accéder aux objets imbriqués dans un tableau JSON.

2. Jointures horizontales imbriquées :

   Une autre approche consiste à utiliser des jointures latérales imbriquées :

   <code class="language-sql">SELECT *
   FROM   reports r, json_array_elements(r.data->'objects') obj
   WHERE  obj->>'src' = 'foo.png';</code>

   Cette requête utilise JOIN LATERAL implicite et fonctionne de la même manière que l'exemple précédent.

Optimiser les requêtes à l'aide de l'index GIN (PostgreSQL 9.4) :

Pour améliorer les performances des requêtes lors de la recherche d'éléments de tableau spécifiques, il est recommandé d'utiliser jsonb_array_elements() et de créer un index GIN sur l'expression JSON data->'objects' :

<code class="language-sql">CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';</code>

L'opérateur

@> vérifie l'inclusion et exige que la structure JSON soit placée entre crochets.

Notes supplémentaires :

Les opérateurs
• ->, ->> et #> sont utilisés pour accéder aux objets et tableaux JSON imbriqués.
• Pour une compréhension complète des opérateurs JSON, consultez la documentation PostgreSQL.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!