Bagikan melalui

OPENJSON (Transact-SQL)

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceTitik akhir analitik SQLAzure Synapse Analytics di Microsoft Fabric Warehouse dalam database Microsoft FabricSQL di Microsoft Fabric

Fungsi OPENJSON bernilai tabel mengurai teks JSON dan mengembalikan objek dan properti dari input JSON sebagai baris dan kolom. Dengan kata lain, OPENJSON menyediakan tampilan set 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 tabel, tampilan, atau fungsi bernilai 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.

Note

Fungsi OPENJSON ini 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 OPENJSON fungsi. 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

Syntax

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

Fungsi OPENJSON bernilai tabel 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

Diagram sintaks untuk OPENJSON TVF.

Secara default, OPENJSON fungsi bernilai tabel mengembalikan tiga kolom, yang berisi nama kunci, nilai, dan jenis setiap key:value pasangan yang ditemukan di jsonExpression. Sebagai alternatif, Anda dapat secara eksplisit menentukan skema kumpulan hasil yang OPENJSON dikembalikan dengan menyediakan with_clause.

with_clause

Diagram sintaksis untuk klausa WITH di OPENJSON TVF.

with_clause berisi daftar kolom dengan jenisnya untuk OPENJSON dikembalikan. Secara default, OPENJSON mencocokkan 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.

Arguments

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);

Results:

kunci value jenis
String_value John 1
DoublePrecisionFloatingPoint_value 45 2
DoublePrecisionFloatingPoint_value 2.3456 2
BooleanTrue_value true 3
BooleanFalse_value false 3
Null_value NULL 0
Array_value ["a","r","r","a","y"] 4
Object_value {"obj":"ect"} 5
  • DoublePrecisionFloatingPoint_value sesuai dengan IEEE-754.

path

Adalah ekspresi jalur JSON opsional yang mereferensikan objek atau array dalam jsonExpression. OPENJSON mencari teks JSON pada posisi yang ditentukan dan hanya mengurai fragmen yang dirujuk. Untuk informasi selengkapnya, lihat Ekspresi Jalur JSON.

Anda dapat memberikan variabel sebagai nilai jalur. (Ini tidak didukung di SQL Server 2016 (13.x) dan versi yang lebih lama.)

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"')

Results

Key Value
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

Results

month temp month_id
Jan 10 0
Feb 12 1
Mar 15 2
Apr 17 3
May 23 4
Jun 27 5

Identitas hanya tersedia di kumpulan SQL tanpa server di Synapse Analytics.

with_clause

Secara eksplisit mendefinisikan skema output agar OPENJSON fungsi dikembalikan. With_clause opsional dapat berisi elemen berikut:

Nama col

Nama untuk kolom output.

Secara default, OPENJSON menggunakan nama kolom untuk mencocokkan properti dalam teks JSON. Misalnya, jika Anda menentukan kolom name dalam skema, OPENJSON coba isi kolom ini dengan properti "nama" dalam teks JSON. Anda dapat mengambil alih pemetaan default ini dengan menggunakan argumen column_path .

type

Jenis data untuk kolom output.

Note

Jika Anda juga menggunakan AS JSON opsi , jenis data 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.

SEBAGAI JSON

AS JSON Gunakan opsi dalam definisi kolom untuk menentukan bahwa properti yang dirujuk berisi objek atau array JSON dalam. Jika Anda menentukan AS JSON opsi , 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 akan kembali NULL dalam lax mode atau mengembalikan kesalahan dalam strict mode. Perilaku ini mirip dengan perilaku JSON_VALUE fungsi.

  • 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 akan kembali NULL dalam lax mode atau mengembalikan kesalahan dalam strict mode. Perilaku ini mirip dengan perilaku JSON_QUERY fungsi.

Note

Jika Anda ingin mengembalikan fragmen JSON berlapis dari properti JSON, Anda harus memberikan AS JSON bendera. Tanpa opsi ini, jika properti tidak dapat ditemukan, OPENJSON mengembalikan NULL nilai alih-alih objek atau array JSON yang dirujuk, atau mengembalikan kesalahan run-time dalam strict mode.

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  
 )

Results

Number Date Customer Quantity Order
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"}

Mengembalikan nilai

Kolom yang dikembalikan OPENJSON fungsi bergantung pada WITH opsi .

  • Saat Anda memanggil OPENJSON dengan skema default - yaitu, saat Anda tidak menentukan skema eksplisit dalam WITH klausul - fungsi mengembalikan tabel dengan kolom berikut:

    • Key. Nilai nvarchar(4000) yang berisi nama properti yang ditentukan atau indeks elemen dalam array yang ditentukan. Kolom key memiliki kolatasi BIN2.

    • Value. Nilai nvarchar(MAX) yang berisi nilai properti . Kolom value mewarisi kolasenya dari jsonExpression.

    • Type. Nilai int yang berisi jenis nilai . Kolom Type dikembalikan hanya saat Anda menggunakan OPENJSON dengan skema default. Kolom type memiliki salah satu nilai berikut:

      Nilai kolom Jenis Jenis data JSON
      0 nol
      1 string
      2 number
      3 true/false
      4 array
      5 objek

    Hanya properti tingkat pertama yang dikembalikan. Pernyataan gagal jika teks JSON tidak diformat dengan benar.

  • Saat Anda memanggil OPENJSON dan menentukan skema eksplisit dalam WITH klausa, fungsi mengembalikan tabel dengan skema yang Anda tentukan dalam WITH klausa.

Note

Kolom Key, Value, dan Type dikembalikan hanya ketika Anda menggunakan OPENJSON dengan skema default dan tidak tersedia dengan skema eksplisit.

Remarks

json_path digunakan dalam argumen OPENJSON kedua atau di with_clause dapat dimulai dengan lax kata kunci atau strict .

  • Dalam lax mode, OPENJSON tidak menimbulkan kesalahan jika objek atau nilai pada jalur yang ditentukan tidak dapat ditemukan. Jika jalur tidak dapat ditemukan, OPENJSON mengembalikan tataan hasil kosong atau NULL nilai.
  • Dalam strictmode OPENJSON , mengembalikan kesalahan jika jalur tidak dapat ditemukan.

Beberapa contoh di halaman ini secara eksplisit menentukan mode jalur, lax atau strict. Mode jalur bersifat opsional. Jika Anda tidak secara eksplisit menentukan mode jalur, lax mode adalah default. Untuk informasi selengkapnya tentang mode jalur dan ekspresi jalur, lihat Ekspresi Jalur JSON.

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 dapat 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" mencocokkan nilai 1 dalam teks JSON berikut:

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

Examples

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. CROSS APPLY Kemudian operator menggabungkan setiap baris pesanan penjualan ke baris yang dikembalikan oleh fungsi bernilai OPENJSON tabel.

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 klausa FROM.

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

Results

title street postcode lon lat
Pasar Makanan Utuh 17991 Jalan Redmond 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

Konten terkait

  • Last updated on