Plan des sièges en SQL avec json_agg

Leçon 12.3 · Temps de lecture : ~5 min · Exercices pratiques ↓

Cette leçon détaille la solution de l'exercice SQL Plan des sièges d'un avion sur la base de données Bookings (PostgreSQL). Vous verrez comment découper un numéro de siège comme 12A en rangée et en lettre avec des fonctions de chaînes, comment regrouper les sièges par rangée et par classe, et comment rassembler les lettres de chaque rangée dans un tableau JSON trié avec json_agg.

L'exercice : construire le plan des sièges du Boeing 777-300. Chaque ligne du résultat décrit une rangée de sièges de la cabine : le numéro de rangée (row), les lettres des sièges de cette rangée sous forme de tableau JSON trié par ordre alphabétique (seats) et la classe (fare_conditions). Par exemple, la première rangée de la classe affaires est 1, ["A", "C", "D", "G", "H", "K"], Business.

Essayez d'abord de résoudre l'exercice vous-même ; la requête complète est cachée à la fin de la leçon.

Où sont les données

• seats stocke tous les sièges de chaque modèle d'avion : aircraft_code, seat_no (comme 12A) et fare_conditions.
• aircrafts_data stocke les modèles ; le nom se trouve dans la colonne JSON model, en plusieurs langues : { "en": "Boeing 777-300", "ru": "Боинг 777-300" }. L'opérateur ->> extrait une valeur sous forme de texte : model->>'en'.

Il n'y a pas de colonne séparée pour la rangée : la rangée et la lettre sont toutes deux dans seat_no.

Solution pas à pas

1. Découper le numéro de siège

SELECT seat_no, rtrim(seat_no, 'ABCDEFGHKJ') AS row_text, right(seat_no, 1) AS letter
FROM seats
WHERE aircraft_code = '773' AND seat_no IN ('1A', '11D');

Résultat : 1A donne la rangée 1 et la lettre A ; 11D donne la rangée 11 et la lettre D.

• rtrim(seat_no, 'ABCDEFGHKJ') supprime n'importe lequel des caractères listés à la fin de la chaîne et laisse les chiffres. La liste contient toutes les lettres utilisées dans la base ; la lettre I n'est pas utilisée dans les plans de cabine, car elle ressemble à 1.
• right(seat_no, 1) prend le dernier caractère, la lettre du siège.

Une alternative plus générale est l'expression régulière : (regexp_match(seat_no, '^(\d+)([A-Z])$'))[1] renvoie les chiffres quelles que soient les lettres.

2. Regrouper par rangée et par classe

Une ligne du résultat correspond à une rangée de cabine d'une classe : regroupez donc par le numéro de rangée extrait et par fare_conditions. Regrouper par une expression fonctionne comme regrouper par une colonne ; la même expression est ensuite utilisée dans SELECT.

3. Rassembler les lettres dans un tableau JSON

json_agg(value) est une fonction d'agrégation : comme COUNT ou SUM, elle traite toutes les lignes d'un groupe, mais les renvoie sous forme d'un seul tableau JSON. L'ordre des éléments se définit avec ORDER BY à l'intérieur de l'appel de fonction :

json_agg(right(seat_no, 1) ORDER BY right(seat_no, 1))

Sans ORDER BY à l'intérieur, les lettres arrivent dans l'ordre de lecture des lignes, par exemple ["C", "A", "D"].

4. Trier les rangées comme des nombres

rtrim() renvoie du texte, et le texte se trie caractère par caractère : 1, 11, 12, … 19, 2, 20. Pour trier les rangées comme des nombres, convertissez la rangée en entier : rtrim(seat_no, 'ABCDEFGHKJ')::int.

Résultat : 46 lignes. La classe affaires occupe les rangées 1 à 5 (6 sièges chacune), Comfort les rangées 11 à 16, l'économique les rangées 17 à 51. Les rangées 6 à 10 n'existent pas, et la dernière rangée, la 51, n'a que 4 sièges : ["D", "E", "F", "G"].

Fonctions similaires dans d'autres bases

select
    rtrim(seat_no, 'ABCDEFGHKJ')::int "row",
    json_agg(right(seat_no, 1) order by right(seat_no, 1)) seats,
    fare_conditions
from seats
join aircrafts_data using (aircraft_code)
where aircrafts_data.model->>'en' = 'Boeing 777-300'
group by rtrim(seat_no, 'ABCDEFGHKJ'), fare_conditions
order by "row";
    

Points clés :

• Quand deux valeurs sont stockées dans une même chaîne, séparez-les avec des fonctions de chaînes : rtrim() pour les chiffres, right() pour la dernière lettre, ou regexp_match() pour n'importe quel format.
• On peut faire un GROUP BY sur une expression, comme le numéro de rangée extrait.
• json_agg() rassemble les valeurs d'un groupe dans un tableau JSON ; ORDER BY à l'intérieur de l'appel fixe leur ordre.
• Les nombres extraits d'un texte doivent être convertis en int pour être triés correctement.
• row est un mot-clé dans PostgreSQL ; comme nom de colonne, il s'écrit entre guillemets doubles : "row".

Questions fréquentes

Comment agréger des valeurs dans un tableau en SQL ?

Dans PostgreSQL, utilisez array_agg() pour un tableau ou json_agg() pour un tableau JSON, avec ORDER BY à l'intérieur de l'appel pour trier les éléments. Dans MySQL, utilisez JSON_ARRAYAGG() ou GROUP_CONCAT() ; dans SQL Server, STRING_AGG().

Comment séparer une chaîne en partie numérique et partie alphabétique en SQL ?

Si les lettres sont à la fin, rtrim(value, 'ABC…') renvoie la partie numérique et right(value, 1) la dernière lettre. Pour n'importe quel format, utilisez une expression régulière, comme regexp_match(value, '^(\d+)([A-Z])$') dans PostgreSQL ou REGEXP_SUBSTR() dans MySQL.

Pourquoi mes nombres sont-ils triés 1, 10, 11, 2 ?

Parce qu'ils sont stockés ou calculés comme du texte, et le texte se compare caractère par caractère. Convertissez-les en nombre avant de trier : ORDER BY value::int dans PostgreSQL ou ORDER BY CAST(value AS UNSIGNED) dans MySQL.