OPENJSON (Transact-SQL)

Artikel
11/15/2023

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru Azure SQL Database Azure SQL Managed Instance Titik akhir analitik Azure Synapse Analytics SQL di Microsoft Fabric Warehouse 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

Konvensi sintaks transact-SQL

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

Sintaks untuk OPENJSON TVF

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

Sintaksis untuk klausa WITH di OPENJSON TVF

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

Number	Tanggal	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:
1. Kunci. Nilai nvarchar(4000) yang berisi nama properti yang ditentukan atau indeks elemen dalam array yang ditentukan. Kolom kunci memiliki kolatasi BIN2.
2. Nilai. Nilai nvarchar(max) yang berisi nilai properti . Kolom nilai mewarisi kolasenya dari jsonExpression.
3. 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.

Nilai kolom Jenis	Jenis data JSON
0	null
1	string
2	number
3	benar/salah
4	array
5	object

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
Pasar Makanan Utuh	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

Bagikan melalui

OPENJSON (Transact-SQL)

Sintaks

openjson

with_clause

Argumen

jsonExpression

jalan

Identitas elemen array

with_clause

Nilai hasil

Keterangan

Contoh

Contoh 1 - Mengonversi array JSON ke tabel sementara

Contoh 2 - Menggabungkan properti dari dua objek JSON

Contoh 3 - Gabungkan baris dengan data JSON yang disimpan dalam sel tabel menggunakan CROSS APPLY

Contoh 4 - Menggabungkan baris relasional dan elemen JSON dengan CROSS APPLY

Contoh 5 - Mengimpor data JSON ke SQL Server

Contoh 6 - Contoh sederhana dengan konten JSON

Lihat juga

Saran dan Komentar

Sumber Daya Tambahan: