Comment puis-je interroger des éléments spécifiques dans les tableaux JSON dans PostgreSQL ?

Interrogation des éléments du tableau JSON dans PostgreSQL

PostgreSQL 9.3 et versions ultérieures prennent en charge le type de données JSON, permettant aux données JSON d'être stockées et manipulées dans la base de données. Cette fonctionnalité permet de stocker des structures de données complexes (telles que des tableaux d'objets) dans une seule colonne. Cependant, rechercher des éléments ou des valeurs spécifiques au sein de ces structures complexes peut s'avérer difficile.

Supposons que la colonne nommée « data » dans la table « reports » stocke les données JSON suivantes :

<code class="language-json">{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}</code>

Une tâche courante consiste à récupérer tous les rapports contenant une valeur spécifique du champ "src" du tableau "objects". Interroger un champ JSON plat comme « background » est simple, mais extraire des éléments dans un tableau nécessite une approche différente.

La requête suivante récupère avec succès tous les rapports correspondant à la valeur « arrière-plan » donnée :

<code class="language-sql">SELECT data AS data FROM reports WHERE data->>'background' = 'background.png'</code>

Cependant, essayez d'interroger les valeurs dans le tableau "objets" en utilisant une syntaxe similaire (par exemple) :

<code class="language-sql">SELECT data AS data FROM reports WHERE data->>'objects' = 'foo.png'</code>

ne produira pas les résultats attendus car les "objets" sont des tableaux, pas de simples champs.

Pour réussir à interroger des éléments dans un tableau JSON, vous pouvez utiliser la fonction json_array_elements() dans une jointure horizontale. Cette fonction extrait un seul élément d'un tableau, vous permettant de tester chaque élément individuellement.

La requête suivante utilise une jointure latérale pour développer le tableau "objects" et tester la présence d'une valeur "src" spécifique :

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

Alternativement, pour un seul niveau d'imbrication, vous pouvez utiliser une version simplifiée de la requête :

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

Ces requêtes utilisent les opérateurs #>, -> et ->> pour parcourir la structure JSON. Consultez la documentation PostgreSQL pour plus de détails sur ces opérateurs.

De plus, pour optimiser les performances des requêtes JSON complexes impliquant des tableaux, pensez à ajouter un index GIN sur l'expression JSON appropriée. Cela peut améliorer considérablement la vitesse de recherche, en particulier dans le cas d’ensembles de données volumineux.

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!