Manejo de arrays en SQL: técnicas y trucos en motores populares

Home » Python » Manejo de arrays en SQL: técnicas y trucos en motores populares

Si trabajas con bases de datos relacionales y te preguntas cómo manejar estructuras tipo lista, no estás solo: el soporte de “arrays” en SQL no es uniforme ni está estandarizado entre motores. Eso implica que las soluciones varían dependiendo de si usas SQL Server, PostgreSQL, MySQL o SQLite, y además cambian con la versión y la compatibilidad del sistema.

En este artículo repasamos, con detalle y ejemplos prácticos, cómo simular o aprovechar los arrays según el SGBD, cuándo es mejor usar tablas temporales, TVPs, funciones como STRING_SPLIT o OPENJSON, y cómo abordar casos reales como contar etiquetas filtradas de un array. También verás por qué a veces conviene abandonar los arrays y modelar la información en tablas relacionales normales, y dónde están los cuellos de botella de rendimiento más habituales.

Qué entendemos por “array” en SQL y por qué no es estándar

Aunque nos gustaría tratar las listas como haríamos en un lenguaje de programación, SQL nació en los 70 orientado a datos tabulares, y el concepto de array como tipo primerizo no forma parte del estándar. Algunos motores incorporan tipos y funciones para listas, otros ofrecen extensiones, y otros no lo contemplan. La consecuencia práctica es que una misma necesidad tiene soluciones distintas según el motor, y conviene conocer sus matices para evitar errores y pérdidas de rendimiento.

Además, es frecuente la tentación de lanzar un IN con una lista separada por comas dentro de una variable (por ejemplo ‘1,2,3,4’) esperando que se expanda mágicamente. Eso no sucede: IN es un operador que el parser reescribe como una cadena de OR (col = val1 OR col = val2…), y una cadena ‘1,2,3,4’ es solo eso, una cadena. La falsa expectativa de “IN (@lista)” es una trampa clásica que conviene desterrar desde el inicio.

SQL Server: no hay arrays nativos, pero sí alternativas sólidas

En SQL Server no existen arrays como tal, de modo que la vía práctica es usar tablas temporales, variables de tabla o TVPs (table-valued parameters) para representar listas. Esta aproximación es natural en un sistema relacional y además escala bien si planificas índices y estadísticas.

Para lotes pequeños, una variable de tabla puede ser muy útil. Por ejemplo, cuando queremos capturar rangos o conjuntos reducidos, una @tabla puede suplir la necesidad de un array:

DECLARE @MyDateArray TABLE (StartDate DATETIME, EndDate DATETIME);
INSERT INTO @MyDateArray (StartDate, EndDate)
SELECT StartDate, EndDate
FROM Reservations1
WHERE DATEADD(day, 0, StartDate) >= @StartDate
  AND DATEADD(day, 0, EndDate)   <= @EndDate
  AND RequestId IN (
      SELECT RequestId
      FROM RequestModelMap1
      WHERE ModelSerialNumber = @ModelSerialNumber
  );

SELECT *
FROM @MyDateArray;

Cuando la lista crece, suele ser mejor una tabla temporal (#temp) o un TVP por cuestiones de estadísticas y optimización, sobre todo si esa lista participa en uniones y filtros complejos. Además, si tu front-end es .NET, los TVPs son cómodos, seguros y eficaces para pasar lotes de valores sin recurrir a concatenaciones frágiles.

Listas separadas por delimitadores en SQL Server: STRING_SPLIT, funciones, XML y JSON

Si ya tienes una lista separada por comas (o por otro delimitador) y no puedes cambiar el origen, SQL Server ofrece varias salidas. La más directa en versiones modernas es STRING_SPLIT, que descompone una cadena en filas:

SELECT ...
FROM dbo.TuTabla t
WHERE t.Col IN (
  SELECT CONVERT(int, value)
  FROM STRING_SPLIT('1,2,3,4', ',')
);

En SQL Server 2022 y Azure SQL puedes pedir también el ordinal (posición en la lista) pasando un tercer parámetro (1), útil para sincronizar múltiples listas paralelas. Antes de 2022 no existe esa columna ordinal y el delimitador solo puede ser de un carácter, así que ojo con escenarios más complejos. Si la BD está con compatibilidad < 130, ni siquiera tendrás STRING_SPLIT disponible, aunque estés en versión 2016 o superior.

Para quienes necesitan controlar el tipo, vacíos y espacios, hay funciones simples que puedes crear, por ejemplo una que convierte a int y otra para cadenas. Las versiones multi-sentencia son fáciles de adaptar a tus reglas (p. ej. devolver NULL en elementos vacíos, recortar espacios, soportar delimitadores de varios caracteres):

CREATE FUNCTION dbo.intlist_to_tbl(@list nvarchar(MAX), @delim nvarchar(10))
RETURNS @tbl TABLE (listpos int NOT NULL IDENTITY(1,1), n int NULL) AS
BEGIN
  DECLARE @pos int = 1, @nextpos int = 1, @valuelen int,
          @delimlen int = DATALENGTH(@delim) / 2;
  WHILE @nextpos > 0
  BEGIN
    SELECT @nextpos = CHARINDEX(@delim COLLATE Czech_BIN2, @list, @pos);
    SELECT @valuelen = (CASE WHEN @nextpos > 0 THEN @nextpos ELSE LEN(@list) + 1 END) - @pos;
    INSERT @tbl(n)
    VALUES (CONVERT(int, NULLIF(SUBSTRING(@list, @pos, @valuelen), '')));
    SELECT @pos = @nextpos + @delimlen;
  END;
  RETURN;
END;

El uso de una colación binaria acelera el hallazgo del delimitador, y el cálculo de longitudes con DATALENGTH/2 evita problemas con espacios. Para listas de cadenas, puedes devolver columnas varchar y nvarchar para respetar los tipos y no deshabilitar índices por conversiones implícitas.

Si no puedes crear funciones ni usar STRING_SPLIT, dos alternativas muy versátiles son XML y OPENJSON; para profundizar en su uso con JSON, consulta procesamiento de JSON en SQL. Con XML, reemplazas el delimitador por nodos y haces .nodes(‘/x/text()’); con JSON, envuelves la lista con corchetes y la abres con OPENJSON, recuperando key (posición 0‑based) y value:

DECLARE @list nvarchar(MAX) = '1,99,22,33,45';
SELECT CONVERT(int, ) + 1 AS listpos, CONVERT(int, value) AS n
FROM OPENJSON('');

Con XML/JSON tendrás mejor rendimiento en listas largas y, si trabajas con strings que incluyen caracteres especiales, recuerda protegerlos (por ejemplo, CDATA en XML) para no romper el parseo. Evita a toda costa generar SQL dinámico del tipo “… WHERE col IN (” + @lista + “)”: es frágil, inseguro (inyección) y suele rendir peor con listas extensas.

Un apunte de rendimiento clave: si vas a usar la lista en consultas complejas, carga primero los valores en una #tabla con PK para que el optimizador tenga estadísticas. Es habitual que un filtro IN contra una función TVF impida al optimizador estimar cardinalidades correctamente y seleccione planes subóptimos.

PostgreSQL: arrays nativos, UNNEST y LATERAL para consultas potentes

PostgreSQL sí dispone de arrays nativos y un ecosistema de funciones muy completo. La herramienta estrella para convertir un array en filas es UNNEST, a menudo con CROSS JOIN LATERAL para correlacionar columnas:

-- Contar solo elementos que empiecen por 'label_'
SELECT tag, COUNT(*) AS total
FROM public.description_labels t
CROSS JOIN LATERAL UNNEST(t.business_tags) AS u(tag)
WHERE tag LIKE 'label\_%'
GROUP BY tag
ORDER BY total DESC;

Este patrón resuelve el típico problema de “tengo una columna array y quiero filtrar y contar solo los elementos que cumplen una condición”. Aplicar LIKE o cualquier filtro debe hacerse sobre la columna “unnesteada”, no sobre el array original, porque el predicado no se empuja dentro de cada elemento si filtras el array como un todo.

Cuando necesitas operar en bloque con múltiples columnas, UNNEST permite pasar varios arrays en paralelo y los alinea por posición (como un “struct of arrays” a “array of structs”):

INSERT INTO test (id, name)
SELECT *
FROM UNNEST($1::INT[], $2::TEXT[]);

Esto elimina la generación dinámica de N placeholders y habilita un statement estático con solo dos parámetros que puede insertar tantos registros como quieras. En pruebas comparativas, la inserción con UNNEST supera a los inserts batched con VALUES (…),(…) al reducir parseo y planificación.

Para IN, también puedes usar UNNEST dentro del operador: WHERE id IN (SELECT * FROM UNNEST($1::INT[])). En ciertos contextos, construir placeholders dinámicos sigue siendo competitivo, pero UNNEST te da limpieza y reusabilidad del plan. Mide y elige según tu carga.

MySQL: JSON como contenedor de listas y funciones de búsqueda

MySQL no tiene un tipo array como tal, pero el tipo JSON permite almacenar listas y consultar sus elementos. Si una columna guarda un array JSON, puedes buscar con JSON_SEARCH o comprobar intersección con JSON_OVERLAPS:

CREATE TABLE supplier (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company VARCHAR(10),
  vehicles JSON
);
INSERT INTO supplier VALUES
(DEFAULT, 'A', '');

-- Buscar un valor concreto en el array JSON
SELECT *
FROM supplier
WHERE JSON_SEARCH(vehicles, 'one', 'Luton') IS NOT NULL;

Si la columna es de texto y contiene un JSON array válido, JSON_OVERLAPS(vehicles, JSON_ARRAY(‘Luton’)) te dirá si hay elementos comunes. Procura almacenar datos en JSON real cuando vayas a usar estas funciones; evitarás conversiones y errores sutiles con comillas y escapes.

Recuerda que estas funciones están disponibles en MySQL 8.x y conviene verificar la versión y el modo SQL para evitar sorpresas. En particular, la sintaxis y comportamiento de JSON_SEARCH/JSON_OVERLAPS es estable en 8.0.34, como en el ejemplo.

SQLite: sin arrays nativos; extensiones carray/rarray y sus límites

SQLite no incluye arrays nativos en el lenguaje SQL, pero dispone de extensiones como carray (y rarray en rusqlite) que exponen arreglos como tablas virtuales de una columna. Esto permite construir inserciones masivas o filtros tipo IN sin generar N placeholders:

-- Ejemplo de inserción combinando dos arrays con CROSS JOIN por rowid
INSERT INTO test (id, name)
SELECT *
FROM rarray(?) AS a
CROSS JOIN rarray(?) AS b
ON a.rowid = b.rowid;

El enfoque funciona, aunque se complica al aumentar columnas y puede no escalar en rendimiento. En pruebas, el uso de estas tablas virtuales ha mostrado peor desempeño que los inserts batched clásicos en SQLite, especialmente por cómo se implementa el acceso por rowid en las VTables. Conclusión: mide y no des por hecho que “array” en SQLite será más rápido; muchas veces insertar por lotes con statements preparados dentro de una transacción es lo óptimo.

Para IN, la idea de “IN (SELECT * FROM rarray(?))” es posible, pero también puede rendir peor que generar dinámicamente placeholders si la lista no es enorme. El consejo aquí es pragmático: elige la menor complejidad que te dé buen rendimiento en tu entorno y versión.

El anti‑patrón de listas delimitadas en columnas y el diseño orientado a tablas

Almacenar en una columna una lista separada por comas (o similar) contradice el principio relacional de atomicidad y suele complicar consultas, índices y mantenimientos. Un caso extremo son múltiples columnas con listas “sincronizadas” por posición (productos, cantidades, precios), algo difícil de actualizar y propenso a incoherencias.

La alternativa profesional es modelar en tablas hijas. Por ejemplo, convertir pedidos con productos/qty/precio en una tabla orderdetails y poblarla “desempaquetando” con STRING_SPLIT (con ordinal) o con funciones/OPENJSON/XML sincronizadas por posición. Una vez normalizados los datos, todo el ecosistema de índices y agregados juega a tu favor, y reconstruir una lista presentable puntualmente (string_agg a partir de 2017, o FOR XML PATH en 2016 y previas) es trivial.

En línea con esto, cuando necesites adjuntar atributos flexibles a una entidad (como una proforma), un esquema nombre‑valor (EAV) es más adaptable que arrays dentro de columnas. Una tabla atributos(id, idproforma, atributo, valor) permite añadir y filtrar atributos sin rediseñar columnas ni manipular matrices. Es más simple de consultar, de indexar y de mantener que un text[][] o una cadena JSON sin control.

Errores comunes y trampas con IN y compatibilidades

Como apuntábamos, IN no expande variables con listas delimitadas. Para que funcione, necesitas convertir la lista en filas (STRING_SPLIT, función, XML/JSON) o pasar un conjunto tabular (TVP, #tabla). Evita SQL dinámico: acarrea riesgos de inyección, planes no reutilizables y parsing costoso cuando la lista crece.

Comprueba siempre la versión y el nivel de compatibilidad. Por ejemplo, STRING_SPLIT requiere compatibilidad 130+ y el tercer argumento con ordinal solo existe en SQL Server 2022/Azure SQL. Si te sale un “Invalid object name ‘STRING_SPLIT’” en 2016, revisa el nivel de compatibilidad de la base antes de sacar conclusiones.

Cuando consumas documentación externa, recuerda que puede quedar obsoleta o movida. Si una referencia falla, usa el buscador del sitio o pide soporte; más de una vez la página “perfecta” para tu versión ya no está donde estaba y hay que localizar su sucesora o equivalente.

Patrones de rendimiento: por qué los arrays no siempre son la vía rápida

“Array” suena a velocidad, pero en SQL el rendimiento depende más de cómo se planifica y reutiliza el statement que de la estructura en sí. En PostgreSQL, UNNEST suele ganar en inserts masivos al mantener consultas estáticas con pocos parámetros. En SQLite, sin embargo, los arrays vía VTables pueden ser más lentos que los inserts batched tradicionales.

En SQL Server, descomponer listas a través de funciones en línea o OPENJSON/XML y mover el resultado a una #tabla con índice da al optimizador información crucial (estadísticas) para elegir índices y evitar scans. Ese pequeño paso suele pagar dividendos cuando las consultas se vuelven complejas.

Para filtros tipo IN en cualquier motor, si el tamaño del conjunto varía mucho, considera estrategias mixtas: placeholder dinámico para listas muy pequeñas, y estructura tabular (TVP/#temp/UNNEST) a partir de cierto umbral. Mide con tu carga de trabajo real; es la única forma de acertar.

Recetas rápidas por motor: lo esencial

SQL Server: usa TVPs/temporales/variables de tabla para “arrays”; STRING_SPLIT para listas delimitadas (con ordinal en 2022+); OPENJSON para necesidad de posición sin funciones; XML si JSON no encaja; evita IN con cadenas y el SQL dinámico.

PostgreSQL: apóyate en arrays nativos y UNNEST con LATERAL; filtra tras descomprimir (LIKE ‘label_%’, expresiones regulares, etc.); para inserts masivos, UNNEST paralelizando arrays por columna simplifica y acelera; valora si IN con UNNEST te compensa frente a placeholders.

MySQL: guarda listas como JSON y consulta con JSON_SEARCH/JSON_OVERLAPS; estandariza el formato de entrada (arrays JSON válidos) para evitar sorpresas; si partes de texto, convierte a JSON_ARRAY en la consulta con mesura.

SQLite: apuesta por transacciones, statements preparados y batches; rarray/carray puede servir en casos concretos, pero no esperes milagros de rendimiento; para N columnas, la combinación por rowid con CROSS JOIN se complica.

Por último, cuando veas columnas con listas, plantéate normalizar. Te ahorrará sufrimiento más adelante y hará que los planes del optimizador jueguen a tu favor, con menos magia y más índices.

Aunque no existe una única receta, ahora cuentas con un mapa claro: si tu SGBD no trae arrays, simúlalos con tablas; si los trae, exprímelos con las funciones nativas adecuadas; si recibes listas delimitadas, desmenúzalas correctamente; y si el rendimiento flojea, lleva esos valores a una estructura con estadísticas. Con estas pautas, el “manejo de arrays en SQL” deja de ser un laberinto y pasa a ser una caja de herramientas bien ordenada.