Mapa de Assentos em SQL com json_agg

Q: Como agregar valores num array em SQL?

No PostgreSQL, use array_agg() para um array ou json_agg() para um array JSON, com ORDER BY dentro da chamada para ordenar os elementos. No MySQL, use JSON_ARRAYAGG() ou GROUP_CONCAT(); no SQL Server, STRING_AGG().

Q: Como separar uma string na parte numérica e na parte de letras em SQL?

Se as letras estão no fim, rtrim(value, 'ABC…') devolve a parte numérica e right(value, 1) a última letra. Para qualquer formato, use uma expressão regular, como regexp_match(value, '^(\d+)([A-Z])$') no PostgreSQL ou REGEXP_SUBSTR() no MySQL.

Lição 12.3 · Tempo de leitura: ~5 min · Tarefas para praticar ↓

Esta lição explica a solução da tarefa SQL Mapa de assentos da aeronave no banco de dados Bookings (PostgreSQL). Você vai ver como dividir um número de assento como 12A em fila e letra com funções de texto, como agrupar os assentos por fila e classe, e como juntar as letras de cada fila num array JSON ordenado com json_agg.

A tarefa: construir o mapa de assentos do Boeing 777-300. Cada linha do resultado descreve uma fila de assentos da cabine: o número da fila (row), as letras dos assentos dessa fila como um array JSON ordenado alfabeticamente (seats) e a classe (fare_conditions). Por exemplo, a primeira fila da classe executiva é 1, ["A", "C", "D", "G", "H", "K"], Business.

Tente resolver a tarefa sozinho primeiro; a consulta completa está escondida no fim da lição.

Onde Estão os Dados

seats guarda todos os assentos de cada modelo de aeronave: aircraft_code, seat_no (como 12A) e fare_conditions.
aircrafts_data guarda os modelos; o nome está na coluna JSON model, em várias línguas: { "en": "Boeing 777-300", "ru": "Боинг 777-300" }. O operador ->> extrai um valor como texto: model->>'en'.

Não há uma coluna separada para a fila: a fila e a letra estão ambas dentro de seat_no.

Solução Passo a Passo

1. Dividir o Número do Assento

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 dá a fila 1 e a letra A; 11D dá a fila 11 e a letra D.

rtrim(seat_no, 'ABCDEFGHKJ') remove qualquer um dos caracteres indicados do fim da string, deixando os dígitos. A lista contém todas as letras usadas no banco de dados; a letra I não é usada nos mapas de assentos de aviões, porque se parece com 1.
right(seat_no, 1) devolve o último caractere, a letra do assento.

Uma alternativa mais geral é uma expressão regular: (regexp_match(seat_no, '^(\d+)([A-Z])$'))[1] devolve os dígitos, sejam quais forem as letras.

2. Agrupar por Fila e Classe

Uma linha do resultado é uma fila da cabine de uma classe, por isso agrupe pelo número da fila extraído e por fare_conditions. Agrupar por uma expressão funciona como agrupar por uma coluna; a mesma expressão é depois usada no SELECT.

3. Juntar as Letras num Array JSON

json_agg(value) é uma função de agregação: tal como COUNT ou SUM, processa todas as linhas de um grupo, mas devolve-as como um único array JSON. A ordem dos elementos define-se com ORDER BY dentro da chamada da função:

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

Sem ORDER BY dentro, as letras vêm na ordem em que as linhas foram lidas, por exemplo ["C", "A", "D"].

4. Ordenar as Filas como Números

rtrim() devolve texto, e o texto é ordenado caractere a caractere: 1, 11, 12, … 19, 2, 20. Para ordenar as filas como números, converta a fila para inteiro: rtrim(seat_no, 'ABCDEFGHKJ')::int.

Resultado: 46 linhas. A classe executiva ocupa as filas 1 a 5 (6 assentos cada), a Comfort as filas 11 a 16, a económica as filas 17 a 51. Não existem as filas 6 a 10, e a última fila, a 51, tem só 4 assentos: ["D", "E", "F", "G"].

Funções Semelhantes Noutros Bancos de Dados

Banco de dados	Juntar os valores de um 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)` (ordem não garantida), `GROUP_CONCAT(x ORDER BY x)`
SQL Server	`STRING_AGG(x, ', ') WITHIN GROUP (ORDER BY x)`

Spoiler: a consulta SQL com a solução está escondida abaixo. Clique para revelar.


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

Principais conclusões:

Quando dois valores estão guardados numa só string, separe-os com funções de texto: rtrim() para os dígitos, right() para a última letra, ou regexp_match() para qualquer formato.
É possível fazer GROUP BY por uma expressão, como o número da fila extraído.
json_agg() junta os valores de um grupo num array JSON; ORDER BY dentro da chamada define a sua ordem.
Os números extraídos de texto têm de ser convertidos para int para serem ordenados corretamente.
row é uma palavra-chave no PostgreSQL, por isso, como nome de coluna, escreve-se entre aspas duplas: "row".

Perguntas Frequentes

Como agregar valores num array em SQL?

No PostgreSQL, use array_agg() para um array ou json_agg() para um array JSON, com ORDER BY dentro da chamada para ordenar os elementos. No MySQL, use JSON_ARRAYAGG() ou GROUP_CONCAT(); no SQL Server, STRING_AGG().

Como separar uma string na parte numérica e na parte de letras em SQL?

Se as letras estão no fim, rtrim(value, 'ABC…') devolve a parte numérica e right(value, 1) a última letra. Para qualquer formato, use uma expressão regular, como regexp_match(value, '^(\d+)([A-Z])$') no PostgreSQL ou REGEXP_SUBSTR() no MySQL.

Por que os meus números são ordenados como 1, 10, 11, 2?

Porque estão guardados ou calculados como texto, e o texto é comparado caractere a caractere. Converta-os para número antes de ordenar: ORDER BY value::int no PostgreSQL ou ORDER BY CAST(value AS UNSIGNED) no MySQL.