Leçon 3.6 · Temps de lecture : ~8 min

Les fonctions JSON permettent à une requête de regarder à l'intérieur d'un document JSON stocké dans une colonne : lire une valeur par son chemin, filtrer les lignes avec, compter les éléments d'un tableau ou transformer des lignes en JSON pour une application. Dans cette leçon, vous lirez des valeurs avec JSON_VALUE, JSON_EXTRACT et l'opérateur ->>, vous filtrerez avec, vous travaillerez avec des tableaux et vous construirez du JSON avec JSON_OBJECT et JSON_ARRAYAGG. Les exemples s'exécutent sur plusieurs bases du site, car chaque base nomme ces fonctions un peu différemment.

Fonctions JSON en SQL

Du JSON dans une colonne

Parfois, une ligne a des attributs qui varient d'une ligne à l'autre, et la table les stocke dans un seul document JSON. La table machine de la base SQLite garde ainsi ses détails :

MySQL et MariaDB ont un type JSON, PostgreSQL a json et jsonb, SQLite et SQL Server stockent le JSON sous forme de texte. Dans tous ces systèmes, les fonctions JSON traitent le document comme des données structurées et non comme une simple chaîne.

Lire une valeur par un chemin

Une valeur dans le document est désignée par un chemin : $ est le document entier, $.acquired la clé acquired, $[0] le premier élément d'un tableau, $[0].source la clé source de cet élément.

SQLite :

SELECT name, json_extract(details, '$.acquired') AS acquired
FROM machine;

Résultat : WY401 — 2023-05-01, Inphormex — 2021-07-15, AutoPlate 9000 — NULL. Une clé absente donne NULL, pas une erreur.

SQLite et MySQL ont aussi une forme courte, l'opérateur ->> : details ->> '$.acquired' renvoie les mêmes valeurs.

Dans MySQL et MariaDB, il existe deux fonctions, et la différence compte :

SELECT JSON_EXTRACT('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS extracted,
       JSON_VALUE('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS value;

Résultat : extracted vaut "ACADEMY DINOSAUR" avec les guillemets, c'est encore un morceau de JSON ; value vaut simplement ACADEMY DINOSAUR.

JSON_EXTRACT renvoie du JSON : pour le comparer à une chaîne, on l'enveloppe dans JSON_UNQUOTE. JSON_VALUE renvoie directement une valeur ordinaire ; SQL Server utilise la même fonction.

Filtrer sur une valeur du JSON

Une fonction JSON s'utilise dans WHERE comme n'importe quelle autre expression. Comme une clé absente donne NULL, IS NULL et IS NOT NULL trouvent les documents sans la clé ou avec elle.

SQLite :

SELECT name, json_extract(details, '$.refurbished') AS refurbished
FROM machine
WHERE json_extract(details, '$.refurbished') IS NOT NULL;

Résultat : une machine, Inphormex, remise à neuf le 2023-10-22.

Les tableaux JSON

Dans la base University (MariaDB), research_projects.funding contient un tableau de financements : [{ "source": "Private Foundation", "amount": 244542.96, ...}], ou [] pour un projet sans financement. JSON_LENGTH compte les éléments, et un chemin avec un indice en lit un :

SELECT project_id, JSON_LENGTH(funding) AS grants,
       JSON_UNQUOTE(JSON_EXTRACT(funding, '$[0].source')) AS first_source
FROM research_projects
ORDER BY project_id
LIMIT 3;

Résultat : le projet 1 a 1 financement de Private Foundation ; les projets 2 et 3 en ont 0, et leur première source vaut NULL.

Pour vérifier si un élément quelconque d'un tableau contient une valeur, MySQL et MariaDB proposent JSON_SEARCH, qui renvoie le chemin de la valeur trouvée ou NULL :

SELECT COUNT(*) AS nasa_projects
FROM research_projects
WHERE JSON_SEARCH(funding, 'one', 'NASA', NULL, '$[*].source') IS NOT NULL;

Résultat : 30 projets ont un financement de la NASA. `$[].source` signifie « le source de chaque élément ».*

PostgreSQL : les opérateurs -> et ->>

PostgreSQL lit le JSON avec des opérateurs plutôt qu'avec des chemins : -> renvoie une valeur JSON, ->> renvoie du texte. Dans la base Bookings, les noms des aéroports sont stockés en plusieurs langues sous forme de jsonb, par exemple { "en": "Yakutsk Airport", "ru": "Якутск"} :

SELECT airport_code, airport_name ->> 'en' AS name, city ->> 'en' AS city
FROM airports_data
ORDER BY airport_code
LIMIT 3;

Résultat : AAQ — Anapa Vityazevo Airport, Anapa ; ABA — Abakan Airport, Abakan ; AER — Sochi International Airport, Sochi.

Construire du JSON à partir de lignes

La tâche inverse consiste à renvoyer un résultat en JSON, par exemple pour une application web. JSON_OBJECT construit un objet à partir de paires clé-valeur.

MySQL, Sakila :

SELECT JSON_OBJECT('id', film_id, 'title', title, 'rating', rating) AS film
FROM film
ORDER BY film_id
LIMIT 2;

Résultat : { "id": 1, "title": "ACADEMY DINOSAUR", "rating": "PG"} et { "id": 2, "title": "ACE GOLDFINGER", "rating": "G"}.

JSON_ARRAYAGG est une fonction d'agrégation : elle rassemble les valeurs de nombreuses lignes dans un seul tableau.

SELECT JSON_ARRAYAGG(name) AS categories
FROM category;

Résultat : une ligne avec un tableau des 16 catégories : ["Action", "Animation", "Children", ...].

Dans PostgreSQL, on fait la même chose avec json_build_object et json_agg :

SELECT json_build_object('code', airport_code, 'city', city ->> 'en') AS airport
FROM airports_data
ORDER BY airport_code
LIMIT 2;

Résultat : { "code" : "AAQ", "city" : "Anapa"} et { "code" : "ABA", "city" : "Abakan"}.

Les fonctions JSON selon la base de données

Quand stocker du JSON

Le JSON est pratique pour des attributs qui varient d'une ligne à l'autre ou qui arrivent d'un système externe sous forme de document. Mais une valeur dans du JSON est plus difficile à contrôler, à joindre et à indexer qu'une colonne. Si la même clé est présente dans chaque ligne et sert dans les filtres, elle mérite sa propre colonne.

Points clés :

• Un chemin désigne une valeur dans le JSON : $.key, $[0], $[0].key, $[*].key.
• JSON_VALUE (MySQL, MariaDB, SQL Server), json_extract et ->> (SQLite, MySQL) et ->> (PostgreSQL) lisent une valeur comme une valeur ordinaire.
• Dans MySQL et MariaDB, JSON_EXTRACT renvoie du JSON : une chaîne revient entre guillemets et demande JSON_UNQUOTE.
• Une clé absente donne NULL : IS NULL et IS NOT NULL filtrent donc les documents selon la présence d'une clé.
• JSON_OBJECT construit un objet à partir de colonnes, JSON_ARRAYAGG rassemble des lignes dans un tableau ; PostgreSQL a json_build_object et json_agg.

Questions fréquentes

Quelle est la différence entre JSON_EXTRACT et JSON_VALUE ?

JSON_EXTRACT renvoie un morceau de JSON : une chaîne revient avec des guillemets, un objet ou un tableau sous forme de texte JSON. JSON_VALUE renvoie une seule valeur scalaire sous forme de texte ordinaire, prête à être comparée ou affichée.

Que renvoie une fonction JSON quand la clé n'existe pas ?

NULL. C'est pourquoi WHERE json_extract(details, '$.refurbished') IS NULL trouve les documents sans cette clé.

Quelle est la différence entre -> et ->> dans PostgreSQL ?

-> renvoie une valeur JSON, ->> renvoie du texte. On utilise -> pour descendre dans un document et ->> pour obtenir la valeur finale.

Questions d'entretien

Quand stockeriez-vous des données dans une colonne JSON plutôt que dans des colonnes séparées ?

Quand les attributs varient d'une ligne à l'autre, servent rarement de filtre ou arrivent comme un document tout prêt d'un autre système. Les valeurs présentes dans chaque ligne et utilisées dans des filtres, des jointures ou des contraintes doivent être des colonnes ordinaires.

Comment trouver les lignes dont un tableau JSON contient une valeur ?

Dans MySQL et MariaDB avec JSON_SEARCH(doc, 'one', 'value', NULL, '$[*].key') IS NOT NULL ou JSON_CONTAINS ; dans PostgreSQL avec l'opérateur d'inclusion @> sur jsonb.

Comment renvoyer le résultat d'une requête en JSON ?

Construisez des objets avec JSON_OBJECT (json_build_object dans PostgreSQL) et rassemblez-les dans un tableau avec JSON_ARRAYAGG (json_agg) ; SQL Server ajoute FOR JSON PATH à la requête.