Accéder au contenu principal

Indexation GIN de PostgreSQL

Les fonctions JSON de PostgreSQL vous permettent de stocker, d'interroger et de manipuler des données JSON dans votre base de données. Ils sont essentiels pour traiter les types de données JSON et effectuer des opérations telles que l'extraction, l'agrégation et la transformation.

Utilisation

Les fonctions JSON sont utilisées pour traiter les données JSON stockées dans PostgreSQL. Elles permettent d'interroger et de manipuler efficacement ce type de données semi-structurées. Ils facilitent les opérations telles que la récupération d'objets JSON, l'accès à des valeurs spécifiques et les modifications.

Syntax


json -> key
jsonb ->> key
jsonb_set(target jsonb, path text[], new_value jsonb)

Dans ces exemples, `->` est utilisé pour extraire des objets JSON, `->>` récupère des valeurs JSON sous forme de texte, et `jsonb_set` met à jour des données JSON à un chemin spécifié.

Différence entre `json` et `jsonb` :

  • json: Enregistre les données JSON sous forme de texte, en préservant les espaces et l'ordre des clés.
  • jsonb: Stocke les données JSON dans un format binaire, offrant de meilleures performances et capacités d'indexation. C'est pourquoi `jsonb` est généralement préféré pour la plupart des cas d'utilisation.

Exemples

1. Extraction d'un objet JSON


SELECT data -> 'name' AS name
FROM json_table;

Cette requête extrait l'objet JSON associé à la clé `name` de la colonne `data` de la table `json_table`.

2. Accéder à une valeur JSON sous forme de texte


SELECT data ->> 'age' AS age
FROM json_table;

Ici, la valeur JSON de `age` est récupérée sous forme de texte, ce qui est idéal pour les cas où vous avez besoin des données au format texte pour un traitement ultérieur.

3. Mise à jour des données JSON


UPDATE json_table
SET data = jsonb_set(data, '{address, city}', '"New York"')
WHERE id = 1;

Ceci met à jour le champ `city` dans l'objet JSON `address` à "New York" pour une ligne spécifique où `id` est 1.

4. Manipuler les tableaux JSON


SELECT jsonb_array_elements(data -> 'tags') AS tag
FROM json_table;

Cette requête montre comment gérer les tableaux JSON en extrayant chaque élément du tableau `tags` à l'intérieur d'un objet JSON.

5. Opérations JSON


SELECT jsonb_each(data)
FROM json_table;

Cet exemple utilise `jsonb_each` pour développer l'objet JSON en un ensemble de paires clé-valeur, utile pour itérer sur les données.

Conseils et bonnes pratiques

  • Utilisez `jsonb` plutôt que `json`. `jsonb` est généralement préféré pour de meilleures performances et capacités d'indexation.
  • Indexer les données JSON. Utilisez l'indexation GIN pour une interrogation efficace des données JSONB afin d'améliorer les performances.
    • 
CREATE INDEX jsonb_idx ON json_table USING GIN (data);
  • Limiter la profondeur du JSON. Gardez les structures JSON peu profondes afin d'éviter les requêtes complexes et d'améliorer la lisibilité.
  • Exploitez les fonctions de PostgreSQL. Utilisez les fonctions intégrées comme `jsonb_array_elements` pour manipuler efficacement les tableaux JSON.
  • Validez la structure JSON. Veillez à ce que les données JSON respectent les schémas prévus afin d'éviter les erreurs lors de l'interrogation ou de la manipulation.
  • Gérez les clés manquantes avec élégance. Utilisez des fonctions comme `coalesce` pour gérer les clés manquantes et fournir des valeurs par défaut.