Memvalidasi, mengkueri, dan mengubah data JSON dengan fungsi bawaan (SQL Server)
Berlaku untuk: SQL Server 2016 (13.x) dan Azure SQL Database
Azure 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.
Untuk semua fungsi JSON, tinjau fungsi JSON.
Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022
database sampel atau AdventureWorksDW2022
, yang dapat Anda unduh dari halaman 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)
);
Fungsi JSON berfungsi sama apakah dokumen JSON disimpan dalam varchar, nvarchar, atau jenis data json asli.
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.
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.
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.
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.