Procesamiento de JSON en SQL: toiminnot, konsultointi ja käsittely

Etusivu » Python » Procesamiento de JSON en SQL: toiminnot, konsultointi ja käsittely

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 modernis han responseido 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 BigQueryn, con eemplos 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 (esim. operadores, tips nativos y límites), y qué índices o formatos conviene usar para que JSON-kyselyiden skaalausNäet myös tuontiasiamiehet desde ficheros y servicios, y cómo abordar migraciones entre motores cuando hay funciones JSON specíficas de cada proveedor.

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

JSON on suosittu tekstimuoto intercambio de datos que brilla en escenarios web, móviles ja microservicios. SQL Server, Azure SQL -tietokanta, Azure SQL:n hallittu instanssi, Azure Synapse Analytics ja SQL-tietokanta Microsoft Fabricissa Incorporan Funciones para analysar, konsultoi y formatear JSON Directamente ja T-SQL. Ejemplo de documento típico: [ { \”nimi\”: \”John\”, \”taidot\”: [ \”SQL\”, \”C#\”, \”Azure\” ] }, { \”nimi\”: \”Jane\”, \”sukunimi\”: \”Doe\” } ].

Näiden toimintojen ansiosta voi lue eskaloituvia arvoja, ylimääräiset objektit tai taulukot, modifioidut propiedades sin reescribir todo el documento, aplanaariset lohkokokoelmia y JSON-muotoiset devolver-tulokset Lista API:asi varten. Lisäksi se combinan sin problema con columnas relacionales, agregados de ventana y cláusulas WHERE/TILAUS/RYHMÄ.

Novedades y compatibilidad: del texto nvarchar al tipo JSON nativo

Historiamente en SQL Server, el JSON se almacenaba en nvarchar/varchar y se procesaba con funciones nativas. SQL Server 2025 (17.x) esitellä en versio preliminar un natiivi JSON-tietotyyppi que Guara el documento en formato binario analizado para luennot, 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 recomomendada para konsultar y transformar el contenido.

Conviene matizar compatibilidad: en documentación previa verás que "JSON ei ole integroitu tyyppi”uusissa painoksissa” 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.

JSON- ja SQL-palvelimen olennaiset toiminnot

El núcleo del soporte T‑SQL se apoya en utilidades muy directas: ISJSON (voimassa) JSON_VALUE (ylimääräisiä eskalaatioita) JSON_QUERY (devuelve-objektit/taulukot) y JSON_MODIFY (actualiza rutas). Estas funciones enablen filterr, 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 aktualizar 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"}]}}

JSON-taulukoista OPENJSON-tiedostoihin

Para aplanar colecciones JSON y consultarlas con SELECT, OPENJSON convierte arrays u objetos en filas/columnas y allowe tipado explícito con la cláusula WITH. Entre sus reglas: muuntaa ilmoitetun tyypin mukaiseksi, tukipalvelut y devuelve NULL si falta un campo; además puedes marcar rutas como tiukka olemassaolon varmistamiseksi.

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 sisältää alijärjestelmät (esim. taitoja dentro de info), encadena otro OPENJSON con APPLY para expandirlo y unirlo al registro padre. ULKOINEN SOVELTAMINEN toistaa 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 validció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 ja agregados nativos

Sovellusliittymän käyttö edellyttää JSON-sovellusta, JSON-TIEDOT delega el formateo en el motor. Con PATH puedes anidar usando alias con puntos; AUTO deriva estructura según las tablas/liittyy. Ihanteellinen OData-, AJAX- tai käyttöliittymälle ja JavaScriptille.

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

Además, josta on pääsy JSON-koottuja tietoja para construir estructuras desde grupos: JSON_OBJECTAGG (esineesiä) y JSON_ARRAYAGG (taulukot). Yksinkertainen hyötykuormien luominen ilman SQL-päätettä.

Almacenamiento, indexación y rendimiento en SQL Server

Para almacenar documentos completos, la option universal es nvarchar(maksimi) (o json nativo cuando estén disponible). Hyväksy TARKISTUS (ISJSON(sara)=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/actualisación asiakirjoista, harkitaan tablas optimizadas para memoria (con la salvedad de tipos soportados) y procedimientos compilados nativamente para operaciones de aktualizació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;

Kuten hybridipatroni, desnormaliza lo variable fi JSON y conserva relacional lo consultado a menudoEn tiedottaa, Yhdysvallat OPENJSON-konversio para preparar datasets rectangulares, y añade JSON_VALUE()-funktion indeksit cuando la ruta se usa en filtros/ordenaciones.

Carga e importación desde archivos y servicios

Vastaa JSON-tiedostot tai päätepisteet REST, puedes cargarlo tal cual y parsearlo en SQL. Con acceso a archivos locales o compartidos, el motor myönsi massaoperaatiot; para orígenes distribuidos como Hadoop tai Azure Blob, PolyBase sallia tekstin JSON ja mapearlo con OPENJSON/JSON_VALUE T-SQL:ssä.

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;

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

Casos de uso y modelo hibrido

Los mejores candidatos para JSON ja SQL sisältää mallit de datos con atributos muuttujat (verkkokauppa) telemetria ja lokit, IoT-puolirakenteinen con analisis casi en tiempo real y REST-näyttely SQL-käyttöliittymistä alkaen. Yleinen suositus es desnormalizar lo necesario, mantener claves de consulta como columnas escalares e indexar-rutit kritiikkiä.

En escenarios de seguridad o internacionalización, JSON se kulkee tekstin tavoin ja hyötyy todas las características del motor (tablas temporales, seguridad a nivel de fila jne.). Esto facilita políticas univormut y evita excepciones en pipelines mixtos.

BigQuery: JSON-tyyppinen nativo ja konsultointi GoogleSQL:llä

BigQuery tarjoaa palveluja natiivi JSON-tietotyyppi pääsy kampuksille operador de punto y subíndice. Puedes crear valores JSON con literales JSON, PARSE_JSON (muunna STRING JSON-tiedostoksi), TO_JSON (convierte tipos SQL ja JSON), ja muodostaa JSON_ARRAY-taulukot u JSON_OBJECT-objektit.

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

Listakohtaisesti JSON_QUERY_ARRAY ottaa talteen MATRIISI ja PESÄTÖN puedes expandir a filas. Tämä on välttämätön kompakti, usa ARRAY_AGG. Para comparaciones/ordenación, JSON_VALUE devuelve escalares como STRING SQL, y LAX_*-toiminnot (esimerkiksi LAX_INT64) muunneltavissa suvaitsevaisuuden kanssa.

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

Tärkeää con valores nulos: el null de JSON ei ole NULL de SQL. JSON_QUERY voi devolver null JSON; JSON_VALUE devuelve NULL SQL ja no hay escalar. Tenlo suodattimien kautta ja yhdistelmät.

Rajoitukset ja kuorma: syvyys enintään 500 de anidación; no puedes particionar tai clusterizar por columnas JSON (ei heinää comparadores definidos). Para ingerir, usa CSV o JSONL rahtitöissä, Tallennuskirjoitus-API nykyaikaiseen suoratoistoon Perinteinen vaihteiston API si menettely; en todos los casos, selviytyä cuando JSON viaja como STRING.

Oracle ja PostgreSQL: ekvivalenssit y migración

Oracle-ohjelmassa (12.2+), JSON_TAULUKKO transforma documentos en filas con PATH por columna y manejo de errores. Muuttomatka es que AWS SCT ei ole automatisoitu JSON de Oracle ja PostgreSQL, joka on idiomatico usa operaattorit -> y ->> lisää JSON-koontitoimintoja.

PostgreSQL: ssä -> devuelve JSON y - >> devuelve texto, mitä facilita CAST explicito y uso en joins/agrupaciones. Para construir estructuras, emplea JSON_BUILD_OBJECT y JSON_AGG (vastaa Oraclen JSON_OBJECT:tä ja JSON_ARRAYAGG:tä). Recuerda-kasteari 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 Conversion: simuloitu JSON_TABLE kanssa json_array_elementit para expandir arrays, usea ->> cuando vayas a castear y homogeeninen takuu al agropar tai unir. Tämä on yllätys 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 dinamicos kanssa columnas relacionales para claves de negocio. Lisätä índices en columnas calculadas (SQL Server) o GIN/expresiones (PostgreSQL), sovellus vahvistukset (TARKISTA ISJSON, JSON Schema en la app) ja suunnitelma vientireitit BI-mallit. Así tendrás agilidad de esquema sin perder control en informes.

En turvaudu, trata JSON como cualquier toinen dato järkevä: cifrado en tránsito y reposo, controles de acceso, auditoría y endurecimiento de sistemas. Para analítica, prepara ETL:n toteutuneet näkymät que descompongan JSON en tablas fact/dim si la herramienta (esim. Power BI) lo demanda. Este enfoque híbrido es el que mejor escala ja sekalaisia ​​lasteja.

Un truco útil en T-SQL: Recorrer arrays sin kohdistin

Tarvitset 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 luettavissa, este enfoque evita errores de índice y problems de rendimientos asociados a WHILE/cursores, y es el Recomendado por el propio motor cuando trabajas con JSON-kokoelmat.

Para organizaciones que buscan ayuda de extremo a extremo, firmas especializadas como Q2BSTUDIO implementan patrones hibridos, 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 specíficas.

Integrar JSON en SQL konsistenssi en elegir el tipo de almacenamiento correcto (alkuperäinen tai teksti), saber usar las funciones de cada motor (T-SQL, GoogleSQL, Operadores de Postgres, JSON_TABLE en Oracle), y laskeutua indekseissä ja näkymissä para que consultas ja API:t sean ágiles. Con estas piezas bien encajadas, puedes kirjoittaja, konsultti, muuntaja ja palvelu JSON ja escala manteniendo gobierno, seguridad y rendimiento en toda la plataforma.