Comment indexer et interroger efficacement les tableaux JSONB dans PostgreSQL ?

Index approprié pour interroger des structures dans des tableaux dans Postgres JSONB

Pour indexer et interroger efficacement des tableaux de données structurées stockées dans un champ Postgres JSONB, considérez les étapes suivantes :

1. Accès correct au tableau :

Utilisez la syntaxe correcte pour accéder aux éléments du tableau dans un contexte JSONB. Par exemple :

e->0->>'event_slug'

2. Opérateurs personnalisés pour JSONB :

Utilisez les opérateurs jsonpath pour des comparaisons supérieures ou inférieures à sur les champs JSONB. Dans Postgres 12 ou version ultérieure, utilisez @?:

e->0->>'end_time' @> '2014-10-13'

Ou, dans les anciennes versions, utilisez jsonb_path_ops:

CREATE INDEX events_gin_idx ON locations USING GIN (events jsonb_path_ops);

3. Solution de base pour Postgres 12 :

Pour les contrôles d'égalité, utilisez le @? opérateur :

SELECT l.*
FROM locations l
WHERE l.events @? '$[*] ? (@.event_slug == "test_1")';

Pour les filtres de type OU, utilisez la syntaxe suivante :

SELECT l.*
FROM locations l
WHERE l.events @? '$[*] ? (@.event_slug == "test_1") ? (@.start_time.datetime() < "2014-10-13".datetime() || @.end_time.datetime() < "2014-10-13".datetime())';

4. Solution de base pour toute version de Postgres :

Pour les contrôles d'égalité, utilisez le @> opérateur :

SELECT * FROM locations WHERE events @> '[{"event_slug":"test_1"}]';

Pour les contrôles supérieurs ou égaux, utilisez une sous-requête :

SELECT l.*
FROM locations l
JOIN jsonb_array_elements(l.events) e ON l.events @> '[{"event_slug":"test_1"}]'
WHERE (e->>'end_time')::timestamp >= '2014-10-30 14:04:06'::timestamptz;

5. Solution avancée utilisant la vue matérialisée :

Pour des performances optimales sur les requêtes complexes, envisagez de créer une vue matérialisée qui stocke les données pertinentes sous forme normalisée :

CREATE MATERIALIZED VIEW loc_event AS
SELECT l.location_id, e.event_slug, e.end_time
FROM locations l, jsonb_populate_recordset(null::event_type, l.events) e;

6. Indexer et interroger la vue matérialisée :

CREATE INDEX loc_event_idx ON loc_event (event_slug, end_time, location_id);

SELECT *
FROM loc_event
WHERE event_slug = 'test_1'
AND end_time >= '2014-10-30 14:04:06 -0400'::timestamptz;

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!