Arrays multidimensionales y unnest WITH ORDINALITY

PostgreSQL es uno de los pocos motores SQL que trata los arrays como tipo de primera clase, no como columnas serializadas. Permiten almacenar listas de valores homogéneos de forma compacta, con operadores específicos, indexación GIN y la capacidad de expandirlos a filas cuando hace falta unirlos con el resto del esquema.

Declaración y construcción

Un array se declara añadiendo [] al tipo base. Las dimensiones no se imponen en el esquema, son convenciones de uso:

CREATE TABLE articulos (
    id BIGSERIAL PRIMARY KEY,
    titulo TEXT NOT NULL,
    tags TEXT[] NOT NULL DEFAULT '{}',
    puntuaciones INT[] NOT NULL DEFAULT '{}',
    matriz_posiciones INT[][] DEFAULT NULL
);

INSERT INTO articulos (titulo, tags, puntuaciones, matriz_posiciones) VALUES
('PostgreSQL avanzado', ARRAY['sql', 'postgresql', 'avanzado'],
 ARRAY[5, 4, 5, 3], ARRAY[[1,2,3],[4,5,6]]);

Las dos sintaxis equivalentes son:

• Literal con llaves: '{sql,postgresql,avanzado}'.
• Constructor ARRAY[...].

Para arrays de strings con espacios usa siempre la sintaxis ARRAY[...]: ARRAY['hola mundo', 'adios'] es más seguro que '{"hola mundo", "adios"}'.

Operadores fundamentales

Los operadores específicos de array permiten consultas eficientes sin necesidad de unnest:

| Operador | Significado | Ejemplo | |----------|-------------|---------| | @> | contiene | tags @> ARRAY['sql'] | | <@ | contenido por | ARRAY['sql'] <@ tags | | && | intersección | tags && ARRAY['sql', 'java'] | | \|\| | concatenación | tags \|\| ARRAY['nuevo'] | | = | igualdad estricta | tags = ARRAY['a', 'b'] | | array_length | longitud | array_length(tags, 1) | | array_position | posición de un valor | array_position(tags, 'sql') | | array_remove | filtrado | array_remove(tags, 'obsoleto') |

-- Articulos con cualquier tag de la lista
SELECT id, titulo
FROM articulos
WHERE tags && ARRAY['sql', 'postgresql', 'mysql'];

-- Articulos con TODOS los tags requeridos
SELECT id, titulo
FROM articulos
WHERE tags @> ARRAY['sql', 'avanzado'];

-- Anyadir un tag sin duplicar
UPDATE articulos
SET tags = tags || 'nuevo'
WHERE id = 1 AND NOT (tags @> ARRAY['nuevo']);

Indexación GIN para búsquedas rápidas

Sin índice, una consulta WHERE tags @> ARRAY['x'] hace secuencial scan. Un índice GIN transforma esto en un acceso casi constante:

CREATE INDEX idx_articulos_tags ON articulos USING GIN (tags);

EXPLAIN ANALYZE
SELECT * FROM articulos WHERE tags @> ARRAY['postgresql'];

El índice GIN es ideal para arrays, JSONB y tsvector. Tarda más en construirse que un B-tree, pero acelera enormemente las consultas de contención.

unnest: del array a las filas

La función unnest expande un array a un conjunto de filas, lo que permite operaciones SQL convencionales sobre los elementos:

SELECT id, titulo, tag
FROM articulos, unnest(tags) AS tag
WHERE tag LIKE 'post%';

Cada fila original genera tantas filas como elementos tenga su array. Es la forma estándar de descomponer un array para hacer agregaciones o joins.

-- Top 10 tags mas usados en toda la tabla
SELECT tag, COUNT(*) AS frecuencia
FROM articulos, unnest(tags) AS tag
GROUP BY tag
ORDER BY frecuencia DESC
LIMIT 10;

WITH ORDINALITY: posición original

Cuando expandes un array necesitas a veces saber en qué posición estaba cada elemento. La cláusula WITH ORDINALITY añade un contador 1-based:

SELECT id, titulo, valor, posicion
FROM articulos,
     unnest(puntuaciones) WITH ORDINALITY AS u(valor, posicion)
WHERE id = 1
ORDER BY posicion;
-- valor=5 posicion=1
-- valor=4 posicion=2
-- valor=5 posicion=3
-- valor=3 posicion=4

Esta posición es crítica cuando el orden tiene significado: por ejemplo, una secuencia de pasos, una lista de items numerados o coordenadas en un vector.

WITH ORDINALITY funciona con cualquier función set-returning, no solo unnest. También se aplica a generate_series, regexp_split_to_table, etc.

Caso práctico: ratings con gestión de cambios

Imagina que cada articulo recibe una secuencia de ratings y necesitas calcular la media móvil de los últimos 3:

SELECT
    id,
    titulo,
    AVG(valor) FILTER (WHERE posicion > array_length(puntuaciones, 1) - 3) AS media_ultimas_3,
    AVG(valor) AS media_total
FROM articulos,
     unnest(puntuaciones) WITH ORDINALITY AS u(valor, posicion)
GROUP BY id, titulo, puntuaciones;

La cláusula FILTER aplica un predicado solo a la agregación correspondiente, sin afectar al resto. Combinada con unnest WITH ORDINALITY permite cálculos posicionales sin window functions.

Modificación de elementos concretos

Para cambiar un elemento por su posición se usa la sintaxis con índice:

UPDATE articulos
SET puntuaciones[2] = 10
WHERE id = 1;

-- Concatenar a una posicion intermedia
UPDATE articulos
SET tags = tags[1:2] || 'inserto' || tags[3:]
WHERE id = 1;

Para eliminar un elemento concreto sin saber su posición exacta:

UPDATE articulos
SET tags = array_remove(tags, 'tag-a-borrar');

Arrays multidimensionales

PostgreSQL admite hasta seis dimensiones, aunque en la práctica se usan dos como mucho. Una matriz se declara con doble corchete y se accede con índices separados por comas:

SELECT matriz_posiciones[1][2] AS celda
FROM articulos
WHERE id = 1;
-- 2

-- Asignar una celda
UPDATE articulos
SET matriz_posiciones[1][2] = 99
WHERE id = 1;

Las matrices son útiles para representar rejillas o coordenadas, pero para datos verdaderamente vectoriales (embeddings de IA, por ejemplo) la extensión pgvector ofrece operadores específicos de distancia y mejor rendimiento.

Arrays vs tabla relacionada

La pregunta clásica: ¿uso un array o una tabla 1:N? Reglas prácticas:

• Array: el conjunto cabe en una fila, su tamaño es modesto (decenas de elementos), se lee y escribe siempre con la fila padre, no necesita integridad referencial individual.
• Tabla relacionada: los elementos pueden referenciarse desde otras tablas, se modifican individualmente con concurrencia, el conjunto puede crecer mucho, se necesitan foreign keys hacia ellos.

-- Array: tags simples, cabe en la fila
CREATE TABLE articulos (
    id BIGSERIAL PRIMARY KEY,
    titulo TEXT,
    tags TEXT[]
);

-- Tabla relacionada: comentarios, crecen sin limite y se modifican uno a uno
CREATE TABLE comentarios (
    id BIGSERIAL PRIMARY KEY,
    articulo_id BIGINT REFERENCES articulos(id),
    autor TEXT,
    contenido TEXT,
    creado_en TIMESTAMPTZ DEFAULT NOW()
);

flowchart LR
    A[Caso de uso] --> B{Tamano y volatilidad}
    B -->|Pocas decenas, leido<br/>siempre con la fila| C[Array nativo]
    B -->|Muchos, modificados<br/>individualmente| D[Tabla relacionada]
    C --> E[Indice GIN<br/>operadores @> &&]
    D --> F[Foreign key + JOIN]

Los arrays son una herramienta única de PostgreSQL que reduce código y simplifica esquemas cuando se usan en el contexto adecuado. La combinación con unnest WITH ORDINALITY los conecta con el resto del modelo relacional sin perder semántica posicional.