Mulai menggunakan fitur JSON di Azure SQL Database dan Azure SQL Managed Instance

  • Artikel

Berlaku untuk:Azure SQL DatabaseAzure SQL Managed Instance

Azure SQL Database dan Azure SQL Managed Instance memungkinkan Anda mengurai dan mengkueri data yang diwakili dalam format JavaScript Object Notation (JSON), dan mengekspor data relasional Anda sebagai teks JSON. Skenario JSON berikut tersedia:

Memformat data relasional dalam format JSON

Jika Anda memiliki layanan web yang mengambil data dari lapisan database dan memberikan respons dalam format JSON, atau kerangka kerja atau pustaka JavaScript pihak klien yang menerima data yang diformat sebagai JSON, Anda dapat memformat konten database Anda sebagai JSON secara langsung dalam kueri SQL. Anda tidak lagi harus menulis kode aplikasi yang memformat hasil dari Azure SQL Database atau Azure SQL Managed Instance sebagai JSON, atau menyertakan beberapa pustaka serialisasi JSON untuk mengonversi hasil kueri tabular lalu menserialisasikan objek ke format JSON. Sebagai gantinya, Anda dapat menggunakan klausul FOR JSON untuk memformat hasil kueri SQL sebagai JSON dan menggunakannya langsung di aplikasi Anda.

Dalam contoh berikut, baris dari tabel Sales.Customer diformat dengan format JSON dengan menggunakan klausul FOR JSON:

select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH

Klausul FOR JSON PATH memformat hasil kueri sebagai teks JSON. Nama kolom digunakan sebagai kunci, sedangkan nilai sel dihasilkan sebagai nilai JSON:

[
{"CustomerName":"Eric Torres","PhoneNumber":"(307) 555-0100","FaxNumber":"(307) 555-0101"},
{"CustomerName":"Cosmina Vlad","PhoneNumber":"(505) 555-0100","FaxNumber":"(505) 555-0101"},
{"CustomerName":"Bala Dixit","PhoneNumber":"(209) 555-0100","FaxNumber":"(209) 555-0101"}
]

Kumpulan hasil diformat sebagai array JSON di mana setiap baris diformat sebagai objek JSON terpisah.

PATH menunjukkan bahwa Anda dapat menyesuaikan format output hasil JSON Anda dengan menggunakan notasi titik di alias kolom. Kueri berikut mengubah nama kunci "CustomerName" dalam format JSON output, dan menempatkan nomor telepon dan faks di sub-objek "Kontak":

select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Output kueri ini terlihat seperti ini:

{
    "Name":"Nada Jovanovic",
    "Contact":{
           "Phone":"(215) 555-0100",
           "Fax":"(215) 555-0101"
    }
}

Dalam contoh ini, kami mengembalikan satu objek JSON alih-alih array dengan menentukan WITHOUT_ARRAY_WRAPPER baru. Anda bisa menggunakan opsi ini jika Anda tahu bahwa Anda mengembalikan satu objek sebagai hasil kueri.

Nilai utama klausul FOR JSON adalah memungkinkan Anda mengembalikan data hierarkis kompleks dari database Anda yang diformat sebagai objek atau array JSON bertumpuk. Contoh berikut ini memperlihatkan cara menyertakan baris dari Orders tabel yang termasuk Customer dalam array Orders bertumpuk:

select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,
        Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
    join Sales.Orders Orders
        on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

Alih-alih mengirim kueri terpisah untuk mendapatkan data Pelanggan dan kemudian mengambil daftar Pesanan terkait, Anda bisa mendapatkan semua data yang diperlukan dengan satu kueri, seperti yang ditunjukkan dalam contoh output berikut:

{
  "Name":"Nada Jovanovic",
  "Phone":"(215) 555-0100",
  "Fax":"(215) 555-0101",
  "Orders":[
    {"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
  ]
}

Bekerja dengan data JSON

Jika Anda tidak memiliki data yang terstruktur secara ketat, jika Anda memiliki sub-objek, array, atau data hierarkis yang kompleks, atau jika struktur data Anda berubah dari waktu ke waktu, maka format JSON dapat membantu Anda untuk mewakili struktur data yang kompleks.

JSON adalah format tekstual yang dapat digunakan seperti tipe string lainnya di Azure SQL Database dan Azure SQL Managed Instance. Anda dapat mengirim atau menyimpan data JSON sebagai NVARCHAR standar:

CREATE TABLE Products (
  Id int identity primary key,
  Title nvarchar(200),
  Data nvarchar(max)
)
go
CREATE PROCEDURE InsertProduct(@title nvarchar(200), @json nvarchar(max))
AS BEGIN
    insert into Products(Title, Data)
    values(@title, @json)
END

Data JSON yang digunakan dalam contoh ini diwakili dengan menggunakan tipe NVARCHAR(MAX). JSON dapat disisipkan ke dalam tabel ini atau disediakan sebagai argumen prosedur yang disimpan menggunakan sintaks Transact-SQL standar seperti yang ditunjukkan dalam contoh berikut:

EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'

Setiap bahasa atau pustaka sisi klien yang berfungsi dengan data string di Azure SQL Database dan Azure SQL Managed Instance juga akan berfungsi dengan data JSON. JSON dapat disimpan dalam tabel apa pun yang mendukung tipe NVARCHAR, seperti tabel yang dioptimalkan memori atau tabel versi Sistem. JSON tidak memperkenalkan batasan apa pun baik dalam kode pihak klien atau di lapisan database.

Mengkueri data JSON

Jika Anda memiliki data yang diformat sebagai JSON yang disimpan dalam tabel Azure SQL, fungsi JSON memungkinkan Anda menggunakan data ini dalam kueri SQL apa pun.

Fungsi JSON yang tersedia di Azure SQL Database dan Azure SQL Managed Instance memungkinkan Anda memperlakukan data yang diformat sebagai JSON sebagai tipe data SQL lainnya. Anda dapat dengan mudah mengekstrak nilai dari teks JSON, dan menggunakan data JSON dalam kueri apa pun:

select Id, Title, JSON_VALUE(Data, '$.Color'), JSON_QUERY(Data, '$.tags')
from Products
where JSON_VALUE(Data, '$.Color') = 'White'

update Products
set Data = JSON_MODIFY(Data, '$.Price', 60)
where Id = 1

Fungsi JSON_VALUE mengekstrak nilai dari teks JSON yang disimpan di kolom Data. Fungsi ini menggunakan jalur seperti JavaScript untuk mereferensikan nilai dalam teks JSON untuk diekstrak. Nilai yang diekstrak dapat digunakan di bagian mana pun dari kueri SQL.

Fungsi JSON_QUERY mirip dengan JSON_VALUE. Tidak JSON_VALUE, fungsi ini mengekstrak sub-objek kompleks seperti array atau objek yang ditempatkan dalam teks JSON.

Fungsi JSON_MODIFY memungkinkan Anda menentukan jalur nilai dalam teks JSON yang harus diperbarui, serta nilai baru yang akan menimpa yang lama. Dengan cara ini Anda dapat dengan mudah memperbarui teks JSON tanpa membandingkan kembali seluruh struktur.

Karena JSON disimpan dalam teks standar, tidak ada jaminan bahwa nilai yang disimpan dalam kolom teks diformat dengan benar. Anda dapat memverifikasi bahwa teks yang disimpan di kolom JSON diformat dengan benar dengan menggunakan batasan pemeriksaan Azure SQL Database standar dan fungsi ISJSON:

ALTER TABLE Products
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

Jika teks input diformat dengan benar JSON, fungsi ISJSON mengembalikan nilai 1. Pada setiap sisipkan atau perbarui kolom JSON, batasan ini akan memverifikasi bahwa nilai teks baru tidak cacat JSON.

Mengubah JSON menjadi format tabular

Azure SQL Database dan Azure SQL Managed Instance juga memungkinkan Anda mengubah koleksi JSON menjadi format tabular dan memuat atau mengkueri data JSON.

OPENJSON adalah fungsi nilai tabel yang mengurai teks JSON, menemukan array objek JSON, melakukan iterasi melalui elemen array, dan mengembalikan satu baris dalam hasil output untuk setiap elemen array.

JSON tabular

Dalam contoh di atas, kita dapat menentukan di mana menemukan array JSON yang harus dibuka (dalam $. Jalur pesanan), kolom apa yang harus dikembalikan sebagai hasilnya, dan di mana menemukan nilai JSON yang akan dikembalikan sebagai sel.

Kita dapat mengubah array JSON dalam @orders variabel menjadi satu set baris, menganalisis kumpulan hasil ini, atau menyisipkan baris ke dalam tabel standar:

CREATE PROCEDURE InsertOrders(@orders nvarchar(max))
AS BEGIN

    insert into Orders(Number, Date, Customer, Quantity)
    select Number, Date, Customer, Quantity
    FROM OPENJSON (@orders)
     WITH (
            Number varchar(200),
            Date datetime,
            Customer varchar(200),
            Quantity int
     )
END

Pengumpulan pesanan yang diformat sebagai array JSON dan disediakan sebagai parameter untuk prosedur yang disimpan dapat diuraikan dan dimasukkan ke dalam tabel Pesanan.