Bagikan melalui

Mengurai dan mengubah data JSON dengan OPENJSON

  • Artikel

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

Fungsi OPENJSON set baris mengonversi teks JSON menjadi sekumpulan baris dan kolom. Setelah mengubah koleksi JSON menjadi set baris dengan OPENJSON, Anda dapat menjalankan kueri SQL apa pun pada data yang dikembalikan atau menyisipkannya ke dalam tabel SQL Server. Untuk informasi selengkapnya tentang bekerja dengan data JSON di Mesin Database SQL Server, lihat data JSON di SQL Server.

Fungsi ini OPENJSON mengambil satu objek JSON atau kumpulan objek JSON dan mengubahnya menjadi satu atau beberapa baris. Secara default, OPENJSON fungsi mengembalikan data berikut:

  • Dari objek JSON, fungsi mengembalikan semua pasangan kunci/nilai yang ditemukannya di tingkat pertama.
  • Dari array JSON, fungsi mengembalikan semua elemen array dengan indeksnya.

Anda dapat menambahkan klausul opsional WITH untuk menyediakan skema yang secara eksplisit menentukan struktur output.

OPENJSON dengan output default

Saat Anda menggunakan OPENJSON fungsi tanpa memberikan skema eksplisit untuk hasil - yaitu, tanpa WITH klausa setelah OPENJSON - fungsi mengembalikan tabel dengan tiga kolom berikut:

  1. Properti name dalam objek input (atau indeks elemen dalam array input).
  2. Properti value atau elemen array.
  3. type (misalnya, string, angka, boolean, array, atau objek).

OPENJSON mengembalikan setiap properti objek JSON, atau setiap elemen array, sebagai baris terpisah.

Contoh berikut menggunakan OPENJSON dengan skema default - yaitu, tanpa klausul opsional WITH - dan mengembalikan satu baris untuk setiap properti objek JSON.

DECLARE @json NVARCHAR(MAX);

SET @json='{ "name": "John", "surname": "Doe", "age": 45, "skills": [ "SQL", "C#", "MVC" ]}';

SELECT *
FROM OPENJSON(@json);

Berikut set hasilnya.

kunci value jenis
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

Untuk informasi dan contoh selengkapnya, lihat Menggunakan OPENJSON dengan Skema Default.

Untuk sintaks dan penggunaan, lihat OPENJSON.

Output OPENJSON dengan struktur eksplisit

Saat Anda menentukan skema untuk hasil dengan menggunakan WITH klausa OPENJSON fungsi, fungsi mengembalikan tabel hanya dengan kolom yang Anda tentukan dalam WITH klausa. Dalam klausa opsional WITH , Anda menentukan sekumpulan kolom output, jenisnya, dan jalur properti sumber JSON untuk setiap nilai output. OPENJSON melakukan iterasi melalui array objek JSON, membaca nilai pada jalur yang ditentukan untuk setiap kolom, dan mengonversi nilai ke jenis yang ditentukan.

Contoh berikut menggunakan OPENJSON dengan skema untuk output yang Anda tentukan secara eksplisit dalam WITH klausa.

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2024-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    },
    {
        "Order": {
            "Number": "SO43661",
            "Date": "2024-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'
);

Berikut set hasilnya.

Number Tanggal Pelanggan Quantity
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-06-01T00:00:00 AW73565 3

Fungsi ini mengembalikan dan memformat elemen array JSON.

  • Untuk setiap elemen dalam array JSON, OPENJSON menghasilkan baris baru dalam tabel output. Dua elemen dalam array JSON dikonversi menjadi dua baris dalam tabel yang dikembalikan.

  • Untuk setiap kolom, yang ditentukan dengan menggunakan colName type json_path sintaks, OPENJSON mengonversi nilai yang ditemukan di setiap elemen array pada jalur yang ditentukan ke jenis yang ditentukan. Dalam contoh ini, nilai untuk Date kolom diambil dari setiap elemen pada jalur $.Order.Date dan dikonversi ke nilai tanggalwaktu.

Untuk informasi dan contoh selengkapnya, lihat Menggunakan OPENJSON dengan Skema Eksplisit (SQL Server).

Untuk sintaks dan penggunaan, lihat OPENJSON.

OPENJSON memerlukan tingkat kompatibilitas 130

Fungsi OPENJSON ini hanya tersedia di bawah tingkat 130 kompatibilitas dan lebih besar. Jika tingkat kompatibilitas database Anda lebih rendah dari 130, SQL Server tidak dapat menemukan dan menjalankan OPENJSON fungsi. Fungsi JSON bawaan lainnya tersedia di semua tingkat kompatibilitas.

Anda bisa memeriksa tingkat kompatibilitas dalam sys.databases tampilan atau di properti database, dan mengubah tingkat kompatibilitas database dengan menggunakan perintah berikut:

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Konten terkait