Type JSON sous PostgreSQL

PostgreSQL propose un nouveau type de données qui permet d'insérer du JSON dans une table.

Il s'agit d'une solution pour coupler de l'imbrication NF² avec une base relationnelle.

Rappel

La syntaxe JSON en bref

SyntaxeCréer une table avec des attributs JSON

1
CREATE TABLE t (
2
...
3
jsonatt JSON,
4
...
5
);
6
SyntaxeInsérer du JSON dans une table
1
INSERT INTO t
2
VALUES (
3
...
4
'{ "key1": "value1", "key2": "value2"}',
5
...
6
);
Exemple
1
CREATE TABLE cours (
2
titre TEXT PRIMARY KEY,
3
auteur JSON NOT NULL,
4
chapitres JSON NOT NULL,
5
nbpages INTEGER
6
);
1
INSERT INTO cours (titre, auteur, chapitres, nbpages)
2
VALUES (
3
'Bases de données',
4
'{"nom":"Crozat","prenom":"Stéphane"}',
5
'["UML","R","SQL","Normalisation","RO"]',
6
98
7
);
8
INSERT INTO cours (titre, auteur, chapitres, nbpages)
9
VALUES (
10
'Ingénierie documentaire',
11
'{"nom":"Crozat","prenom":"Stéphane"}',
12
'["XML","JSON"]',
13
45
14
);
1
          titre          |                auteur                |               chapitres                | nbpages 
2
-------------------------+--------------------------------------+----------------------------------------+---------
3
 Bases de données        | {"nom":"Crozat","prenom":"Stéphane"} | ["UML","R","SQL","Normalisation","RO"] |      98
4
 Ingénierie documentaire | {"nom":"Crozat","prenom":"Stéphane"} | ["XML","JSON"]                         |      45
Fondamental
Pour manipuler les données JSON intégrées dans une base PosgreSQL, on dispose d'opérateur complémentaires permettant de projeter les représentations JSON en relationnel.
Par exemple :
  • ->> permet de projeter une valeur JSON
  • -> permet de parcourir un arbre JSON
  • JSON_ARRAY_ELEMENTS permet de convertir un tableau de valeurs JSON en relationnel
  • JSON_TO_RECORDSET permet de convertir un tableau d'objets JSON en relationnel