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

Početna » Piton » 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 list, no estás solo: el soporte de “arrays” en SQL no es uniforme ni está estandarizado entre motores. To implicira da rješenja ovise o varijantama koje koristite SQL Server, PostgreSQL, MySQL ili SQLite, y además cambian s verzijom i kompatibilnošću sustava.

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 liste como haríamos en un lenguaje de programación, SQL nació en los 70 orientado a tatos tabulares, y el concepto de array como tipo primerizo no forma parte del estándar. Algunos motores incorporan tipos y funciones para lists, 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 list parada por comas dentro de una variable (por ejemplo '1,2,3,4') esperando que se expanda mágicamente. Eso no sucede: IN es un operator que el parser reescribe como una cadena de OR (col = val1 ILI col = val2…), y una cadena '1,2,3,4' es solo eso, una cadena. Lažno očekivanje "IN (@lista)" je klasična trampa que conviene desterrar desde el inicio.

SQL Server: nema izvornih nizova, ali su alternativne čvrste

Na SQL Serveru ne postoje nizovi kao što je to, de modo que la vía practica es usar tablas temporales, varijable de tabla o TVPs (parametri s tabličnim vrijednostima) za predstavnike lista. 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;

Kad se popis otvori, suele ser mejor una tabla temporal (#temp) ili un TVP por cuestiones de estadísticas y optimización, sobre todo si esa list 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.

Popisi odvojeni za razgraničenja na SQL poslužitelju: STRING_SPLIT, funkcije, XML i JSON

Ako imate jedan popis odvojenih zarezima (ili drugim razgraničenjima) i nemate izvorno mjesto, SQL Server nudi različite stranice. 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', ',')
);

Na SQL Serveru 2022 y Azure SQL-u je prikazan también el (pozicija na popisu) pasando un tercer parameter (1), útil za sinkroniziranje više paralelnih popisa. 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. Ako je BD kompatibilan < 130, nije dostupan STRING_SPLIT, aunque estés en verzija 2016 ili 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.

Ako ne možete kreirati funkcije koje koristite STRING_SPLIT, dos alternativas muy versátiles son XML y OPENJSON; para profundizar en su uso con JSON, consulta obrada JSON-a u SQL-u. U XML-u, ponovo razgraničite čvorove i ima .nodes('/x/text()'); s JSON-om, 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 dinamički del tipo “… WHERE col IN (” + @lista + “)”: fragil, inseguro (inyección) y suele rendir per con extensas popis.

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: izvorni nizovi, UNNEST i LATERAL za moćne konzultacije

PostgreSQL je dostupan nativos nizovi y un ecosistema de funciones muy completo. La erramienta estrella para converter 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 passar varios arrays en paralelo y los alinea por posición (kao „struktura nizova“ i „niz struktura“):

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 izjava 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 kao kontender popisa i funkcija pisanja

MySQL nema tipskog niza kao što je tip, ali tip JSON dopušta pohranjivanje popisa y consultar sus elementos. Ako jedan stupac čuva polje JSON, možete ga pronaći pomoću JSON_SEARCH ili usporediti presjek s 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;

Ako je stupac teksta i sadrži JSON niz valjan, JSON_OVERLAPS(vozila, 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 bijeg.

Recuerda que estas funciones están disponibles en MySQL 8.xy conviene verificar la versión y el modo SQL para evitar sorpresas. Konkretno, sintaksa i usklađenost JSON_SEARCH/JSON_OVERLAPS je uspostavljena na 8.0.34, kao na primjeru.

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

SQLite ne uključuje izvorne nizove na SQL jeziku, pero dispone de extensiones como carray (y rarray en rusqlite) que exponen arreglos como tablas virtuales de una columna. Esto permite construir inserciones masivas or filteros 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;

Žarište funkcionira, samo 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, posebno za implementaciju el acceso por rowid en las VTables. Zaključak: 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.

Za IN, ideja "IN (SELECT * FROM niz(?))" je moguća, ali también puede rendir peor que generar dinámicamente rezervirana mjesta si la lista no es enorme. El consejo aquí es pragmático: elige la menor complejidad que te dé buen rendimiento u svojoj sredini i verziji.

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

Almacenar en una columna una lista separada por cos (ili slično) contradice el principio relacional de atomicidad y suele complicar consultas, índices y mantenimientos. Un caso extremo son višekratnik columnas con popisi “sincronizadas” por posición (productos, cantidades, precios), algo difícil de actualizar y propenso a incoherencias.

Profesionalna alternativa je modeli na tjednim tablicama. Na primjer, pretvorite podatke s proizvodima/količinom/cijenom u tablicu s detaljima o narudžbi i pokrenite "desempaquetando" s STRING_SPLIT (redni) ili s funkcijama/OPENJSON/XML sinkronizadama po položaju. Una vez normalizados los datos, todo el ecosistema de índices y agregados juega a tu favor, y reconstruir una presentable puntualmente listing (string_agg partir de 2017, o FOR XML PATH en 2016 y previas) je trivijalan.

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

Errores comunes y trampas con IN y compatibilidades

Kako se napunjavamo, IN nema proširenih varijabli s popisima razgraničenja. Za ovu funkciju potrebno je pretvoriti popis u datoteku (STRING_SPLIT, funkcija, XML/JSON) ili staviti u spojenu tablicu (TVP, #tabla). Evita SQL dinamički: 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 zahtijeva kompatibilnost 130+ y el tercer argumento con ordinal solo existe en SQL Server 2022/Azure SQL. Prodajete "Nevažeći naziv objekta 'STRING_SPLIT'" en 2016, revisa el nivel de compatibilidad de la base antes de sacar zaključke.

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" je brz, ali 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 parametros. En SQLite, sin embargo, los arrays via VTables pueden ser más lentos que los inserts batched tradicionales.

Na SQL Serveru, descomponer prikazuje través de funciones en línea o OPENJSON/XML y pokretač 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: rezervirano mjesto dinámico za popise muy pequeñas, I tabularna struktura (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 za popise razgraničenja (con ordinal en 2022+); OPENJSON para necesidad de posición sin funciones; XML i JSON bez unosa; evita IN con cadenas y el SQL dinamico.

PostgreSQL: apóyate en arrays nativos y UNNEST con LATERAL; filtra tras descomprimir (KAO 'label_%', expresiones regulares, itd.); za umetke masivos, UNNEST paralelizira nizove u stupcu simplifica y acelera; valora si IN con UNNEST te compensa frente a placeholders.

MySQL: čuvajte popise kao JSON i konzultirajte s JSON_SEARCH/JSON_OVERLAPS; estandariza el formato de entrada (nizovi JSON valjani) para evitar sorpresas; u dijelovima teksta, pretražite JSON_ARRAY i konzultirajte se s mjerom.

SQLite: apuesta por transacciones, statements preparados y batches; rarray/carray puede servir en casos concretos, pero no esperes milagros de rendimiento; za N stupaca, kombinacija retka s KRIŽNIM SPAJANJEM je složena.

konačno, 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: ako tu SGBD nema nizova, simulalos 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.