OPENJSON (Transact-SQL)
Berlaku untuk:
SQL Server 2016 (13.x) dan yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceTitik akhir analitik Azure Synapse AnalyticsSQL di Microsoft FabricWarehouse di Microsoft Fabric
OPENJSON adalah fungsi bernilai tabel yang mengurai teks JSON dan mengembalikan objek dan properti dari input JSON sebagai baris dan kolom. Dengan kata lain, OPENJSON menyediakan tampilan kumpulan baris melalui dokumen JSON. Anda dapat secara eksplisit menentukan kolom dalam set baris dan jalur properti JSON yang digunakan untuk mengisi kolom. Karena OPENJSON mengembalikan sekumpulan baris, Anda dapat menggunakan OPENJSON dalam FROM klausul pernyataan Transact-SQL sama seperti Anda dapat menggunakan fungsi tabel, tampilan, atau nilai tabel lainnya.
Gunakan OPENJSON untuk mengimpor data JSON ke SQL Server, atau untuk mengonversi data JSON ke format relasional untuk aplikasi atau layanan yang tidak dapat menggunakan JSON secara langsung.
Catatan
Fungsi OPENJSON hanya tersedia di bawah tingkat kompatibilitas 130 atau lebih tinggi. Jika tingkat kompatibilitas database Anda lebih rendah dari 130, SQL Server tidak dapat menemukan dan menjalankan fungsi OPENJSON . Fungsi JSON lainnya tersedia di semua tingkat kompatibilitas.
Anda dapat memeriksa tingkat kompatibilitas dalam sys.databases tampilan atau di properti database. Anda bisa mengubah tingkat kompatibilitas database dengan perintah berikut:
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130
Sintaks
OPENJSON( jsonExpression [ , path ] ) [ <with_clause> ]
<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )
Fungsi bernilai tabel OPENJSON mengurai jsonExpression yang disediakan sebagai argumen pertama dan mengembalikan satu atau beberapa baris yang berisi data dari objek JSON dalam ekspresi. jsonExpression dapat berisi sub-objek berlapis. Jika Anda ingin mengurai sub-objek dari dalam jsonExpression, Anda dapat menentukan parameter jalur untuk sub-objek JSON.
openjson
Secara default, fungsi bernilai tabel OPENJSON mengembalikan tiga kolom, yang berisi nama kunci, nilai, dan jenis setiap pasangan {key:value} yang ditemukan di jsonExpression. Sebagai alternatif, Anda dapat secara eksplisit menentukan skema kumpulan hasil yang dikembalikan OPENJSON dengan menyediakan with_clause.
with_clause
with_clause berisi daftar kolom dengan jenisnya agar OPENJSON kembali. Secara default, OPENJSON cocok dengan kunci di jsonExpression dengan nama kolom di with_clause (dalam hal ini, cocok dengan kunci yang menyiratkan bahwa itu peka huruf besar/kecil). Jika nama kolom tidak cocok dengan nama kunci, Anda dapat memberikan column_path opsional, yang merupakan Ekspresi Jalur JSON yang mereferensikan kunci dalam jsonExpression.
Argumen
jsonExpression
Adalah ekspresi karakter Unicode yang berisi teks JSON.
OPENJSON mengulangi elemen array atau properti objek dalam ekspresi JSON dan mengembalikan satu baris untuk setiap elemen atau properti. Contoh berikut mengembalikan setiap properti objek yang disediakan sebagai jsonExpression:
DECLARE @json NVARCHAR(2048) = N'{
"String_value": "John",
"DoublePrecisionFloatingPoint_value": 45,
"DoublePrecisionFloatingPoint_value": 2.3456,
"BooleanTrue_value": true,
"BooleanFalse_value": false,
"Null_value": null,
"Array_value": ["a","r","r","a","y"],
"Object_value": {"obj":"ect"}
}';
SELECT * FROM OpenJson(@json);
Hasil:
| kunci | value | jenis |
|---|---|---|
| String_value | John | 1 |
| DoublePrecisionFloatingPoint_value | 45 | 2 |
| DoublePrecisionFloatingPoint_value | 2.3456 | 2 |
| BooleanTrue_value | benar | 3 |
| BooleanFalse_value | salah | 3 |
| Null_value | NULL | 0 |
| Array_value | ["a","r","r","a","y"] | 4 |
| Object_value | {"obj":"ect"} | 5 |
- DoublePrecisionFloatingPoint_value sesuai dengan IEEE-754.
jalan
Adalah ekspresi jalur JSON opsional yang mereferensikan objek atau array dalam jsonExpression. OPENJSON mencari ke dalam teks JSON pada posisi yang ditentukan dan hanya mengurai fragmen yang dirujuk. Untuk informasi selengkapnya, lihat Ekspresi Jalur JSON (SQL Server).
Di SQL Server 2017 (14.x) dan di Azure SQL Database, Anda dapat memberikan variabel sebagai nilai jalur.
Contoh berikut mengembalikan objek berlapis dengan menentukan jalur:
DECLARE @json NVARCHAR(4000) = N'{
"path": {
"to":{
"sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]
}
}
}';
SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')
Hasil
| Tombol | Nilai |
|---|---|
| 0 | en-GB |
| 1 | en-UK |
| 2 | de-AT |
| 3 | es-AR |
| 4 | sr-Cyrl |
Saat OPENJSON menguraikan array JSON, fungsi mengembalikan indeks elemen dalam teks JSON sebagai kunci.
Perbandingan yang digunakan untuk mencocokkan langkah jalur dengan properti ekspresi JSON peka huruf besar/kecil dan tidak sadar kolas (yaitu, perbandingan BIN2).
Identitas elemen array
OPENJSON fungsi di kumpulan SQL tanpa server di Azure Synapse Analytics dapat secara otomatis menghasilkan identitas setiap baris yang dikembalikan sebagai hasilnya. Kolom identitas ditentukan menggunakan ekspresi $.sql:identity() di jalur JSON setelah definisi kolom. Kolom dengan nilai ini dalam ekspresi jalur JSON akan menghasilkan angka berbasis 0 unik untuk setiap elemen dalam array JSON yang diurai fungsi. Nilai identitas mewakili posisi/indeks elemen array.
DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
{"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
]';
SELECT * FROM OPENJSON(@array)
WITH ( month VARCHAR(3),
temp int,
month_id tinyint '$.sql:identity()') as months
Hasil
| month | temp | month_id |
|---|---|---|
| Jan | 10 | 0 |
| Feb | 12 | 1 |
| Mar | 15 | 2 |
| Apr | 17 | 3 |
| Mei | 23 | 4 |
| Jun | 27 | 5 |
Identitas hanya tersedia di kumpulan SQL tanpa server di Synapse Analytics.
with_clause
Secara eksplisit mendefinisikan skema output untuk fungsi OPENJSON yang akan dikembalikan. With_clause opsional dapat berisi elemen berikut:
colName Adalah nama untuk kolom output.
Secara default, OPENJSON menggunakan nama kolom untuk mencocokkan properti dalam teks JSON. Misalnya, jika Anda menentukan nama kolom dalam skema, OPENJSON mencoba mengisi kolom ini dengan properti "nama" dalam teks JSON. Anda dapat mengambil alih pemetaan default ini dengan menggunakan argumen column_path .
jenis
Adalah jenis data untuk kolom output.
Catatan
Jika Anda juga menggunakan opsi AS JSON, jenis kolom harus NVARCHAR(MAX).
column_path
Adalah jalur JSON yang menentukan properti untuk dikembalikan di kolom yang ditentukan. Untuk informasi selengkapnya, lihat deskripsi parameter jalur sebelumnya dalam topik ini.
Gunakan column_path untuk mengambil alih aturan pemetaan default saat nama kolom output tidak cocok dengan nama properti.
Perbandingan yang digunakan untuk mencocokkan langkah jalur dengan properti ekspresi JSON peka huruf besar/kecil dan tidak sadar kolas (yaitu, perbandingan BIN2).
Untuk informasi selengkapnya tentang jalur, lihat Ekspresi Jalur JSON (SQL Server).
SEBAGAI JSON
Gunakan opsi AS JSON dalam definisi kolom untuk menentukan bahwa properti yang dirujuk berisi objek atau array JSON dalam. Jika Anda menentukan opsi AS JSON , jenis kolom harus NVARCHAR(MAX).
Jika Anda tidak menentukan AS JSON untuk kolom, fungsi mengembalikan nilai skalar (misalnya, int, string, true, false) dari properti JSON yang ditentukan pada jalur yang ditentukan. Jika jalur mewakili objek atau array, dan properti tidak dapat ditemukan di jalur yang ditentukan, fungsi mengembalikan null dalam mode lax atau mengembalikan kesalahan dalam mode ketat. Perilaku ini mirip dengan perilaku fungsi JSON_VALUE .
Jika Anda menentukan AS JSON untuk kolom, fungsi mengembalikan fragmen JSON dari properti JSON yang ditentukan pada jalur yang ditentukan. Jika jalur mewakili nilai skalar, dan properti tidak dapat ditemukan di jalur yang ditentukan, fungsi mengembalikan null dalam mode laks atau mengembalikan kesalahan dalam mode ketat. Perilaku ini mirip dengan perilaku fungsi JSON_QUERY .
Catatan
Jika Anda ingin mengembalikan fragmen JSON berlapis dari properti JSON, Anda harus memberikan bendera JSON AS. Tanpa opsi ini, jika properti tidak dapat ditemukan, OPENJSON mengembalikan nilai NULL alih-alih objek atau array JSON yang dirujuk, atau mengembalikan kesalahan run-time dalam mode ketat.
Misalnya, kueri berikut mengembalikan dan memformat elemen array:
DECLARE @json 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 *
FROM OPENJSON ( @json )
WITH (
Number VARCHAR(200) '$.Order.Number',
Date DATETIME '$.Order.Date',
Customer VARCHAR(200) '$.AccountNumber',
Quantity INT '$.Item.Quantity',
[Order] NVARCHAR(MAX) AS JSON
)
Hasil
| Angka | Date | Pelanggan | Quantity | Pesanan |
|---|---|---|---|---|
| SO43659 | 2011-05-31T00:00:00 | AW29825 | 1 | {"Number":"SO43659","Date":"2011-05-31T00:00:00"} |
| SO43661 | 2011-06-01T00:00:00 | AW73565 | 3 | {"Number":"SO43661","Date":"2011-06-01T00:00:00"} |
Nilai hasil
Kolom yang dikembalikan fungsi OPENJSON bergantung pada opsi WITH.
Saat Anda memanggil OPENJSON dengan skema default - yaitu, saat Anda tidak menentukan skema eksplisit dalam klausa WITH - fungsi mengembalikan tabel dengan kolom berikut:
Kunci. Nilai nvarchar(4000) yang berisi nama properti yang ditentukan atau indeks elemen dalam array yang ditentukan. Kolom kunci memiliki kolatasi BIN2.
Nilai. Nilai nvarchar(max) yang berisi nilai properti . Kolom nilai mewarisi kolasenya dari jsonExpression.
Jenis. Nilai int yang berisi jenis nilai . Kolom Jenis dikembalikan hanya saat Anda menggunakan OPENJSON dengan skema default. Kolom jenis memiliki salah satu nilai berikut:
Nilai kolom Jenis Jenis data JSON 0 null 1 string 2 number 3 benar/salah 4 array 5 object
Hanya properti tingkat pertama yang dikembalikan. Pernyataan gagal jika teks JSON tidak diformat dengan benar.
Saat Anda memanggil OPENJSON dan Anda menentukan skema eksplisit dalam klausul WITH, fungsi mengembalikan tabel dengan skema yang Anda tentukan dalam klausul WITH.
Catatan
Kolom Kunci, Nilai, dan Jenis hanya dikembalikan saat Anda menggunakan OPENJSON dengan skema default dan tidak tersedia dengan skema eksplisit.
Keterangan
json_path digunakan dalam argumen kedua OPENJSON atau dalam with_clause dapat dimulai dengan kata kunci laks atau ketat.
- Dalam mode laks , OPENJSON tidak memunculkan kesalahan jika objek atau nilai pada jalur yang ditentukan tidak dapat ditemukan. Jika jalur tidak dapat ditemukan, OPENJSON mengembalikan tataan hasil kosong atau nilai NULL.
- Secara ketat, mode OPENJSON mengembalikan kesalahan jika jalur tidak dapat ditemukan.
Beberapa contoh di halaman ini secara eksplisit menentukan mode jalur, laks, atau ketat. Mode jalur bersifat opsional. Jika Anda tidak secara eksplisit menentukan mode jalur, mode lax adalah default. Untuk informasi selengkapnya tentang mode jalur dan ekspresi jalur, lihat Ekspresi Jalur JSON (SQL Server).
Nama kolom di with_clause dicocokkan dengan kunci dalam teks JSON. Jika Anda menentukan nama kolom , itu [Address.Country]cocok dengan kunci Address.Country. Jika Anda ingin mereferensikan kunci Country berlapis dalam objek Address, Anda harus menentukan jalur $.Address.Country di jalur kolom.
json_path mungkin berisi kunci dengan karakter alfanumerik. Loloskan nama kunci di json_path dengan tanda kutip ganda jika Anda memiliki karakter khusus di kunci. Misalnya, $."my key $1".regularKey."key with . dot" cocok dengan nilai 1 dalam teks JSON berikut:
{
"my key $1": {
"regularKey":{
"key with . dot": 1
}
}
}
Contoh
Contoh 1 - Mengonversi array JSON ke tabel sementara
Contoh berikut menyediakan daftar pengidentifikasi sebagai array angka JSON. Kueri mengonversi array JSON menjadi tabel pengidentifikasi dan memfilter semua produk dengan ID yang ditentukan.
DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'
SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
ON product.productTypeID = productTypes.value
Kueri ini setara dengan contoh berikut. Namun, dalam contoh di bawah ini, Anda harus menyematkan angka dalam kueri alih-alih meneruskannya sebagai parameter.
SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)
Contoh 2 - Menggabungkan properti dari dua objek JSON
Contoh berikut memilih gabungan semua properti dari dua objek JSON. Dua objek memiliki properti nama duplikat. Contoh menggunakan nilai kunci untuk mengecualikan baris duplikat dari hasil.
DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)
SET @json1=N'{"name": "John", "surname":"Doe"}'
SET @json2=N'{"name": "John", "age":45}'
SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))
Contoh 3 - Gabungkan baris dengan data JSON yang disimpan dalam sel tabel menggunakan CROSS APPLY
Dalam contoh berikut, SalesOrderHeader tabel memiliki SalesReason kolom teks yang berisi array SalesOrderReasons dalam format JSON. Objek SalesOrderReasons berisi properti seperti Kualitas dan Produsen. Contoh membuat laporan yang menggabungkan setiap baris pesanan penjualan ke alasan penjualan terkait. Operator OPENJSON memperluas array JSON alasan penjualan seolah-olah alasan disimpan dalam tabel anak terpisah. Kemudian operator CROSS APPLY menggabungkan setiap baris pesanan penjualan ke baris yang dikembalikan oleh fungsi bernilai tabel OPENJSON.
SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)
Tip
Ketika Anda harus memperluas array JSON yang disimpan di bidang individual dan menggabungkannya dengan baris induknya, Anda biasanya menggunakan operator TRANSACT-SQL CROSS APPLY. Untuk informasi selengkapnya tentang CROSS APPLY, lihat FROM (Transact-SQL).
Kueri yang sama dapat ditulis ulang dengan menggunakan OPENJSON dengan skema baris yang ditentukan secara eksplisit untuk dikembalikan:
SELECT SalesOrderID, OrderDate, value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')
Dalam contoh ini, jalur mereferensikan $ setiap elemen dalam array. Jika Anda ingin secara eksplisit mentransmisikan nilai yang dikembalikan, Anda bisa menggunakan jenis kueri ini.
Contoh 4 - Menggabungkan baris relasional dan elemen JSON dengan CROSS APPLY
Kueri berikut menggabungkan baris relasional dan elemen JSON ke dalam hasil yang diperlihatkan dalam tabel berikut.
SELECT store.title, location.street, location.lat, location.long
FROM store
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')
WITH (street VARCHAR(500) , postcode VARCHAR(500) '$.postcode' ,
lon int '$.geo.longitude', lat int '$.geo.latitude')
AS location
Hasil
| title | jalan | Kodepos | lon | lat |
|---|---|---|---|---|
| Siapa le Food Markets | 17991 Redmond Way | WA 98052 | 47.666124 | -122.10155 |
| Sears | NE Ave ke-148 | WA 98052 | 47.63024 | -122.141246,17 |
Contoh 5 - Mengimpor data JSON ke SQL Server
Contoh berikut memuat seluruh objek JSON ke dalam tabel SQL Server.
DECLARE @json NVARCHAR(max) = N'{
"id" : 2,
"firstName": "John",
"lastName": "Smith",
"isAlive": true,
"age": 25,
"dateOfBirth": "2015-03-25T12:00:00",
"spouse": null
}';
INSERT INTO Person
SELECT *
FROM OPENJSON(@json)
WITH (id INT,
firstName NVARCHAR(50), lastName NVARCHAR(50),
isAlive BIT, age INT,
dateOfBirth DATETIME, spouse NVARCHAR(50))
Contoh 6 - Contoh sederhana dengan konten JSON
--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'
SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues
Baca juga
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk