Requête et filtrage des champs JSON dans PostgreSQL

Les fonctions JSON de PostgreSQL vous permettent de stocker, d'interroger et de manipuler des données JSON dans les tableaux de votre base de données. Ils sont essentiels pour les applications qui nécessitent un schéma flexible ou qui doivent traiter des données JSON directement dans la base de données.

Comprendre JSON et JSONB

`JSON` et `JSONB` sont des types de données dans PostgreSQL utilisés pour stocker des données JSON. `JSON` stocke les données dans un format texte, tandis que `JSONB` stocke les données dans un format binaire décomposé, permettant une indexation et une interrogation efficaces.

Utilisation

Les fonctions JSON sont utilisées dans PostgreSQL pour accéder et filtrer les données JSON stockées dans des colonnes de type `JSON` ou `JSONB`. Ils permettent de récupérer et de manipuler des données complexes en fournissant des méthodes pour extraire des éléments, filtrer des tableaux et modifier des objets JSON.

SELECT json_column->'key'
FROM table_name
WHERE json_column->>'key' = 'value';

Dans cette syntaxe, `->` est utilisé pour extraire un objet JSON, tandis que `->>` extrait du texte JSON.

Exemples

1. Extraction d'un champ JSON

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

Cette requête extrait le champ `name` de la colonne JSON `data` du tableau `users`.

2. Filtrer avec JSONB

SELECT *
FROM orders
WHERE order_data->>'status' = 'shipped';

Ici, la requête filtre les tableaux `orders` dont la clé `status` de la colonne JSONB `order_data` est définie sur `shipped`.

3. Filtrage des tableaux JSONB

SELECT id, info
FROM products
WHERE info->'tags' @> '["electronics"]';

Cette requête sélectionne `id` et `info` dans le tableau `products` où le tableau `tags` dans la colonne JSONB `info` contient la valeur "electronics".

4. Mise à jour des données JSON

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

Cette requête met à jour le champ `city` dans l'objet `address` de la colonne JSONB `data` pour l'utilisateur avec `id` 1.

Conseils et bonnes pratiques

• Utilisez JSONB pour l'indexation. Préférez `JSONB` à `JSON` pour de meilleures performances d'indexation et de recherche.
• Tirez parti des indices GIN. Créez des index GIN sur les colonnes JSONB pour accélérer les requêtes de confinement.
• Extraire avec précaution. Utilisez `->` pour les objets JSON et `->>` pour l'extraction de texte afin de réduire les erreurs de conversion de type.
• Veillez à ce que les requêtes soient spécifiques. Structurez vos requêtes pour minimiser la quantité de données JSON traitées et améliorer ainsi les performances.
• Pensez à la normalisation. Pour les champs JSON fréquemment interrogés, envisagez de normaliser les données dans des colonnes ou des tableaux distincts pour plus d'efficacité.
• Comprenez les différences de performance. `JSONB` est généralement plus rapide pour les opérations de lecture en raison de son format binaire, mais il peut nécessiter plus d'espace de stockage.
• Attention aux implications en termes de stockage. `JSONB` peut avoir des besoins de stockage accrus en raison de sa représentation binaire, choisissez donc le type de données en fonction de votre cas d'utilisation spécifique.