PostgreJSON

La croissance de la base de données NoSQL a permis d’enregistrer des données avec un schéma plus libre que les SGBD relationnels classiques. Dans sa version 9.3, PostgreSQL s’adapte et ajoute à son catalogue de type de colonnes le format JSON. Voici un rapide aperçu des possibilités qu’offre cette nouvelle colonne.

Jeux de test

CREATE TABLE developpeurs (
  id serial NOT NULL,
  nom character varying(64),
  prenom character varying(64),
  contact json,
  CONSTRAINT pk_developpeurs PRIMARY KEY (id));

INSERT INTO developpeurs (nom, prenom, contact)
VALUES('Drd', 'Tyler','{"adress":"Paper Street", "city":"Los Angeles"}');
INSERT INTO developpeurs (nom, prenom, contact)
VALUES('D', 'Jeff','{"twitter":"CotcotCoder", "email":"jeffd@mail.fr"}');
INSERT INTO developpeurs (nom, prenom, contact)
VALUES('Sld', 'Lisbeth ','{"twitter":"lizbets", "email":"lizbets@mail.fr"}');
INSERT INTO developpeurs (nom, prenom, contact)
VALUES('Lgh', 'David','{"linkedin":"DavidLgh", "email":"david.lgh@mail.fr", "phone":"0666789561"}');

Notre colonne contact est au format JSON. C’est à dire un format Text vérifiant que son contenu est bien formaté dans le format éponyme. Le développeur sera donc libre d’y stocker des informations dans un schéma facilement évolutif (ajout d’un nouveau moyen de contact). Enfin ce format permettra l’utilisation des commandes JSON que nous allons voir par la suite.

SELECT information->>’deeper’

La commande suivante permettra de récupérer directement le champs email de notre contact sans avoir à retourner l’objet JSON au complet à notre applicatif :

SELECT contact->>'email' AS email FROM developpeurs

Ici nous utilisons l’opérateur ->> qui permet de retourner l’élément sous sa forme textuelle.

Naturellement ce champs peut être filtrées ou utilisée comme une colonne « relationnelle classique »:

SELECT CONCAT (nom, ' ', prenom) AS "Email OK"
FROM developpeurs
WHERE contact->>'email' NOT LIKE '';

Si nous ajoutons un peu de variété à notre jeu de test :

INSERT INTO developpeurs (nom, prenom, contact)
VALUES('IM', 'Max','{"telephones":["0345467921","0345467490"]}');

La requête suivante permettra d’accéder au tableau :

SELECT prenom, contact->'telephones'
FROM developpeurs
WHERE prenom ILIKE 'Max'

Nous avons utilisé l’opérateur -> qui nous retourne l’objet en tant que tel, donc ici un tableau.

Mais nous allons aussi pouvoir sélectionner directement le second élément du tableau (l’index commence à 0) :

SELECT prenom, contact->'telephones'->1
FROM developpeurs
WHERE prenom ILIKE 'Max'

Ou connaitre sa taille :

SELECT json_array_length(contact->'telephones')
FROM developpeurs
WHERE prenom LIKE 'Max'

Naturellement de nombreuses autres fonctions sont présentes afin de traiter plus finement le type JSON, dans les procédures stockées.

On pourra ainsi traiter chaque élément de la colonne comme une ligne indépendante :

SELECT * 
FROM json_each('{"fixe":"0384852540", "portable":"0673484312"}')

Ou alors de traiter les clés :

SELECT  * 
FROM json_object_keys('{"rue":"Place Victor Hugo", "CP":"25000", "ville":"Besancon"}')

Pour plus d’informations n’hésitez pas à consulter la documentation.

Documentation officielle