Procesamiento de JSON en SQL: funciones, consultas y rendimiento

Home » Python » Procesamiento de JSON en SQL: funciones, consultas y rendimiento

Trabajar con datos semiestructurados ya no es opcional: JSON se ha convertido en el idioma común para APIs, apps móviles y registros, y los motores SQL modernos han respondido con funciones nativas y patrones de diseño para integrarlo sin fricciones. En esta guía práctica vas a encontrar cómo consultarlo, transformarlo y almacenarlo de forma eficiente en SQL Server, Azure SQL, PostgreSQL, Oracle y BigQuery, con ejemplos comentados y recomendaciones de rendimiento.

Más allá de las demos, el objetivo es operativo: explicar qué hacer en cada plataforma, dónde están las diferencias (por ejemplo, operadores, tipos nativos y límites), y qué índices o formatos conviene usar para que las consultas JSON escalen. También verás patrones de importación desde ficheros y servicios, y cómo abordar migraciones entre motores cuando hay funciones JSON específicas de cada proveedor.

Qué es JSON en el mundo SQL y dónde se aplica

JSON es un formato de texto popular para intercambio de datos que brilla en escenarios web, móviles y microservicios. SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics y SQL Database en Microsoft Fabric incorporan funciones para analizar, consultar y formatear JSON directamente en T‑SQL. Ejemplo de documento típico: [ { \”name\”: \”John\”, \”skills\”: [ \”SQL\”, \”C#\”, \”Azure\” ] }, { \”name\”: \”Jane\”, \”surname\”: \”Doe\” } ].

Con estas funciones puedes leer valores escalares, extraer objetos o arrays, modificar propiedades sin reescribir todo el documento, aplanar colecciones en filas y devolver resultados como JSON listo para tu API. Además, se combinan sin problema con columnas relacionales, agregados de ventana y cláusulas WHERE/ORDER BY/GROUP BY.

Novedades y compatibilidad: del texto nvarchar al tipo JSON nativo

Históricamente en SQL Server, el JSON se almacenaba en nvarchar/varchar y se procesaba con funciones nativas. SQL Server 2025 (17.x) introduce en versión preliminar un tipo de datos JSON nativo que guarda el documento en formato binario analizado para lecturas, escrituras y compresión más eficientes. Esto convive con el modelo clásico: las funciones ISJSON/JSON_VALUE/JSON_QUERY/JSON_MODIFY y OPENJSON siguen siendo la vía recomendada para consultar y transformar el contenido.

Conviene matizar compatibilidad: en documentación previa verás que “JSON no es un tipo integrado” en ciertas ediciones; la introducción del tipo nativo llega más tarde y puede no estar disponible en todos los entornos. Por eso, elige nvarchar(max) o el nuevo tipo JSON en función de la plataforma, y mantén el acceso a través de las funciones JSON para asegurar portabilidad y rendimiento.

Funciones esenciales de JSON en SQL Server

El núcleo del soporte T‑SQL se apoya en utilidades muy directas: ISJSON (valida), JSON_VALUE (extrae escalares), JSON_QUERY (devuelve objetos/arrays) y JSON_MODIFY (actualiza rutas). Estas funciones permiten filtrar, ordenar y agrupar por valores almacenados dentro del documento JSON sin necesidad de sacarlo de la base.

Ejemplo de mezcla relacional+JSON con filtros y ordenación por rutas JSON: selecciona campos de People y extrae código postal, dirección y skills.

SELECT Name,
       Surname,
       JSON_VALUE(jsonCol, '$.info.address.PostCode')     AS PostCode,
       JSON_VALUE(jsonCol, '$.info.address."Address Line 1"') + ' ' +
       JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
       JSON_QUERY(jsonCol, '$.info.skills')               AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
  AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
  AND Status = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

Para actualizar una propiedad concreta sin reescribir el documento: JSON_MODIFY localiza la ruta y sustituye el valor.

DECLARE @json nvarchar(max) = '{"info": {"address": [{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');
SELECT @json; -- {"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}}

De JSON a tablas con OPENJSON

Para aplanar colecciones JSON y consultarlas con SELECT, OPENJSON convierte arrays u objetos en filas/columnas y permite tipado explícito con la cláusula WITH. Entre sus reglas: convierte al tipo indicado, soporta jerarquías y devuelve NULL si falta un campo; además puedes marcar rutas como strict para exigir que existan.

DECLARE @json nvarchar(max) = N'[
 {"id":2, "info":{"name":"John", "surname":"Smith"}, "age":25},
 {"id":5, "info":{"name":"Jane", "surname":"Smith"}, "dob":"2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json)
WITH (
  id         int            'strict $.id',
  firstName  nvarchar(50)   '$.info.name',
  lastName   nvarchar(50)   '$.info.surname',
  age        int,
  dateOfBirth datetime2     '$.dob'
);

Si el documento incluye subarrays (por ejemplo, skills dentro de info), encadena otro OPENJSON con APPLY para expandirlo y unirlo al registro padre. OUTER APPLY repetirá la fila de la entidad por cada elemento del subarray (útil para informes o normalizaciones parciales).

DECLARE @json nvarchar(max) = N'[
 {"id":2, "info":{"name":"John", "surname":"Smith"}, "age":25},
 {"id":5, "info":{"name":"Jane", "surname":"Smith", "skills":["SQL","C#","Azure"]},
  "dob":"2005-11-04T12:00:00"}
]';

SELECT id, firstName, lastName, age, dateOfBirth, skill
FROM OPENJSON(@json)
WITH (
  id           int           'strict $.id',
  firstName    nvarchar(50)  '$.info.name',
  lastName     nvarchar(50)  '$.info.surname',
  age          int,
  dateOfBirth  datetime2     '$.dob',
  skills       nvarchar(max) '$.info.skills' AS JSON
) AS P
OUTER APPLY OPENJSON(P.skills) WITH (skill nvarchar(32) '$');

Una alternativa muy práctica cuando solo quieres validación y extracción ligera es JSON_VALUE para campos escalares con índices en expresiones. Así se obtiene rendimiento al filtrar por rutas frecuentes.

De tablas a JSON: FOR JSON y agregados nativos

Cuando la aplicación o tu API necesitan JSON, FOR JSON delega el formateo en el motor. Con PATH puedes anidar usando alias con puntos; AUTO deriva estructura según las tablas/joins. Es ideal para OData, AJAX o frontends en JavaScript.

SELECT id,
       firstName AS "info.name",
       lastName  AS "info.surname",
       age,
       dateOfBirth AS dob
FROM People
FOR JSON PATH;

Además, dispones de agregados JSON para construir estructuras desde grupos: JSON_OBJECTAGG (objetos) y JSON_ARRAYAGG (arrays). Simplifican la creación de payloads agregados sin salir de SQL.

Almacenamiento, indexación y rendimiento en SQL Server

Para almacenar documentos completos, la opción universal es nvarchar(max) (o json nativo cuando esté disponible). Valida con CHECK(ISJSON(col)=1) si necesitas asegurar integridad de formato, y exponte campos clave con columnas calculadas para indexar propiedades usadas en búsquedas.

CREATE TABLE WebSite.Logs (
  [_id]  bigint IDENTITY PRIMARY KEY,
  [log]  nvarchar(max),
  [severity] AS JSON_VALUE([log], '$.severity'),
  INDEX ix_severity (severity)
);
-- Opcional: forzar JSON válido
ALTER TABLE WebSite.Logs
ADD CONSTRAINT CK_Log_JSON CHECK (ISJSON([log]) = 1);

Para cargas analíticas y archivado masivo, un índice Columnstore agrupado en la colección aporta compresión y escaneos rápidos. Si predomina la escritura/actualización de documentos, considera tablas optimizadas para memoria (con la salvedad de tipos soportados) y procedimientos compilados nativamente para operaciones de actualización con JSON_MODIFY.

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log]  nvarchar(max)
) WITH (MEMORY_OPTIMIZED = ON);
GO
CREATE PROCEDURE WebSite.UpdateData
  @Id int, @Property nvarchar(100), @Value nvarchar(100)
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (transaction isolation level = snapshot, language = N'English')
  UPDATE WebSite.Logs
  SET [log] = JSON_MODIFY([log], @Property, @Value)
  WHERE _id = @Id;
END;

Como patrón híbrido, desnormaliza lo variable en JSON y conserva relacional lo consultado a menudo. En informes, usa OPENJSON con WITH para preparar datasets rectangulares, y añade índices sobre JSON_VALUE() cuando la ruta se usa en filtros/ordenaciones.

Carga e importación desde archivos y servicios

Si recibes JSON desde ficheros o endpoints REST, puedes cargarlo tal cual y parsearlo en SQL. Con acceso a archivos locales o compartidos, el motor admite operaciones bulk; para orígenes distribuidos como Hadoop o Azure Blob, PolyBase permite ingerir texto JSON y mapearlo con OPENJSON/JSON_VALUE en T‑SQL.

DECLARE @jsonVariable nvarchar(max) = N'[
 { "Order": {"Number":"SO43659", "Date":"2011-05-31T00:00:00"},
   "AccountNumber":"AW29825", "Item": {"Price":2024.9940, "Quantity":1}},
 { "Order": {"Number":"SO43661", "Date":"2011-06-01T00:00:00"},
   "AccountNumber":"AW73565", "Item": {"Price":2024.9940, "Quantity":3}}
]';

SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, '$') WITH (
  Number    varchar(200)  '$.Order.Number',
  Date      datetime      '$.Order.Date',
  Customer  varchar(200)  '$.AccountNumber',
  Quantity  int           '$.Item.Quantity'
) AS SalesOrderJsonData;

Cuando el tipo json nativo esté disponible en tu plataforma, aprovéchalo por eficiencia de E/S y compresión; si no, nvarchar(4000) ofrece ventajas de rendimiento frente a nvarchar(max) si sabes que los documentos no superan 8 KB.

Casos de uso y modelo híbrido

Los mejores candidatos para JSON en SQL incluyen modelos de datos con atributos variables (e‑commerce), telemetría y logs, IoT semiestructurado con análisis casi en tiempo real y exposición REST desde SQL a frontends. La recomendación general es desnormalizar lo necesario, mantener claves de consulta como columnas escalares e indexar rutas críticas.

En escenarios de seguridad o internacionalización, JSON se comporta como texto y se beneficia de todas las características del motor (tablas temporales, seguridad a nivel de fila, etc.). Esto facilita políticas uniformes y evita excepciones en pipelines mixtos.

BigQuery: tipo JSON nativo y consulta con GoogleSQL

BigQuery ofrece un tipo de datos JSON nativo con acceso a campos por operador de punto y subíndice. Puedes crear valores JSON con literales JSON, PARSE_JSON (convierte STRING a JSON), TO_JSON (convierte tipos SQL a JSON), y construir arrays con JSON_ARRAY u objetos con JSON_OBJECT.

-- Crear tabla con columna JSON
CREATE OR REPLACE TABLE mydataset.table1 (
  id   INT64,
  cart JSON
);
-- Insertar literales JSON
INSERT INTO mydataset.table1 VALUES
  (1, JSON '{"name":"Alice","items":[{"product":"book","price":10},{"product":"food","price":5}]}'),
  (2, JSON '{"name":"Bob","items":[{"product":"pen","price":20}]}');

-- Acceso por punto y subíndice
SELECT cart.name, cart.items[0] AS first_item
FROM mydataset.table1;

Para listas, JSON_QUERY_ARRAY extrae ARRAY<JSON> y con UNNEST puedes expandir a filas. Si necesitas volver a compactar, usa ARRAY_AGG. Para comparaciones/ordenación, JSON_VALUE devuelve escalares como STRING SQL, y funciones LAX_* (por ejemplo LAX_INT64) convierten con tolerancia.

-- Expandir items y leer el campo product
SELECT id, JSON_VALUE(item.product) AS product
FROM mydataset.table1,
UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
ORDER BY id;

-- Conversión flexible de tipos
SELECT LAX_INT64(JSON '"10"') AS id; -- 10

Importante con valores nulos: el null de JSON no es el NULL de SQL. JSON_QUERY puede devolver null JSON; JSON_VALUE devuelve NULL SQL si no hay escalar. Tenlo en cuenta en filtros y agregaciones.

Limitaciones y carga: profundidad máxima 500 de anidación; no puedes particionar o clusterizar por columnas JSON (no hay comparadores definidos). Para ingerir, usa CSV o JSONL en jobs de carga, Storage Write API para streaming moderno o la API de transmisión heredada si procede; en todos los casos, respeta el escapado cuando JSON viaja como STRING.

Oracle y PostgreSQL: equivalencias y migración

En Oracle (12.2+), JSON_TABLE transforma documentos en filas con PATH por columna y manejo de errores. El reto al migrar es que AWS SCT no convierte automáticamente funciones JSON de Oracle a PostgreSQL, donde el enfoque idiomático usa operadores -> y ->> más funciones de agregación JSON.

En PostgreSQL, -> devuelve JSON y ->> devuelve texto, lo que facilita CAST explícito y uso en joins/agrupaciones. Para construir estructuras, emplea JSON_BUILD_OBJECT y JSON_AGG (equivalentes a JSON_OBJECT y JSON_ARRAYAGG de Oracle). Recuerda castear cuando necesites tipos numéricos o fechas para comparaciones.

-- Lectura en PostgreSQL: rutas jerárquicas y casteo con ->>
SELECT
  (json_doc::json -> 'data' -> 'account' ->> 'parentAccountNumber')::int AS parentAccountNumber,
  (json_doc::json -> 'data' -> 'account' ->> 'accountNumber')::int      AS accountNumber,
  (json_doc::json -> 'data' -> 'account' ->> 'businessUnitId')::int      AS businessUnitId,
  (json_doc::json -> 'data' ->> 'positionId')::varchar                   AS positionId
FROM aws_test_pg_table;

-- Agregación a JSON (equivalente conceptual a Oracle)
SELECT JSON_BUILD_OBJECT(
  'accountCounts', JSON_AGG(
     JSON_BUILD_OBJECT(
       'businessUnitId', businessUnitId,
       'parentAccountNumber', parentAccountNumber,
       'accountNumber', accountNumber,
       'totalOnlineContactsCount', online_contacts_count,
       'countByPosition', JSON_BUILD_OBJECT(
          'taxProfessionalCount', tax_professional_count,
          'attorneyCount',       attorney_count,
          'nonAttorneyCount',    non_attorney_count,
          'clerkCount',          clerk_count
       )
     )
  )
)
FROM (...) t; -- Agrega unifica y castea antes de construir el objeto

Buena práctica en la conversión: simula JSON_TABLE con json_array_elements para expandir arrays, utiliza ->> cuando vayas a castear y garantiza tipos homogéneos al agrupar o unir. Esto te evita sorpresas de colaciones/ordenaciones y maximiza el rendimiento con índices funcionales o GIN según el caso.

Patrones de implementación, seguridad y BI

Para arquitecturas empresariales, combina documentos JSON para atributos dinámicos con columnas relacionales para claves de negocio. Añade índices en columnas calculadas (SQL Server) o GIN/expresiones (PostgreSQL), aplica validaciones (CHECK ISJSON, JSON Schema en la app) y planifica vías de exportación a modelos de BI. Así tendrás agilidad de esquema sin perder control en informes.

En seguridad, trata JSON como cualquier otro dato sensible: cifrado en tránsito y reposo, controles de acceso, auditoría y endurecimiento de sistemas. Para analítica, prepara vistas materializadas o ETL que descompongan JSON en tablas fact/dim si la herramienta (por ejemplo Power BI) lo demanda. Este enfoque híbrido es el que mejor escala en cargas mixtas.

Un truco útil en T‑SQL: recorrer arrays sin cursor

Si necesitas iterar elementos de un array JSON, evita concatenar rutas en bucles. OPENJSON con APPLY es más limpio, rápido y seguro. Un patrón típico para un procedimiento que inserta elementos de un array en una tabla sería:

DECLARE @json nvarchar(max) = N'{
  "timeCardLogs": [
    {"day":"2023-07-03T00:00:00", "totalHours":"01:52"},
    {"day":"2023-07-04T00:00:00", "totalHours":"02:09"}
  ]
}';

-- Expandir el array y proyectar columnas tipadas
SELECT 
  JSON_VALUE(j.value, '$.day')        AS Day,
  JSON_VALUE(j.value, '$.totalHours') AS TotalHours
FROM OPENJSON(@json, '$.timeCardLogs') AS j;

-- Inserción ejemplo
-- INSERT INTO dbo.TimeCard(Day, TotalHours)
-- SELECT JSON_VALUE(j.value, '$.day'), JSON_VALUE(j.value, '$.totalHours')
-- FROM OPENJSON(@json, '$.timeCardLogs') AS j;

Además de ser más legible, este enfoque evita errores de índice y problemas de rendimientos asociados a WHILE/cursores, y es el recomendado por el propio motor cuando trabajas con colecciones JSON.

Para organizaciones que buscan ayuda de extremo a extremo, firmas especializadas como Q2BSTUDIO implementan patrones híbridos, despliegues cloud (AWS/Azure), ciberseguridad y automatización con IA. La clave está en combinar el diseño de datos con renderizado JSON en la base, pipelines robustos y visualización en suites BI, con acompañamiento en migraciones entre motores cuando hay funciones JSON específicas.

Integrar JSON en SQL consiste en elegir el tipo de almacenamiento correcto (nativo o texto), saber usar las funciones de cada motor (T‑SQL, GoogleSQL, operadores de Postgres, JSON_TABLE en Oracle), y apoyarse en índices y vistas para que consultas y APIs sean ágiles. Con estas piezas bien encajadas, puedes ingerir, consultar, transformar y servir JSON a escala manteniendo gobierno, seguridad y rendimiento en toda la plataforma.