Procesamiento de JSON en SQL: Funciones, consultas y rendimiento

Hjem » Python » Procesamiento de JSON en SQL: Funciones, consultas y rendimiento

Trabajar con datas semiestructurados ya no es optional: JSON er konverteret til el idioma común for API'er, apps móviles og 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 alle de las demoer, el objetivo es operativo: forklare qué hacer en cada plataforma, dónde están las diferencias (for eksempel, operadores, tipos nativos y límites), y qué índices o formatos conviene usar para que JSON-skaleringskonsultationerDu vil også se importører 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 donde se aplica

JSON er et populært tekstformat til intercambio data que brilla en escenarios web, mobile og microservicios. SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics og SQL Database i Microsoft Fabric inkorporer funktioner til analyse, rådgivning og JSON-formatering og T-SQL. Eksempel på dokumenttype: [ { \”navn\”: \”John\”, \”færdigheder\”: [ \”SQL\”, \”C#\”, \”Azure\” ] }, { \”navn\”: \”Jane\”, \”efternavn\”: \”Doe\” } ].

Med disse funktioner kan læs stigende værdier, ekstraher objekter eller arrays, ændre ejendomme sin reescribir todo el documento, aplanar samlinger i filas y devolver resultater som JSON liste til din API. Derudover, se combinan sin problema con columnas relacionales, agregados de ventana y cláusulas HVOR/ORDER BY/GROUP BY.

Nyheder og kompatibilitet: af teksten til den oprindelige JSON-type

Historien på SQL Server, eller JSON er almacenaba en nvarchar/varchar y se processaba con funciones nativas. SQL Server 2025 (17.x) introducere en version preliminar un JSON-datatype (oprindelig) que guarda el documento en formato binario analizado para forelæsninger, skrift og kompression mere effektiv. Esto convive con el modelo clásico: funktionerne ISJSON/JSON_VALUE/JSON_QUERY/JSON_MODIFY og OPENJSON ses som en anbefaling til at konsultere og transformere indholdet.

Conviene matizar compatibilidad: en documentación previa verás que “JSON er ikke en integreret type"i bøgerne"; la introducción del tipo nativo llega más tarde y puede no estar disponible en todos los entornos. For så vidt, 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.

Nødvendige funktioner på JSON og SQL Server

Den nucleo del af T-SQL kan bruges til at bruge mange direkte: ISJSON (gyldig), JSON_VALUE (ekstra escalerer), JSON_QUERY (udvikle objekter/arrays) y JSON_MODIFY (actualiza rutas). Estas funktioner tillader filtre, ordner og agrupar af valores almacenados dentro del dokumentet JSON sin necesidad de sacarlo de la base.

Eksempler på mezcla relacional+JSON med filtre og ordenación af rutas JSON: selecciona campos de People y extrae código postal, directció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');

For at aktualisere en konkretiseret beskrivelse af dokumentet: 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-tabeller med OPENJSON

Para aplanar colecciones JSON y consultarlas con SELECT, OPENJSON konvierer arrays til objekter i filas/søjler y permite tipado explícito con la cláusula MED. Entre sus reglas: konverter til angivet type, støtter jerarquías og devuelve NULL er falta un campo; además puedes marcar rutas como streng for at udsættes for noget, der eksisterer.

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

Dette dokument indeholder underarrays (af eksemplet, færdigheder dentro de info), encadena otro ÅBENJSON con APPLY for expandirlo y unirlo al registro padre. YDRE 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 for campos escalares con indices and expresiones. Así se obtiene rendimiento al filtrar por rutas frecuentes.

Oversigt over JSON: FOR JSON og indfødte

Brug applikationen til API nødvendig JSON, TIL JSON delega el formateo en el motor. Con PATH puedes anidar usando alias con puntos; AUTO deriva estructura según las tablas/joins. Det er ideelt til OData, AJAX eller frontends og JavaScript.

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

Además, disponerer samlede JSON-filer til opbygning af strukturer af grupper: JSON_OBJECTAGG (genstande) y JSON_ARRAYAGG (arrays). Simplifican la creación de payloads agregados uden SQL-salg.

Almacenamiento, indeksering og rendimiento på SQL Server

Til almacenar documentos completos, la opción universal es nvarchar(max) (o json nativo cuando esté disponible). Gyldig med 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);

For analyser og arkivering, un índice Columnstore agrupado en la colección aporta compresión y escaneos rápidos. Si predomina la escritura/actualización dokumenter, overvejet tablas optimeret til memoria (con la salvedad de tipos soportados) y procedimientos compilados nativamente til aktualiseringsoperationer med 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;

Som en hybrid beskytter, desnormaliza lo variabel en JSON y conserva relacional lo consultado a menudoEn information, USA OPENJSON med MED para forberedelse af datasæt rektangulære, y añade Indekser over JSON_VALUE() cuando la ruta se usa en filtros/ordenaciones.

Transport og import af arkiver og tjenester

Du modtager JSON som angiver oplysninger om endepunkter REST, puedes cargarlo tal cual og parsearlo en SQL. Få adgang til lokale arkiver eller kompartidos, eller motoradmite bulkoperationer; para orígenes distribuidos como Hadoop eller Azure Blob, PolyBase tillade ingerir tekst til JSON og mapearlo con OPENJSON/JSON_VALUE i 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;

Når typen er json nativ esté disponible en tu plataforma, aprovéchalo por eficiencia de E/S y compresión; men, nvarchar(4000) ofrece ventajas de rendimiento frente a nvarchar(max) si sabes que los documentos no superan 8 KB.

Casos de uso og modelo hibrido

De bedste kandidater til JSON og SQL inkluderer modeller af data med attributvariabler (e-handel), telemetri og logfiler, Semistruktureret IoT con análisis casi en tiempo real y REST-udstillingen fra SQL til frontends. Den generelle anbefaling es desnormalizar lo necesario, mantener claves de consulta como columnas escalares e indekser ruter kritikpunkter.

En escenarios de seguridad eller internacionalización, JSON se comportá som tekst og fordele ved todas las motorkarakteristika (tablas temporales, seguridad a nivel de fila osv.). Esto facilita politiques uniformes y evita excepciones en pipelines mixtos.

BigQuery: tip til JSON-nativo og konsultation med GoogleSQL

BigQuery tilbyder en JSON-datatype (oprindelig) med adgang til campos por operador de punto y subíndice. Puedes crear valores JSON con literales JSON, PARSE_JSON (konverter STRING til JSON), TO_JSON (overfør SQL til JSON), og konstruerer arrays med JSON_ARRAY u objekter med 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 lister, JSON_QUERY_ARRAY uddrag ARRAY og UNNEST puedes expandir a filas. Si necesitas volver a compactar, usa ARRAY_AGG. Para comparaciones/ordenación, JSON_VALUE devuelve escalares som STRING SQL, y LAX_* funktioner (f.eks LAX_INT64) overvinde med tolerance.

-- 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 kan devolvere null JSON; JSON_VALUE devuelve NULL SQL og ingen hø escalar. Hold øje med filtrene og sammenlægninger.

Begrænsninger og last: maksimal dybde på 500 anidation; ingen particionar eller clusterizar af kolonner JSON (ingen hay comparadores definidos). For ingerir, usa CSV o JSONL i fragtjobs, Storage Write API til moderne streaming eller Herdet transmissions-API si gå videre; en todos los casos, respekt for eskapaden cuando JSON viaja como STRING.

Oracle og PostgreSQL: ækvivalenter og migration

I Oracle (12.2+), JSON_TABLE transforma documentos en filas con PATH por columna y manejo de errores. El reto al migrar det er AWS SCT har ingen automatiske funktioner JSON de Oracle og PostgreSQL, ikke enfoque idiomático usa operatører -> y ->> flere funktioner til sammenlægning af JSON.

I PostgreSQL, -> udvikle JSON y - >> afgiv teksten, hvad facilita CAST eksplicito y uso en joins/agrupaciones. Til opbygning af strukturer, emplea JSON_BUILD_OBJECT y JSON_AGG (svarende til JSON_OBJECT og JSON_ARRAYAGG af 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 konvertering: simulering af JSON_TABLE med json_array_elements til at udvide arrays, udnytte ->> cuando vayas a castear y garanti for homogene typer al agrupar eller unir. Dette undgår du overraskelser de colaciones/ordenaciones y maximiza el rendimiento con índices funcionales o GIN según el caso.

Patrones de implementación, securidad og BI

Para arquitecturas empresariales, combina JSON-dokumenter til dinámicos med columnas relacionales para claves de negocio. Tilføje indices og columnas calculadas (SQL-server) eller GIN/udtryk (PostgreSQL), aplica valideringer (tjek ISJSON, JSON-skema i appen) og plan eksportveje BI-modeller. Así tendrás agilidad de esquema sin perder control en informerer.

På en sikker måde, behandler JSON som en anden fornuftig dato: cifrado en tránsito y reposo, kontroller de acceso, auditoría y endurecimiento de sistemas. Para analítica, prepara ETL-udsigter, der er materialiseret que descompongan JSON en tablas fact/dim si la herramienta (por ejemplo Power BI) hvad der efterspørges. Este enfoque híbrido es el que mejor escala i blandede laster.

Un truco outil en T-SQL: genopretter arrays med markøren

Hvis der er behov for flere elementer af en array JSON, kan du samle linjer og bucles. OPENJSON med APPLY es mere 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 læselig, 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 JSON-samlinger.

For organizaciones que buscan ayuda de extremo a extremo, firmas especializadas como Q2BSTUDIO implementan patroner híbridos, despliegues cloud (AWS/Azure), ciberseguridad og automatisering med IA. La clave está en combinar el diseño de data med renderizado JSON en la base, rørledninger robuste og visualisering en suites BI, med acompañamiento en migraciones entre motores cuando hay funciones JSON specificas.

Integrar JSON og SQL består af elegir el tipo de almacenamiento correcto (oprindelig tekst) sabre usar las funciones de cada motor (T-SQL, GoogleSQL, operatører af Postgres, JSON_TABLE og Oracle), og se indeks og udsigter for at konsultere og API'er sean ágiles. Con estas piezas bien encajadas, brugere, konsulenter, transformerer og JSON-tjenester manteniendo gobierno, seguridad y rendimiento en toda la plataforma.