SQL Seat Map Query with json_agg

Lesson 12.3 · Reading time: ~5 min · Practice tasks ↓

This lesson walks through the solution of the SQL task Aircraft seat map on the Bookings database (PostgreSQL). You'll see how to split a seat number such as 12A into a row and a letter with string functions, how to group seats by row and class, and how to collect the letters of each row into a sorted JSON array with json_agg.

The task: build the seat map of the Boeing 777-300. Each result row describes one row of seats in the cabin: the row number (row), the seat letters of that row as a JSON array sorted alphabetically (seats), and the class (fare_conditions). For example, the first row of business class is 1, ["A", "C", "D", "G", "H", "K"], Business.

Try to solve the task yourself first; the full query is hidden at the end of the lesson.

Where the Data Is

• seats stores every seat of every aircraft model: aircraft_code, seat_no (such as 12A) and fare_conditions.
• aircrafts_data stores the models; the name is in the JSON column model, in several languages: { "en": "Boeing 777-300", "ru": "Боинг 777-300" }. The operator ->> extracts a value as text: model->>'en'.

There is no separate row column: the row and the letter are both inside seat_no.

Step-by-Step Solution

1. Split the Seat Number

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');

Result: 1A gives row 1 and letter A; 11D gives row 11 and letter D.

• rtrim(seat_no, 'ABCDEFGHKJ') removes any of the listed characters from the end of the string, leaving the digits. The list contains every letter used in the database; the letter I is not used in aircraft seat maps because it looks like 1.
• right(seat_no, 1) takes the last character, the seat letter.

A more general alternative is a regular expression: (regexp_match(seat_no, '^(\d+)([A-Z])$'))[1] returns the digits whatever the letters are.

2. Group by Row and Class

One result row is one cabin row of one class, so group by the extracted row number and fare_conditions. Grouping by an expression works like grouping by a column; the same expression is then used in SELECT.

3. Collect the Letters into a JSON Array

json_agg(value) is an aggregate function: like COUNT or SUM, it processes all rows of a group, but returns them as one JSON array. The order of elements inside it is set with ORDER BY inside the function call:

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

Without ORDER BY inside, the letters come in whatever order the rows were read, for example ["C", "A", "D"].

4. Sort the Rows as Numbers

rtrim() returns text, and text is sorted character by character: 1, 11, 12, … 19, 2, 20. To sort rows as numbers, cast the row to an integer: rtrim(seat_no, 'ABCDEFGHKJ')::int.

Result: 46 rows. Business class takes rows 1–5 (6 seats each), Comfort rows 11–16, Economy rows 17–51. There are no rows 6–10, and the last row 51 has only 4 seats: ["D", "E", "F", "G"].

Similar Functions in Other Databases

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";
    

Key Takeaways:

• When two values are stored in one string, split them with string functions: rtrim() for the digits, right() for the last letter, or regexp_match() for any format.
• You can GROUP BY an expression, such as the extracted row number.
• json_agg() collects the values of a group into a JSON array; ORDER BY inside the call sets their order.
• Numbers extracted from text must be cast to int to sort correctly.
• row is a keyword in PostgreSQL, so as a column name it is written in double quotes: "row".

Frequently Asked Questions

How do I aggregate values into an array in SQL?

In PostgreSQL, use array_agg() for an array or json_agg() for a JSON array, with ORDER BY inside the call to sort the elements. In MySQL, use JSON_ARRAYAGG() or GROUP_CONCAT(); in SQL Server, STRING_AGG().

How do I split a string into its number and letter parts in SQL?

If the letters are at the end, rtrim(value, 'ABC…') returns the number part and right(value, 1) the last letter. For any format, use a regular expression, such as regexp_match(value, '^(\d+)([A-Z])$') in PostgreSQL or REGEXP_SUBSTR() in MySQL.

Why are my numbers sorted as 1, 10, 11, 2?

Because they are stored or calculated as text, and text is compared character by character. Cast them to a number before sorting: ORDER BY value::int in PostgreSQL or ORDER BY CAST(value AS UNSIGNED) in MySQL.