Proses JSON dalam SQL: fungsi, konsultasi, dan kinerja

Beranda » Ular sanca » Proses JSON dalam SQL: fungsi, konsultasi, dan kinerja

Bekerja dengan data semi-struktural ya bukan pilihan: JSON diubah menjadi istilah umum untuk API, aplikasi seluler, dan registrasi, dan mesin SQL modern merespons dengan fungsi lokal dan pelindung perangkat untuk terintegrasi tanpa gangguan. Dalam panduan praktis ini untuk menemukan cara berkonsultasi, mengubah, dan menggunakan cara yang efisien SQL Server, Azure SQL, PostgreSQL, Oracle y Pertanyaan Besar, dengan contoh komentar dan rekomendasi penampilan.

Semua demo lainnya, tujuannya adalah: jelaskan apa yang harus dilakukan di setiap platform, karena perbedaannya (sebagai contoh, operator, tipe nativos dan limites), dan apa indeks atau format yang dapat digunakan untuk itu las consultas JSON escalenAnda juga akan melihat patrones de importación dari file dan layanan, dan bagaimana menghentikan migrasi ke mesin ketika ada fungsi JSON khusus dari setiap pengirim.

Itu adalah JSON di dunia SQL dan tidak ada aplikasinya

JSON adalah format teks populer untuk pertukaran data yang sangat baik dalam skenario web, seluler, dan layanan mikro. SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics dan SQL Database di Microsoft Fabric menggabungkan fungsi untuk menganalisis, berkonsultasi, dan memformat JSON secara langsung di T‑SQL. Contoh dokumen tipikal: [ { \”nama\”: \”John\”, \”keahlian\”: [ \”SQL\”, \”C#\”, \”Azure\” ] }, { \”nama\”: \”Jane\”, \”nama belakang\”: \”Doe\” } ].

Con estas funciones puedes leer valores escalares, extraer objetos o arrays, modificar propiedades tanpa menulis ulang semua dokumen, aplanar colecciones en filas y devolver resultados como JSON listo para tu API. Además, jika digabungkan tanpa masalah dengan kolom relasional, kumpulan ventilasi dan klausa WHERE/ORDER BY/GROUP BY.

Pembaruan dan kompatibilitas: teksnya tidak sama dengan jenis JSON nativo

Sejarahnya di SQL Server, JSON ada di dalamnya nvarchar/varchar dan itu adalah proses yang berfungsi secara lokal. SQL Server 2025 (17.x) perkenalkan en versi awal suatu tipo de datos JSON nativo yang menjaga dokumen dalam format binario analizado para ceramah, penulisan, dan kompresi lebih efisien. Ini meyakinkan dengan model klasik: fungsi ISJSON/JSON_VALUE/JSON_QUERY/JSON_MODIFY dan OPENJSON lalu lihat melalui rekomendasi untuk berkonsultasi dan mengubah konten.

Menyampaikan mati yang kompatibel: dalam dokumentasi sebelumnya yang sebenarnya “JSON no es un tipo integrado” en ciertas ediciones; pengenalan jenis negara ini lebih lambat dan Anda tidak dapat memberikan bintang ini di semua lingkungan. Oleh karena itu, memenuhi syarat nvarchar(max) atau jenis JSON baru yang berfungsi pada platform, dan menjaga akses melalui fungsi JSON untuk mengamankan portabilitas dan kinerja.

Fungsi-fungsi penting JSON di SQL Server

Inti dari dukungan T-SQL dapat digunakan secara langsung: ISJSON (valid), Nilai_JSON (ekstra eskalare), JSON_QUERY (devuelve objetos/arrays) y JSON_UBAH (mengaktualisasikan rute). Fungsi ini memungkinkan pemfilteran, pengurutan, dan pengumpulan untuk nilai semua dokumen dalam dokumen JSON tanpa perlu menyimpan data dasar.

Contoh mezcla relacional+JSON dengan filter dan urutan dengan rutas JSON: pilih Kampus Orang dan kode pos tambahan, arahan, dan keterampilan.

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

Untuk mewujudkan suatu propiedad beton tanpa menulis ulang dokumen: JSON_MODIFY melokalisasi rute dan mempertahankan nilai.

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

Untuk mengumpulkan JSON dan berkonsultasi dengan SELECT, OPENJSON mengonversi array ke objek dalam file/kolom dan izinkan tipado menjelaskan dengan klausa DENGAN. Di antara kami adalah: convierte al tipo indicado, soporta jerarquías y ubah NULL jika gagal; además puedes marcar rutas como ketat 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'
);

Jika dokumen tersebut menyertakan subarray (sebagai contoh, keterampilan dentro de info), encadena lainnya BUKAJSON dengan APPLY untuk memperluas dan membuka akun registrasi. OUTER APPLY mengulangi fila dari entitas dengan setiap elemen subarray (sampai para menginformasikan normalisasi 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) '$');

Alternatif yang sangat praktis ketika Anda hanya ingin validasi dan ekstraksi lebih lanjut JSON_VALUE untuk meningkatkan indeks dan ekspresi. Jadi Anda mendapatkan hasil penyaringan yang sering.

Tablas ke JSON: UNTUK JSON dan kumpulan nativos

Ketika aplikasi atau API Anda memerlukan JSON, UNTUK JSON mendelegasikan format dan motor. Menipu PATH puedes anidar usando alias con puntos; AUTO turunkan struktur setelah tablas/gabungan. Ideal untuk OData, AJAX, atau frontend dalam 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 untuk membangun struktur dari grup: JSON_OBJECTAGG (objetos) dan JSON_ARRAYAGG (array). Menyederhanakan pembuatan agregat payload sin salir de SQL.

Penyimpanan, pengindeksan, dan pemulihan di SQL Server

Untuk menyelesaikan dokumen, opsi universal adalah nvarchar(maks) (o json nativo cuando esté diponible). Validasi dengan CHECK(ISJSON(col)=1) jika perlu memastikan integritas format, y exponte campos clave con kolom perhitungan untuk mengindeks propiedades usadas en busquedas.

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

Untuk konten analitis dan arsip besar, sebuah grup Columnstore telah dikumpulkan dalam pengumpulan port kompresi dan pemindaian cepat. Jika Anda mendominasi penulisan/aktualisasi de documentos, considera tablas optimizadas untuk memoria (dengan salvedad de tipos soportados) y prosedur kompilasi nativamente untuk operasi aktualisasi dengan 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, desnormalisasi variabel dalam JSON dan simpan relasional untuk berkonsultasi dengan menu. En informes, usa BUKAJSON dengan DENGAN untuk menyiapkan kumpulan data berbentuk persegi panjang, dan seterusnya índices sobre JSON_VALUE() jika rutenya digunakan dalam filter/urutan.

Pengangkutan dan impor dari arsip dan layanan

Jika Anda menerima JSON dari file atau titik akhir REST, Anda bisa isi semuanya dan parsearlo dalam SQL. Dengan mengakses arsip lokal atau partisi, motor dapat diterima operasi massal; untuk orígenes didistribusikan seperti Hadoop atau Azure Blob, PolyBase izinkan memasukkan teks JSON dan memetakannya terlebih dahulu OPENJSON/JSON_VALUE dalam 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 asli ini tersedia di platform Anda, disetujui oleh efisiensi E/S dan kompresi; jika tidak, nvarchar(4000) ofrece ventajas de rendimiento Frente a nvarchar(max) if sabes que los documentos no superan 8 KB.

Casos de uso dan model hibrido

Kandidat utama untuk JSON dan SQL termasuk model data dengan atribut variabel (perdagangan elektronik), telemetría y logs, IoT semiestructurado dengan menganalisis kasus ini pada waktu yang sebenarnya eksposisi REST desde SQL a frontends. Rekomendasi umum hal ini tidak diperlukan, jaga agar tetap berkonsultasi seperti kolom peningkatan dan indexar rutas kritik.

Dalam skenario keamanan atau internasionalisasi, JSON se comporta como texto dan manfaat dari semua karakteristik motor (tablas temporales, seguridad a nivel de fila, dll.). Ini adalah fasilitas seragam politik dan menghindari pengecualian dalam campuran saluran pipa.

BigQuery: jenis JSON asli dan berkonsultasi dengan GoogleSQL

BigQuery ofrece un tipo de datos JSON nativo con acceso a campos por operador de punto dan subíndice. Anda dapat menciptakan nilai-nilai JSON dengan literal JSON, PARSE_JSON (mengubah STRING menjadi JSON), KE_JSON (mengonversi tip SQL ke JSON), dan membangun array dengan 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 ekstrak LARANGAN dan UNNEST dapat diperluas ke filas. Jika perlu untuk beralih ke pemadatan, AS ARRAY_AGG. Untuk perbandingan/perintah, Nilai_JSON meningkatkan skala seperti STRING SQL, y fungsi LAX_* (misalnya 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

Pentingnya Nilai Nulos: el null dari JSON bukanlah NULL dari SQL. JSON_QUERY puede devolver null JSON; Nilai_JSON kembangkan NULL SQL jika tidak ada eskalasi. Tenlo en cuenta en filtros y agregaciones.

Limitaciones y carga: profundidad máxima 500 de anidación; tidak dapat berpartisipasi atau mengelompokkan kolom JSON (tidak ada pembanding yang pasti). Untuk Ingerir, AS CSV o JSONL en jobs de carga, API Penulisan Penyimpanan para streaming moderno o la API de transmisión heredada jika kamu melanjutkan; dan semuanya, respeta el escapado ketika JSON viaja seperti STRING.

Oracle dan PostgreSQL: kesetaraan dan migrasi

Di Oracle (12.2+), JSON_TABEL mengubah dokumen menjadi file con PATH oleh kolom dan cara melakukan kesalahan. El reto al migrar yaitu AWS SCT tidak secara otomatis mengonversi fungsi JSON dari Oracle ke PostgreSQL, karena itu adalah istilah yang digunakan operator -> y ->> lebih banyak fungsi agregat JSON.

Di PostgreSQL, -> devuelve JSON y - >> devuelve texto, lo que facilita CAST explícito y gunakan bergabung/agrupaciones. Untuk membangun struktur, contohnya JSON_BUILD_OBJECT y JSON_AGG (setara dengan JSON_OBJECT dan JSON_ARRAYAGG dari Oracle). Recuerda castear jika Anda memerlukan nomor atau jumlah yang berbeda untuk perbandingan.

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

Praktik praktis dalam konversi: simulasi JSON_TABLE dengan elemen_array_json untuk memperluas array, gunakan ->> ketika Anda ingin menggunakan castear y garantiza tipos homogéneos al agrupar o unir. Esto te evita sorpresas de colaciones/ordenaciones dan memaksimalkan kinerja dengan indeks yang berfungsi atau GIN seperti itu.

Pelindung pelaksanaan, keamanan dan BI

Para arquitecturas empresariales, gabungan dokumen JSON untuk atribut dinamis dengan kolom relasional untuk perdagangan. Menambahkan indeks dalam kolom perhitungan (SQL Server) o GIN/ekspresi (PostgreSQL), aplica validasi (PERIKSA ISJSON, Skema JSON di aplikasi) dan rencana vías de exportación a modelos de BI. Ini cenderung agilidad de esquema tanpa kontrol dan informasi.

Dengan aman, JSON seperti apa pun data lain yang masuk akal: cifrado en transit dan reposo, kontrol akses, auditoria dan bertahan pada sistem. Untuk analitis, persiapan vistas materializadas o ETL yang menguraikan JSON dalam tab fakta/redup jika ada (sebagai contoh Power BI) lo demanda. Ini adalah panggilan hibrid yang paling penting en cargas mixtas.

Kegunaan yang sebenarnya dalam T‑SQL: array perekam tanpa kursor

Jika Anda perlu mengulangi elemen dari array JSON, maka tidak akan ada rangkaian ruas di dalamnya. OPENJSON dengan APPLY sangat cepat, cepat, dan aman. Patron tipikal untuk prosedur yang memasukkan elemen array ke dalam tabel berikut:

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 lebih mudah dibaca, ini enfoque menghindari kesalahan pada indikator dan masalah pada kinerja asociados a WHILE/kursor, y es el recomendado por el propio motor cuando trabajas con koleksi JSON.

Bagi organisasi yang mencari pekerjaan ekstrem dan ekstrem, perusahaan khusus seperti pelaksana Q2BSTUDIO patrones hibridos, despliegues cloud (AWS/Azure), ciberseguridad dan automatización con IA. Clave adalah kombinasi dari perangkat data dengan merender JSON di pangkalan, pipeline yang kuat, dan visualisasi di suite BI, dengan menyertai migrasi ke seluruh mesin ketika ada fungsi khusus JSON.

Integrasikan JSON dan SQL terdiri dari pilih jenis almacenamiento yang benar (nativo o texto), saber use las funciones de cada motor (T‑SQL, GoogleSQL, operator Postgres, JSON_TABLE dan Oracle), dan apoyarse en índices y vistas untuk konsultasi dan API yang cepat. Dengan piezas bien encajadas, dapat dilakukan, konsultasi, transformasi, dan layanan JSON dalam skala besar manteniendo gobierno, seguridad dan rendimiento en toda la plataforma.