Comment puis-je rechercher efficacement des éléments dans les tableaux JSON dans PostgreSQL ?

Conseils pour une recherche efficace des éléments du tableau JSON dans PostgreSQL

Afin d'optimiser la recherche d'éléments spécifiques dans un tableau JSON, veillez à prendre en compte la version de PostgreSQL et la structure des données.

PostgreSQL 9.4 et JSONB

Dans PostgreSQL 9.4 et versions ultérieures, le type de données binaire JSON jsonb offre des capacités d'indexation considérablement améliorées. Vous pouvez créer un index GIN directement sur le tableau jsonb :

<code class="language-sql">CREATE TABLE tracks (id serial, artists jsonb);  -- !
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);</code>

Cet index permet des requêtes efficaces utilisant l'opérateur @> :

<code class="language-sql">SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';</code>

Alternativement, vous pouvez utiliser la classe d'opérateur jsonb_path_ops GIN spécialisée :

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (artists jsonb_path_ops);  -- !</code>

L'instruction de requête reste inchangée.

PostgreSQL 9.3 et JSON

Pour PostgreSQL 9.3, pensez à utiliser une fonction IMMUTABLE pour extraire les éléments d'un tableau JSON dans un tableau de valeurs texte :

<code class="language-sql">CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';</code>

Ensuite, créez un index GIN fonctionnel sur le tableau extrait :

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (json2arr(artists, 'name'));</code>

Dans les requêtes, utilisez des opérateurs de tableau pour faire correspondre les expressions d'index :

<code class="language-sql">SELECT * FROM tracks
WHERE  '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));</code>

Veuillez noter que l'indexation des fonctions ne peut être utilisée qu'avec les fonctions IMMUTABLE. Validez la mutabilité de la fonction json_array_elements() et de toute autre fonction JSON utilisée.

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!