Memvalidasi, mengkueri, dan mengubah data JSON dengan fungsi bawaan (SQL Server)

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) dan Azure SQL DatabaseAzure SQL Managed Instance yang lebih baru

Dukungan bawaan untuk JSON mencakup fungsi bawaan berikut yang dijelaskan secara singkat dalam artikel ini.

  • ISJSON menguji apakah string berisi JSON yang valid.
  • JSON_VALUE mengekstrak nilai skalar dari string JSON.
  • JSON_QUERY mengekstrak objek atau array dari string JSON.
  • JSON_MODIFY memperbarui nilai properti dalam string JSON dan mengembalikan string JSON yang diperbarui.

Artikel ini memerlukan AdventureWorks2022 database sampel, yang dapat Anda unduh dari beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

Teks JSON untuk contoh di halaman ini

Contoh di halaman ini menggunakan teks JSON yang mirip dengan konten yang diperlihatkan dalam contoh berikut:

{
    "id": "DesaiFamily",
    "parents": [
        { "familyName": "Desai", "givenName": "Prashanth" },
        { "familyName": "Miller", "givenName": "Helen" }
    ],
    "children": [
        {
            "familyName": "Desai",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1,
            "pets": [
                { "givenName": "Goofy" },
                { "givenName": "Shadow" }
            ]
        },
        {
            "familyName": "Desai",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
    ],
    "address": {
        "state": "NY",
        "county": "Manhattan",
        "city": "NY"
    },
    "creationDate": 1431620462,
    "isRegistered": false
}

Dokumen JSON ini, yang berisi elemen kompleks berlapis, disimpan dalam tabel sampel berikut:

CREATE TABLE Families (
    id INT identity CONSTRAINT PK_JSON_ID PRIMARY KEY,
    doc NVARCHAR(MAX)
);

Memvalidasi teks JSON dengan menggunakan fungsi ISJSON

Fungsi menguji ISJSON apakah string berisi JSON yang valid.

Contoh berikut mengembalikan baris di mana kolom JSON berisi teks JSON yang valid. Tanpa batasan JSON eksplisit, Anda dapat memasukkan teks apa pun di kolom NVARCHAR:

SELECT *
FROM Families
WHERE ISJSON(doc) > 0;

Untuk informasi selengkapnya, lihat ISJSON (Transact-SQL).

Mengekstrak nilai dari teks JSON dengan menggunakan fungsi JSON_VALUE

Fungsi ini JSON_VALUE mengekstrak nilai skalar dari string JSON. Kueri berikut mengembalikan dokumen di mana id bidang JSON cocok dengan nilai DesaiFamily, diurutkan menurut city dan state bidang JSON:

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC,
    JSON_VALUE(f.doc, '$.address.state') ASC

Hasil kueri ini diperlihatkan dalam tabel berikut:

Nama Kota Wilayah
DesaiFamily NY Manhattan

Untuk informasi selengkapnya, lihat JSON_VALUE (Transact-SQL).

Mengekstrak objek atau array dari teks JSON dengan menggunakan fungsi JSON_QUERY

Fungsi ini JSON_QUERY mengekstrak objek atau array dari string JSON. Contoh berikut menunjukkan cara mengembalikan fragmen JSON dalam hasil kueri.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
    JSON_QUERY(f.doc, '$.parents') AS Parents,
    JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily';

Hasil kueri ini diperlihatkan dalam tabel berikut:

Alamat orang tua Parent0
{ "state": "NY", "county": "Manhattan", "city": "NY" } [ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ] { "familyName": "Desai", "givenName": "Prashanth" }

Untuk informasi selengkapnya, lihat JSON_QUERY (Transact-SQL).

Mengurai koleksi JSON berlapis

OPENJSON fungsi memungkinkan Anda mengubah subarray JSON menjadi set baris lalu menggabungkannya dengan elemen induk. Sebagai contoh, Anda dapat mengembalikan semua dokumen keluarga, dan "bergabung" dengan objek yang children disimpan sebagai array JSON dalam:

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    c.givenName,
    c.grade
FROM Families f
CROSS APPLY OPENJSON(f.doc, '$.children') WITH (
    grade INT,
    givenName NVARCHAR(100)
) c

Hasil kueri ini diperlihatkan dalam tabel berikut:

Nama Kota givenName nilai
DesaiFamily NY Jesse 1
DesaiFamily NY Lisa 8

Dua baris dikembalikan, karena satu baris induk digabungkan dengan dua baris anak yang dihasilkan dengan mengurai dua elemen subarray anak. OPENJSON fungsi mengurai children fragmen dari doc kolom dan mengembalikan grade dan givenName dari setiap elemen sebagai sekumpulan baris. Kumpulan baris ini dapat digabungkan dengan dokumen induk.

Kueri bertumpuk subarray JSON hierarkis

Anda dapat menerapkan beberapa CROSS APPLY OPENJSON panggilan untuk mengkueri struktur JSON berlapis. Dokumen JSON yang digunakan dalam contoh ini memiliki array berlapis yang disebut children, di mana setiap anak memiliki array berlapis .pets Kueri berikut mengurai turunan dari setiap dokumen, mengembalikan setiap objek array sebagai baris, lalu mengurai pets array:

SELECT c.familyName,
    c.givenName AS childGivenName,
    p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (
    familyName NVARCHAR(100),
    children NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY OPENJSON(children) WITH (
    familyName NVARCHAR(100),
    givenName NVARCHAR(100),
    pets NVARCHAR(max) AS JSON
) AS c
OUTER APPLY OPENJSON(pets) WITH (givenName NVARCHAR(100)) AS p;

Panggilan pertama OPENJSON mengembalikan fragmen children array menggunakan klausul AS JSON. Fragmen array ini disediakan untuk fungsi kedua OPENJSON yang mengembalikan givenName, firstName dari setiap anak, serta array .pets Array pets disediakan untuk fungsi ketiga OPENJSON yang mengembalikan givenName hewan peliharaan.

Hasil kueri ini diperlihatkan dalam tabel berikut:

familyName childGivenName petName
Desai Jesse Goofy
Desai Jesse Shadow
Desai Lisa NULL

Dokumen akar digabungkan dengan dua children baris yang dikembalikan dengan panggilan pertama OPENJSON(children) yang membuat dua baris (atau tuple). Kemudian setiap baris digabungkan dengan baris baru yang dihasilkan dengan OPENJSON(pets) menggunakan OUTER APPLY operator. Jesse memiliki dua hewan peliharaan, jadi (Desai, Jesse) bergabung dengan dua baris yang dihasilkan untuk Goofy dan Shadow. Lisa tidak memiliki hewan peliharaan, jadi tidak ada baris yang dikembalikan oleh OPENJSON(pets) untuk tuple ini. Namun, karena kita menggunakan OUTER APPLY, kita masuk NULL ke kolom . Jika kita menempatkan CROSS APPLY alih-alih OUTER APPLY, Lisa tidak akan dikembalikan dalam hasil karena tidak ada baris hewan peliharaan yang dapat digabungkan dengan tuple ini.

Membandingkan JSON_VALUE dan JSON_QUERY

Perbedaan utama antara JSON_VALUE dan adalah yang JSON_VALUE mengembalikan nilai skalar, sementara JSON_QUERY mengembalikan JSON_QUERY objek atau array.

Pertimbangkan contoh teks JSON berikut.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}

Dalam contoh teks JSON ini, anggota data "a" dan "c" adalah nilai string, sementara anggota data "b" adalah array. JSON_VALUE dan JSON_QUERY kembalikan hasil berikut:

Jalur menghasilkan JSON_VALUE menghasilkan JSON_QUERY
$ NULL atau kesalahan { "a": "[1,2]", "b": [1, 2], "c": "hi" }
$.a [1,2] NULL atau kesalahan
$.b NULL atau kesalahan [1,2]
$.b[0] 1 NULL atau kesalahan
$.c hi NULL atau kesalahan

Menguji JSON_VALUE dan JSON_QUERY dengan database sampel AdventureWorks

Uji fungsi bawaan yang dijelaskan dalam artikel ini dengan menjalankan contoh berikut dengan AdventureWorks2022 database sampel. Untuk informasi selengkapnya tentang cara menambahkan data JSON untuk pengujian dengan menjalankan skrip, lihat Dukungan JSON bawaan uji coba.

Dalam contoh berikut, Info kolom dalam SalesOrder_json tabel berisi teks JSON.

Contoh 1 - Mengembalikan kolom standar dan data JSON

Kueri berikut mengembalikan nilai dari kolom relasional standar dan dari kolom JSON.

SELECT SalesOrderNumber,
    OrderDate,
    Status,
    ShipDate,
    AccountNumber,
    TotalDue,
    JSON_QUERY(Info, '$.ShippingInfo') ShippingInfo,
    JSON_QUERY(Info, '$.BillingInfo') BillingInfo,
    JSON_VALUE(Info, '$.SalesPerson.Name') SalesPerson,
    JSON_VALUE(Info, '$.ShippingInfo.City') City,
    JSON_VALUE(Info, '$.Customer.Name') Customer,
    JSON_QUERY(OrderItems, '$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0;

Contoh 2- Agregat dan filter nilai JSON

Kueri berikut menggabungkan subtotal menurut nama pelanggan (disimpan dalam JSON) dan status (disimpan dalam kolom biasa). Kemudian memfilter hasil menurut kota (disimpan di JSON) dan OrderDate (disimpan dalam kolom biasa).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid = 3;
SET @city = N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer,
    Status,
    SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID = @territoryid
    AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
    AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'),
    Status
HAVING SUM(SubTotal) > 1000;

Memperbarui nilai properti dalam teks JSON dengan menggunakan fungsi JSON_MODIFY

Fungsi memperbarui JSON_MODIFY nilai properti dalam string JSON dan mengembalikan string JSON yang diperbarui.

Contoh berikut memperbarui nilai properti JSON dalam variabel yang berisi JSON.

SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London');

Untuk informasi selengkapnya, lihat JSON_MODIFY (Transact-SQL).

Konten terkait