Mapa de asientos en SQL con json_agg

Q: ¿Cómo agrego valores en un array en SQL?

En PostgreSQL, usa array_agg() para un array o json_agg() para un array JSON, con ORDER BY dentro de la llamada para ordenar los elementos. En MySQL, usa JSON_ARRAYAGG() o GROUP_CONCAT(); en SQL Server, STRING_AGG().

Q: ¿Cómo separo una cadena en su parte numérica y su parte de letras en SQL?

Si las letras están al final, rtrim(value, 'ABC…') devuelve la parte numérica y right(value, 1) la última letra. Para cualquier formato, usa una expresión regular, como regexp_match(value, '^(\d+)([A-Z])$') en PostgreSQL o REGEXP_SUBSTR() en MySQL.

Lección 12.3 · Tiempo de lectura: ~5 min · Tareas de práctica ↓

Esta lección explica la solución del ejercicio SQL Mapa de asientos del avión en la base de datos Bookings (PostgreSQL). Verás cómo separar un número de asiento como 12A en fila y letra con funciones de cadenas, cómo agrupar los asientos por fila y clase, y cómo juntar las letras de cada fila en un array JSON ordenado con json_agg.

El ejercicio: construir el mapa de asientos del Boeing 777-300. Cada fila del resultado describe una fila de asientos de la cabina: el número de fila (row), las letras de los asientos de esa fila como un array JSON ordenado alfabéticamente (seats) y la clase (fare_conditions). Por ejemplo, la primera fila de la clase ejecutiva es 1, ["A", "C", "D", "G", "H", "K"], Business.

Primero intenta resolver el ejercicio por tu cuenta; la consulta completa está oculta al final de la lección.

Dónde están los datos

seats guarda todos los asientos de cada modelo de avión: aircraft_code, seat_no (como 12A) y fare_conditions.
aircrafts_data guarda los modelos; el nombre está en la columna JSON model, en varios idiomas: { "en": "Boeing 777-300", "ru": "Боинг 777-300" }. El operador ->> extrae un valor como texto: model->>'en'.

No hay una columna aparte para la fila: tanto la fila como la letra están dentro de seat_no.

Solución paso a paso

1. Separar el número de asiento

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

Resultado: 1A da la fila 1 y la letra A; 11D da la fila 11 y la letra D.

rtrim(seat_no, 'ABCDEFGHKJ') quita cualquiera de los caracteres indicados del final de la cadena y deja los dígitos. La lista contiene todas las letras que se usan en la base de datos; la letra I no se usa en los mapas de asientos de los aviones porque se parece al 1.
right(seat_no, 1) toma el último carácter, la letra del asiento.

Una alternativa más general es una expresión regular: (regexp_match(seat_no, '^(\d+)([A-Z])$'))[1] devuelve los dígitos sean cuales sean las letras.

2. Agrupar por fila y clase

Una fila del resultado es una fila de la cabina de una clase, así que agrupa por el número de fila extraído y por fare_conditions. Agrupar por una expresión funciona igual que agrupar por una columna; luego la misma expresión se usa en SELECT.

3. Juntar las letras en un array JSON

json_agg(value) es una función de agregación: igual que COUNT o SUM, procesa todas las filas de un grupo, pero las devuelve como un único array JSON. El orden de los elementos se define con ORDER BY dentro de la llamada a la función:

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

Sin ORDER BY dentro, las letras llegan en el orden en que se leyeron las filas, por ejemplo ["C", "A", "D"].

4. Ordenar las filas como números

rtrim() devuelve texto, y el texto se ordena carácter por carácter: 1, 11, 12, … 19, 2, 20. Para ordenar las filas como números, convierte la fila a entero: rtrim(seat_no, 'ABCDEFGHKJ')::int.

Resultado: 46 filas. La clase ejecutiva ocupa las filas 1 a 5 (6 asientos cada una), Comfort las filas 11 a 16 y la económica las filas 17 a 51. No existen las filas 6 a 10, y la última fila, la 51, tiene solo 4 asientos: ["D", "E", "F", "G"].

Funciones similares en otras bases de datos

Base de datos	Juntar los valores de un grupo
PostgreSQL	`json_agg(x ORDER BY x)`, `array_agg(x ORDER BY x)`, `string_agg(x, ', ' ORDER BY x)`
MySQL 8.0	`JSON_ARRAYAGG(x)` (orden no garantizado), `GROUP_CONCAT(x ORDER BY x)`
SQL Server	`STRING_AGG(x, ', ') WITHIN GROUP (ORDER BY x)`

Spoiler: la consulta SQL con la solución está oculta abajo. Haz clic para verla.


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

Conclusiones clave:

Cuando dos valores se guardan en una misma cadena, sepáralos con funciones de cadenas: rtrim() para los dígitos, right() para la última letra o regexp_match() para cualquier formato.
Se puede hacer GROUP BY por una expresión, como el número de fila extraído.
json_agg() junta los valores de un grupo en un array JSON; ORDER BY dentro de la llamada define su orden.
Los números extraídos de un texto deben convertirse a int para ordenarse correctamente.
row es una palabra clave en PostgreSQL, así que como nombre de columna se escribe entre comillas dobles: "row".

Preguntas frecuentes

¿Cómo agrego valores en un array en SQL?

En PostgreSQL, usa array_agg() para un array o json_agg() para un array JSON, con ORDER BY dentro de la llamada para ordenar los elementos. En MySQL, usa JSON_ARRAYAGG() o GROUP_CONCAT(); en SQL Server, STRING_AGG().

¿Cómo separo una cadena en su parte numérica y su parte de letras en SQL?

Si las letras están al final, rtrim(value, 'ABC…') devuelve la parte numérica y right(value, 1) la última letra. Para cualquier formato, usa una expresión regular, como regexp_match(value, '^(\d+)([A-Z])$') en PostgreSQL o REGEXP_SUBSTR() en MySQL.

¿Por qué mis números se ordenan como 1, 10, 11, 2?

Porque se guardan o se calculan como texto, y el texto se compara carácter por carácter. Conviértelos a número antes de ordenar: ORDER BY value::int en PostgreSQL o ORDER BY CAST(value AS UNSIGNED) en MySQL.