Comment interroger efficacement les éléments d'un tableau dans les données JSON dans PostgreSQL ?

Récupération d'éléments de tableau à partir de données JSON dans PostgreSQL

Le type de données JSON de PostgreSQL (introduit dans la version 9.3) simplifie le stockage et la gestion de structures de données complexes. Cependant, interroger efficacement des éléments spécifiques dans des tableaux JSON peut s'avérer délicat. Ce guide démontre des stratégies efficaces pour récupérer des éléments de tableau, en se concentrant particulièrement sur les champs « src » au sein d'un tableau d'objets.

Approche optimisée pour PostgreSQL 9.4 et versions ultérieures (jsonb)

PostgreSQL 9.4 et versions ultérieures offrent le type de données jsonb supérieur, offrant des performances et des fonctionnalités améliorées. La méthode la plus efficace utilise l'opérateur @> avec jsonb_array_elements() :

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

Cette requête concise vérifie directement si le tableau 'objets' contient un élément correspondant aux critères spécifiés.

Solution pour PostgreSQL 9.3 et versions ultérieures (json)

Pour PostgreSQL 9.3 et versions ultérieures (en utilisant le type json), la fonction json_array_elements() est utilisée dans une jointure latérale :

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

Cette approche désemboîte le tableau, permettant ainsi l'examen des éléments individuels.

Remarques importantes :

• Ces exemples supposent un seul niveau d'imbrication dans le tableau JSON. Des tableaux plus profondément imbriqués nécessiteront des unnest appels ou des jointures latérales supplémentaires.
• Pour les ensembles de données volumineux, la création d'un index sur l'expression « objets » est cruciale pour optimiser les performances des requêtes. Cela accélère considérablement le processus de recherche.

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!