Eh oui, stocker des données au format JSON dans PostgreSQL c’est possible. À l’instar des base de données NoSQL comme MongoDB, PostgresSQL peut stocker des documents au format JSON. 

Mais pourquoi ? 

Eh bien, d’après-moi c’est parce que cela offre le meilleur des 2 mondes. Il arrive souvent que dans les applications modernes, le recours au stockage de document (clé / valeur) soit très pratique. Pour enregistrer des logs, des informations rapidement accessibles, de l’information brut et non relative … Les applications modernes ne requiert pas en tout temps des données relative.

Une autre raison qui me parait intéressante est que des fois, la fainéantise (ou le manque de temps) du développeur va faire que certaine données ne seront pas collectées et enregistrées en base données. Si la modélisation demande d’ajouter 7 tables et que le schéma de la base de données devient une usine à gaz, le développeur est humain, oui, et des fois ça ne le tente pas, il préfère aller se boire une bière. C’est alors que le type de données JSON intervient pour permettre de « jeter » un groupe de donnée au format JSON dans un champs. 

Il suffit de créer un champs de type JSON ou JSONB (nous verrons la différence plus tard) et vous avez accès au stockage de donnée hiérarchique que permet JSON. Mais ce n’est pas tout, sinon vous auriez pu mettre vos données au format JSON dans un champ de type « TEXT ». L’important avec le type de donnée JSON est que vous allez pouvoir faire des requêtes SQL directement dans le JSON. PostgreSQL propose des fonctions et des opérateurs spécifiques au format JSON pour faire vos requêtes, et cerise sur le gâteau, c’est très efficace et rapide.

JSON ou JSONB 

Lorsque vous souhaitez enregistrer des données au format JSON dans PostgreSQL, vous avez le choix en JSON et JSONB (JSON Binary). 

Les deux types de données JSON et JSONB dans Postgres sont utilisés pour stocker des données au format JSON, mais ils ont des comportements différents en ce qui concerne l’indexation et la vérification de la validité des données.

JSON est un type de données non indexable, ce qui signifie qu’il n’est pas possible d’utiliser des index pour accélérer les requêtes qui filtrent ou trient des données JSON. Il est également moins efficace pour stocker des données qui contiennent des erreurs de syntaxe ou des valeurs non valides.

JSONB est un type de données indexable, il permet d’utiliser des index pour accélérer les requêtes qui filtrent ou trient des données. Il est également plus efficace pour stocker des données qui contiennent des erreurs de syntaxe ou des valeurs non valides car il vérifie la validité des données au moment de l’insertion.

Requêtes SQL sur un champs JSON dans PostgreSQL

Pour cet article, nous allons nous concentrer sur les opérateurs, mais sachez qu’ils existent aussi plein de fonctions dont vous pouvez trouver la liste dans la documentation de PostgreSQL. Les opérateurs vous permettent de faire des requêtes SQL simple dans un champs JSON ou JSONB.

La flèche courte -> garde le type JSON

La flèche longue ->> retourne du texte.

Voici le code SQL pour reproduire ce petit tutoriel vous même : 

CREATE TABLE transaction (
id serial NOT NULL PRIMARY KEY,
message json NOT NULL
);

INSERT INTO transaction (message)
VALUES('{ "order_id": "7856", "items": {"product": "Beer","qty": 24}}'),
('{ "order_id": "9801", "items": {"product": "Pizza","qty": 1}}'),
('{ "order_id": "10089", "items": {"product": "Wine","qty": 2}}');

Voici un exemple de requête pour récupérer tous les order_id du champs « message » de la table « transaction ». Attention ça va aller vite :

SELECT message->'order_id' from transaction;

Plutôt simple, non ? ça nous retourne 3 ligne : « 7856 », « 9801 » et « 10089 »

Maintenant si je souhaite obtenir les items dont le order_id est 9801, voici la requête :

SELECT message->'items' as items from orders WHERE message ->> 'order_id' = '9801';

Aller plus loin sur JSON et JSONB dans Postgres

Voici une vidéo pour étendre le sujet du JSON & JSONB pour Postgres :

Conclusion

Vous voyez la puissance du couple JSON et SQL ? C’est super utile de pouvoir stocker des données non organisées dans un JSON et de pouvoir faire une requête SQL dans le JSON directement. C’est un gain de temps énorme et ça peut aussi améliorer votre application puisque le SQL va pouvoir pré-mâcher les données.

Si vous êtes intéressé à en savoir plus sur les champs de type JSON dans Postgres et comment travailler avec ceux-ci, à voir plus d’exemples … Laissez un commentaire et je pourrais faire un autre article plus en profondeur.