Memproses data JSON dengan fungsi bawaan

Pertimbangkan skenario di mana aplikasi e-niaga Anda menyimpan preferensi pelanggan dan memesan metadata sebagai dokumen JSON. Aplikasi seluler mengirim data ke cart belanja dalam format JSON, dan sistem pelaporan Anda perlu mengekspor katalog produk sebagai JSON untuk API web. Bekerja langsung dengan JSON dalam database Anda menghilangkan kebutuhan akan transformasi lapisan aplikasi dan menjaga pemrosesan data Anda tetap efisien.

Database SQL Server, Azure SQL, dan SQL di Fabric menyediakan dukungan JSON bawaan yang memungkinkan Anda mengurai, mengkueri, membuat, dan mengubah data JSON langsung di T-SQL. Dalam unit ini, Anda mempelajari cara menggunakan fungsi JSON untuk mengekstrak nilai, membuat output JSON, menggabungkan data ke dalam array JSON, dan memvalidasi konten JSON.

Mengekstrak nilai dengan JSON_VALUE dan JSON_QUERY

Saat bekerja dengan JSON yang disimpan dalam database Anda, Anda perlu mengekstrak nilai tertentu untuk pemfilteran, gabungan, atau tampilan. SQL Server menyediakan dua fungsi untuk tujuan ini:

JSON_VALUE() mengekstrak nilai skalar (string, angka, boolean) dari string JSON:

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd",
        "active": true
    },
    "orderTotal": 1599.99
}';

SELECT 
    JSON_VALUE(@json, '$.customer.id') AS CustomerID,
    JSON_VALUE(@json, '$.customer.name') AS CustomerName,
    JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;

Kumpulan hasilnya akan menjadi:

CustomerID   CustomerName   OrderTotal
----------   ------------   ----------
12345        Contoso Ltd    1599.99

Fungsi menavigasi struktur JSON menggunakan ekspresi jalur dan mengembalikan nilai sebagai NVARCHAR(4000) string. Anda dapat melemparkan hasilnya ke jenis data lain sesuai kebutuhan untuk perhitungan atau perbandingan.

JSON_QUERY() mengekstrak objek atau array JSON (nilai bukan skalar):

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd"
    },
    "items": [
        {"product": "Widget", "qty": 5},
        {"product": "Gadget", "qty": 3}
    ]
}';

SELECT 
    JSON_QUERY(@json, '$.customer') AS CustomerObject,
    JSON_QUERY(@json, '$.items') AS ItemsArray;

Kumpulan hasilnya akan menjadi:

CustomerObject                          ItemsArray
--------------------------------------  ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"}     [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]

Tidak seperti JSON_VALUE(), JSON_QUERY() mempertahankan struktur JSON, mengembalikan objek dan array sebagai string JSON valid yang dapat Anda simpan, teruskan ke fungsi lain, atau kembali ke aplikasi.

Ekspresi jalur menggunakan $ untuk mewakili elemen akar, dengan notasi titik untuk properti berlapis dan notasi tanda kurung untuk elemen array, seperti dalam contoh berikut:

-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;

Hasilnya adalah:

FirstProduct
------------
Widget

Indeks array dimulai dari 0, jadi $.items[0] menunjukkan elemen pertama. Gunakan sintaks ini untuk mengekstrak item tertentu saat Anda mengetahui posisinya, atau gabungkan dengan OPENJSON saat Anda perlu memproses semua elemen array.

Mengurai array JSON dengan OPENJSON

OPENJSON adalah fungsi bernilai tabel yang mengubah data JSON menjadi kumpulan baris relasional. Gunakan fungsi ini untuk menggabungkan data JSON dengan tabel relasional atau memproses elemen array satu per satu.

Kueri berikut menguraikan array JSON ke dalam baris dengan skema default:

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "Widget", "price": 29.99},
    {"id": 2, "name": "Gadget", "price": 49.99},
    {"id": 3, "name": "Gizmo", "price": 19.99}
]';

SELECT * FROM OPENJSON(@json);

Kumpulan hasilnya akan menjadi:

key   value                                          type
---   --------------------------------------------   ----
0     {"id": 1, "name": "Widget", "price": 29.99}   5
1     {"id": 2, "name": "Gadget", "price": 49.99}   5
2     {"id": 3, "name": "Gizmo", "price": 19.99}    5

Tanpa skema, OPENJSON mengembalikan tiga kolom: key (indeks array atau nama properti), value (konten JSON), dan type (angka yang menunjukkan jenis data JSON: 0=null, 1=string, 2=angka, 3=boolean, 4=array, 5=object).

Kueri berikut menentukan skema eksplisit untuk mengekstrak kolom tertentu dengan jenis data yang tepat:

SELECT 
    ProductID,
    ProductName,
    Price
FROM OPENJSON(@json)
WITH (
    ProductID INT '$.id',
    ProductName NVARCHAR(100) '$.name',
    Price DECIMAL(10,2) '$.price'
);

Kumpulan hasilnya akan menjadi:

ProductID   ProductName   Price
---------   -----------   ------
1           Widget        29.99
2           Gadget        49.99
3           Gizmo         19.99

Klausa WITH memetakan properti JSON ke kolom yang ditik. Pendekatan ini memberi Anda jenis data yang tepat untuk perhitungan dan perbandingan, dan memungkinkan Anda memilih hanya properti yang Anda butuhkan.

Gabungkan OPENJSON dengan data tabel menggunakan CROSS APPLY:

-- Assuming Orders table has a JSON column called OrderDetails
SELECT 
    o.OrderID,
    o.CustomerID,
    items.ProductName,
    items.Quantity,
    items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
    ProductName NVARCHAR(100) '$.product',
    Quantity INT '$.qty',
    UnitPrice DECIMAL(10,2) '$.price'
) AS items;

Membangun JSON dengan JSON_OBJECT dan JSON_ARRAY

SQL Server 2022 diperkenalkan JSON_OBJECT dan JSON_ARRAY berfungsi untuk konstruksi JSON intuitif:

JSON_OBJECT() membuat objek JSON dari pasangan kunci-nilai. Contoh berikut menunjukkan cara membuat objek JSON untuk produk:

SELECT JSON_OBJECT(
    'id': ProductID,
    'name': Name,
    'price': ListPrice,
    'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;

Hasilnya adalah:

ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}

Fungsi ini secara otomatis menangani konversi jenis data dan pelepasan JSON yang tepat untuk karakter khusus dalam nilai string.

JSON_ARRAY() membuat array JSON dari nilai. Contoh berikut membangun array JSON:

SELECT JSON_ARRAY(
    'SQL Server',
    'Azure SQL Database',
    'SQL Database in Fabric'
) AS Platforms;

Hasilnya adalah:

Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]

Anda dapat meneruskan nilai kolom, variabel, atau nilai harfiah ke JSON_ARRAY(). Fungsi ini membuat array JSON yang diformat dengan benar terlepas dari jenis input.

Kemudian, gabungkan fungsi-fungsi ini untuk membangun struktur JSON berlapis. Contoh berikut membuat objek JSON pesanan lengkap dengan informasi pelanggan dan total:

SELECT JSON_OBJECT(
    'orderId': soh.SalesOrderID,
    'orderDate': soh.OrderDate,
    'customer': JSON_OBJECT(
        'id': c.CustomerID,
        'name': c.CompanyName
    ),
    'totals': JSON_OBJECT(
        'subtotal': soh.SubTotal,
        'tax': soh.TaxAmt,
        'total': soh.TotalDue
    )
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
    ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;

Hasilnya adalah:

OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}

Panggilan bertingkat JSON_OBJECT membentuk struktur hierarkis yang sesuai dengan format aplikasi Anda yang diharapkan. Pendekatan ini lebih bersih daripada penggabungan string dan memastikan output JSON yang valid.

Agregat data dengan JSON_ARRAYAGG

JSON_ARRAYAGG mengagregasi nilai dari beberapa baris ke dalam satu array JSON. Fungsi ini berguna untuk membuat output JSON denormalisasi dari data relasional yang dinormalisasi:

SELECT 
    c.CustomerID,
    c.CompanyName,
    JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

Hasilnya adalah:

CustomerID   CompanyName           OrderIds
----------   -------------------   ------------------
29825        Contoso Retail        [71774,71776,71780]
29847        Adventure Works       [71782,71784]

Fungsi ini mengumpulkan semua nilai yang cocok dari baris yang dikelompokkan dan menggabungkannya menjadi satu array JSON. Metode ini berguna untuk membuat respons API denormalisasi dari tabel database yang dinormalisasi.

Anda dapat menggabungkan JSON_ARRAYAGG dengan JSON_OBJECT untuk membuat array objek kompleks:

SELECT 
    pc.Name AS Category,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id': p.ProductID,
            'name': p.Name,
            'price': p.ListPrice
        )
    ) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
    ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;

Hasil berikut adalah:

Category        Products
--------------  --------------------------------------------------------------------------
Road Bikes      [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes  [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]

Memvalidasi dan memeriksa JSON dengan JSON_CONTAINS

Data JSON dari sumber eksternal dapat salah bentuk, properti yang diharapkan hilang, atau berisi nilai yang tidak terduga. Mencoba mengekstrak nilai dari JSON yang tidak valid atau jalur yang hilang dapat menyebabkan kegagalan kueri atau mengembalikan hasil menyesatkan yang menutupi NULL masalah data.

Pemrosesan JSON yang kuat memerlukan pengkodian defensif: memvalidasi bahwa JSON terbentuk dengan baik sebelum mengurainya. Kemudian periksa apakah jalur yang diharapkan ada sebelum mengekstrak nilai, dan verifikasi bahwa nilai sesuai dengan harapan Anda sebelum menggunakannya dalam logika bisnis. SQL Server menyediakan beberapa fungsi untuk membantu Anda memvalidasi konten JSON di setiap tahap pemrosesan.

Memahami mode jalur longgar vs ketat

Anda dapat menggunakan ekspresi jalur JSON dalam dua mode yang mengontrol penanganan kesalahan:

DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';

-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;

-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;

Hasilnya adalah:

LaxResult
---------
NULL

-- Strict mode raises: Property cannot be found on the specified JSON path.

Gunakan lax mode (default) ketika properti yang hilang diharapkan dan harus mengembalikan NULL. Gunakan strict mode saat properti yang hilang menunjukkan masalah data yang harus menimbulkan kesalahan.

ISJSON memvalidasi apakah string berisi JSON yang valid. Contoh berikut menunjukkan cara menggunakan ISJSON:

SELECT 
    ISJSON('{"name": "test"}') AS ValidJson,      -- Returns 1
    ISJSON('not valid json') AS InvalidJson,       -- Returns 0
    ISJSON(NULL) AS NullJson;                      -- Returns NULL

Hasilnya adalah:

ValidJson   InvalidJson   NullJson
---------   -----------   --------
1           0             NULL

Untuk menangani data yang tidak valid dengan baik, gunakan ISJSON pada klausa WHERE untuk menyaring baris dengan JSON yang valid, atau dalam ekspresi CASE.

JSON_PATH_EXISTS memeriksa apakah jalur tertentu ada dalam dokumen JSON, seperti contoh berikut:

DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';

SELECT 
    JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
    JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;

Hasilnya adalah:

HasName   HasEmail
-------   --------
1         0

Fungsi ini mengembalikan 1 jika jalur ada, 0 jika tidak. Gunakan sebelum memanggil JSON_VALUE dalam mode ketat, atau untuk memproses JSON secara kondisional dengan berbagai struktur.

Gunakan JSON_CONTAINS untuk memeriksa apakah dokumen JSON berisi nilai atau objek tertentu, seperti contoh berikut:

DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';

SELECT 
    JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
    JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;

Hasilnya adalah:

HasSqlTag   HasPythonTag
---------   ------------
1           0

Mengoptimalkan kueri JSON dengan kolom komputasi

Saat Anda sering mengkueri properti JSON tertentu, mesin database harus mengurai dokumen JSON untuk setiap baris pada setiap kueri. Untuk tabel dengan ribuan atau jutaan baris, penguraian berulang ini menciptakan overhead yang signifikan. Kolom komputasi memungkinkan Anda mengekstrak nilai JSON sekali dan menyimpannya dalam format yang dapat dikueri yang mendukung pengindeksan.

Mengapa penguraian JSON memengaruhi performa

Pertimbangkan tabel dengan 100.000 rekaman produk di mana setiap baris berisi dokumen JSON dengan atribut produk. Pemfilteran kueri menurut kategori harus:

1. Membaca setiap baris dari tabel
2. Untuk menemukan properti kategori, uraikan dokumen JSON
3. Mengekstrak dan membandingkan nilai

Tanpa pengoptimalan, bahkan filter sederhana memerlukan pemindaian tabel penuh dengan penguraian JSON pada setiap baris.

Membuat kolom komputasi untuk properti JSON

Kolom komputasi secara otomatis mengekstrak properti JSON dan membuatnya tersedia sebagai kolom biasa, seperti contoh berikut:

-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');

-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';

Hasilnya adalah:

ProductID   ProductName           ProductCategory
---------   -------------------   ---------------
101         Wireless Mouse        Electronics
102         USB Keyboard          Electronics
103         HD Monitor            Electronics

Secara default, kolom komputasi bersifat virtual. Database menghitung nilai pada waktu kueri tetapi dapat mengoptimalkan ekstraksi JSON. Untuk performa yang lebih baik, Anda dapat mempertahankan kolom komputasi seperti dalam contoh berikut:

-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;

Kolom yang persisten menyimpan nilai yang diekstrak pada disk, sehingga JSON hanya diurai selama operasi INSERT dan UPDATE, bukan selama kueri SELECT.

Menambahkan indeks untuk pemfilteran yang lebih cepat

Perolehan performa nyata berasal dari pengindeksan kolom komputasi:

-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);

-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';

Tanpa indeks, kueri memindai semua 100.000 baris. Dengan indeks, mesin kueri melakukan pencarian indeks dan hanya mengambil baris yang cocok. Indeks ini dapat mengurangi waktu kueri dari detik ke milidetik.

Mengindeks beberapa properti JSON

Untuk kueri yang memfilter beberapa properti JSON, buat kolom komputasi dan indeks komposit:

-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
    ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
    ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;

-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);

-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);

Sekarang kueri memfilter menurut kategori dan merek, atau mengurutkan berdasarkan harga, dapat menggunakan indeks ini secara efisien.

Mengubah data relasional ke JSON dengan FOR JSON

Untuk output JSON yang komprehensif dari kueri, gunakan FOR JSON PATH atau FOR JSON AUTO:

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');

Hasilnya adalah:

{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}

FOR JSON PATH memberi Anda kontrol atas struktur JSON melalui alias kolom. Gunakan notasi titik dalam alias untuk membuat objek berlapis:

SELECT 
    p.ProductID AS 'product.id',
    p.Name AS 'product.name',
    pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;

Hasilnya adalah:

[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]

Kolom alias 'product.id' membuat objek tertanam product dengan properti id. Teknik ini memungkinkan Anda membentuk output agar sesuai dengan format API yang diharapkan tanpa pasca-pemrosesan.

Untuk informasi selengkapnya tentang fungsi JSON di SQL Server, lihat Data JSON di Fungsi SQL Server dan JSON.