Bagikan melalui

Mulai menggunakan fitur JSON

Berlaku untuk:Azure SQL Database Azure SQL Managed InstanceSQL database di Fabric

Azure SQL Database, database SQL di Microsoft Fabric, 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 perlu lagi menulis kode aplikasi yang memformat hasil sebagai JSON, atau menyertakan beberapa pustaka serialisasi JSON untuk mengonversi hasil kueri tabular lalu membuat serialisasi objek ke format JSON. Sebagai gantinya FOR JSON , Anda dapat menggunakan klausul 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

Klausa 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 CustomerName kunci dalam format JSON output, dan menempatkan nomor telepon dan faks di Contact sub-objek:

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 FOR JSON utama klausul ini adalah memungkinkan Anda mengembalikan data hierarkis kompleks dari database Anda yang diformat sebagai objek atau array JSON berlapis. 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 Customer data lalu untuk mengambil daftar terkait Orders, 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 jenis string lainnya. 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 jenis 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 juga akan berfungsi dengan data JSON. JSON dapat disimpan dalam tabel apa pun yang mendukung jenis 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.

Kueri data JSON

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

Fungsi JSON memungkinkan Anda memperlakukan data yang diformat sebagai JSON sebagai jenis 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 ini 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 seperti 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 perlu diperbarui, serta nilai baru yang akan menggantikan nilai 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 dalam kolom JSON diformat dengan benar dengan menggunakan batasan pemeriksaan standar dan ISJSON fungsi :

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

Jika teks input diformat JSON dengan benar, ISJSON fungsi 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

Anda dapat mengubah koleksi JSON menjadi format tabular dan memuat atau mengkueri data JSON.

OPENJSON adalah fungsi T-SQL 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.

Cuplikan layar dan cuplikan kode sampel data tabular JSON.

Dalam contoh ini, kita dapat menentukan lokasi untuk menemukan array JSON yang harus dibuka (di jalur $.Orders JSON), kolom apa yang perlu dikembalikan sebagai hasil, 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.

Konten terkait