Data JSON dalam SQL Server

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

JSON adalah format data tekstual populer yang digunakan untuk bertukar data di aplikasi web dan seluler modern. JSON juga digunakan untuk menyimpan data yang tidak terstruktur dalam file log atau database NoSQL seperti Microsoft Azure Cosmos DB. Banyak layanan web REST mengembalikan hasil yang diformat sebagai teks JSON atau menerima data yang diformat sebagai JSON. Misalnya, sebagian besar layanan Azure, seperti Azure Search, Azure Storage, dan Azure Cosmos DB, memiliki titik akhir REST yang mengembalikan atau menggunakan JSON. JSON juga merupakan format utama untuk bertukar data antara halaman web dan server web dengan menggunakan panggilan AJAX.

Fungsi JSON, pertama kali diperkenalkan di SQL Server 2016 (13.x), memungkinkan Anda menggabungkan NoSQL dan konsep relasional dalam database yang sama. Anda dapat menggabungkan kolom relasional klasik dengan kolom yang berisi dokumen yang diformat sebagai teks JSON dalam tabel yang sama, menguraikan dan mengimpor dokumen JSON dalam struktur relasional, atau memformat data relasional ke teks JSON.

Catatan

Dukungan JSON memerlukan tingkat kompatibilitas database 130 atau lebih tinggi.

Berikut adalah contoh teks JSON:

[
    {
        "name": "John",
        "skills": [ "SQL", "C#", "Azure" ]
    },
    {
        "name": "Jane",
        "surname": "Doe"
    }
]

Dengan menggunakan fungsi dan operator bawaan SQL Server, Anda dapat melakukan hal-hal berikut dengan teks JSON:

• Uraikan teks JSON dan baca atau ubah nilai.
• Mengubah array objek JSON menjadi format tabel.
• Jalankan kueri Transact-SQL apa pun pada objek JSON yang dikonversi.
• Format hasil kueri Transact-SQL dalam format JSON.

Kemampuan JSON utama SQL Server dan SQL Database

Bagian berikutnya membahas kemampuan utama yang disediakan SQL Server dengan dukungan JSON bawaannya.

Jenis data JSON

Jenis data json baru yang menyimpan dokumen JSON dalam format biner asli yang memberikan manfaat berikut daripada menyimpan data JSON di varchar/nvarchar:

• Bacaan yang lebih efisien, karena dokumen sudah diurai
• Penulisan yang lebih efisien, karena kueri dapat memperbarui nilai individual tanpa mengakses seluruh dokumen
• Penyimpanan yang lebih efisien, dioptimalkan untuk pemadatan
• Tidak ada perubahan kompatibilitas dengan kode yang ada

Catatan

Jenis data JSON saat ini dalam pratinjau untuk Azure SQL Database dan Azure SQL Managed Instance (dikonfigurasi dengan kebijakan pembaruan Always-to-date).

Menggunakan fungsi JSON yang sama yang dijelaskan dalam artikel ini tetap menjadi cara paling efisien untuk mengkueri jenis data json . Untuk informasi selengkapnya tentang jenis data json asli, lihat Jenis data JSON.

Mengekstrak nilai dari teks JSON dan menggunakannya dalam kueri

Jika Anda memiliki teks JSON yang disimpan dalam tabel database, Anda bisa membaca atau mengubah nilai dalam teks JSON dengan menggunakan fungsi bawaan berikut:

• ISJSON (Transact-SQL) menguji apakah string berisi JSON yang valid.
• JSON_VALUE (Transact-SQL) mengekstrak nilai skalar dari string JSON.
• JSON_QUERY (Transact-SQL) mengekstrak objek atau array dari string JSON.
• JSON_MODIFY (Transact-SQL) mengubah nilai dalam string JSON.

Contoh

Dalam contoh berikut, kueri menggunakan data relasional dan JSON (disimpan dalam kolom bernama jsonCol) dari tabel yang disebut People:

SELECT Name,
    Surname,
    JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
    JSON_VALUE(jsonCol, '$.info.address."Address Line 1"')
        + ' ' + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
    JSON_QUERY(jsonCol, '$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
    AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
    AND STATUS = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

Aplikasi dan alat tidak melihat perbedaan antara nilai yang diambil dari kolom tabel skalar dan nilai yang diambil dari kolom JSON. Anda dapat menggunakan nilai dari teks JSON di bagian mana pun dari kueri Transact-SQL (termasuk klausa WHERE, ORDER BY, atau GROUP BY, agregat jendela, dan sebagainya). Fungsi JSON menggunakan sintaks seperti JavaScript untuk mereferensikan nilai di dalam teks JSON.

Untuk informasi selengkapnya, lihat Memvalidasi, Mengkueri, dan Mengubah Data JSON dengan Fungsi Bawaan (SQL Server), JSON_VALUE (Transact-SQL), dan JSON_QUERY (Transact-SQL).

Mengubah nilai JSON

Jika Anda harus memodifikasi bagian teks JSON, Anda dapat menggunakan fungsi JSON_MODIFY (Transact-SQL) untuk memperbarui nilai properti dalam string JSON dan mengembalikan string JSON yang diperbarui. Contoh berikut memperbarui nilai properti dalam variabel yang berisi JSON:

DECLARE @json NVARCHAR(MAX);

SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');

SELECT modifiedJson = @json;

Berikut set hasilnya.

{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}}

Mengonversi koleksi JSON menjadi set baris

Anda tidak memerlukan bahasa kueri kustom untuk mengkueri JSON di SQL Server. Untuk mengkueri data JSON, Anda bisa menggunakan T-SQL standar. Jika Anda harus membuat kueri atau melaporkan data JSON, Anda dapat dengan mudah mengonversi data JSON ke baris dan kolom dengan memanggil OPENJSON fungsi set baris. Untuk informasi selengkapnya, lihat Mengurai dan Mengubah Data JSON dengan OPENJSON.

Contoh berikut memanggil OPENJSON dan mengubah array objek yang disimpan dalam @json variabel ke set baris yang dapat dikueri dengan pernyataan Transact-SQL SELECT standar:

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
);

Berikut set hasilnya.

ID	firstName	lastName	usia	dateOfBirth
2	John	Smith	25
5	Jane	Smith		2005-11-04T12:00:00

OPENJSON mengubah array objek JSON menjadi tabel di mana setiap objek direpresentasikan sebagai satu baris, dan pasangan kunci/nilai dikembalikan sebagai sel. Output mengamati aturan berikut:

• OPENJSON mengonversi nilai JSON ke jenis yang ditentukan dalam WITH klausa.
• OPENJSON dapat menangani pasangan kunci/nilai datar dan objek berlapis yang diatur secara hierarkis.
• Anda tidak perlu mengembalikan semua bidang yang terkandung dalam teks JSON.
• Jika nilai JSON tidak ada, OPENJSON mengembalikan NULL nilai.
• Anda dapat secara opsional menentukan jalur setelah spesifikasi jenis untuk mereferensikan properti berlapis atau untuk mereferensikan properti dengan nama yang berbeda.
• Awalan opsional strict dalam jalur menentukan bahwa nilai untuk properti yang ditentukan harus ada dalam teks JSON.

Untuk informasi selengkapnya, lihat Mengurai dan Mengubah Data JSON dengan OPENJSON dan OPENJSON (Transact-SQL).

Dokumen JSON mungkin memiliki sub-elemen dan data hierarkis yang tidak dapat langsung dipetakan ke dalam kolom relasional standar. Dalam hal ini, Anda dapat meratakan hierarki JSON dengan menggabungkan entitas induk dengan sub-array.

Dalam contoh berikut, objek kedua dalam array memiliki sub-array yang mewakili keterampilan orang. Setiap sub-objek dapat diurai menggunakan panggilan fungsi tambahan OPENJSON :

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"}
]';

SELECT id,
    firstName,
    lastName,
    age,
    dateOfBirth,
    skill
FROM OPENJSON(@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob',
    skills NVARCHAR(MAX) '$.info.skills' AS JSON
)
OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(8) '$');

Array skills dikembalikan dalam fragmen teks JSON pertama OPENJSON sebagai asli dan diteruskan ke fungsi lain OPENJSON menggunakan APPLY operator. Fungsi kedua OPENJSON menguraikan array JSON dan mengembalikan nilai string sebagai kumpulan baris kolom tunggal yang akan digabungkan dengan hasil yang pertama OPENJSON.

Berikut set hasilnya.

ID	firstName	lastName	usia	dateOfBirth	keterampilan
2	John	Smith	25
5	Jane	Smith		2005-11-04T12:00:00	SQL
5	Jane	Smith		2005-11-04T12:00:00	C#
5	Jane	Smith		2005-11-04T12:00:00	Azure

OUTER APPLY OPENJSON menggabungkan entitas tingkat pertama dengan sub-array dan mengembalikan resultset flatten. Karena JOIN, baris kedua diulang untuk setiap keterampilan.

Mengonversi data SQL Server ke JSON atau mengekspor JSON

Catatan

Mengonversi data Azure Synapse Analytics ke JSON atau mengekspor JSON tidak didukung.

Format data SQL Server atau hasil kueri SQL sebagai JSON dengan menambahkan FOR JSON klausa ke SELECT pernyataan. Gunakan FOR JSON untuk mendelegasikan pemformatan output JSON dari aplikasi klien Anda ke SQL Server. Untuk informasi selengkapnya, lihat Memformat hasil kueri sebagai JSON dengan FOR JSON.

Contoh berikut menggunakan mode PATH dengan FOR JSON klausa:

SELECT id,
    firstName AS "info.name",
    lastName AS "info.surname",
    age,
    dateOfBirth AS dob
FROM People
FOR JSON PATH;

Klausa memformat FOR JSON hasil SQL sebagai teks JSON yang dapat disediakan untuk aplikasi apa pun yang memahami JSON. Opsi PATH menggunakan alias yang dipisahkan titik dalam klausa SELECT untuk menumpuk objek dalam hasil kueri.

Berikut set hasilnya.

[
  {
    "id": 2,
    "info": {
      "name": "John",
      "surname": "Smith"
    },
    "age": 25
  },
  {
    "id": 5,
    "info": {
      "name": "Jane",
      "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
  }
]

Untuk informasi selengkapnya, lihat Memformat hasil kueri sebagai JSON dengan FOR JSON dan FOR Clause (Transact-SQL).

Data JSON dari agregat

Fungsi agregat JSON memungkinkan pembangunan objek atau array JSON berdasarkan agregat dari data SQL.

• JSON_OBJECTAGG membuat objek JSON dari agregasi data atau kolom SQL.
• JSON_ARRAYAGG membangun array JSON dari agregasi data atau kolom SQL.

Catatan

Kedua fungsi JSON_OBJECTAGG agregat json dan JSON_ARRAYAGG saat ini dalam pratinjau untuk Azure SQL Database dan Azure SQL Managed Instance (dikonfigurasi dengan kebijakan pembaruan Always-to-date).

Kasus penggunaan untuk data JSON di SQL Server

Dukungan JSON di SQL Server dan Azure SQL Database memungkinkan Anda menggabungkan konsep relasional dan NoSQL. Anda dapat dengan mudah mengubah data relasional ke semi terstruktur dan sebaliknya. Namun, JSON bukan pengganti model relasional yang ada. Berikut adalah beberapa kasus penggunaan spesifik yang mendapat manfaat dari dukungan JSON di SQL Server dan di SQL Database.

Menyederhanakan model data yang kompleks

Pertimbangkan untuk mendenormalisasi model data Anda dengan bidang JSON sebagai pengganti beberapa tabel anak.

Menyimpan data ritel dan e-niaga

Simpan info tentang produk dengan berbagai atribut variabel dalam model denormalisasi untuk fleksibilitas.

Memproses data log dan telemetri

Muat, kueri, dan analisis data log yang disimpan sebagai file JSON dengan semua kekuatan bahasa Transact-SQL.

Menyimpan data IoT semi terstruktur

Saat Anda memerlukan analisis real-time data IoT, muat data masuk langsung ke database alih-alih penahapan di lokasi penyimpanan.

Menyederhanakan pengembangan REST API

Ubah data relasional dari database Anda dengan mudah ke dalam format JSON yang digunakan oleh REST API yang mendukung situs web Anda.

Menggabungkan data relasional dan JSON

SQL Server menyediakan model hibrid untuk menyimpan dan memproses data relasional dan JSON dengan menggunakan bahasa T-SQL standar. Anda dapat menata koleksi dokumen JSON Anda dalam tabel, membangun hubungan di antaranya, menggabungkan kolom skalar yang sangat diketik yang disimpan dalam tabel dengan pasangan kunci/nilai fleksibel yang disimpan dalam kolom JSON, dan mengkueri nilai skalar dan JSON dalam satu atau beberapa tabel dengan menggunakan Transact-SQL penuh.

Teks JSON disimpan dalam VARCHAR kolom atau NVARCHAR dan diindeks sebagai teks biasa. Setiap fitur atau komponen SQL Server yang mendukung teks mendukung JSON, sehingga hampir tidak ada batasan interaksi antara JSON dan fitur SQL Server lainnya. Anda dapat menyimpan JSON dalam tabel Dalam memori atau Temporal, menerapkan predikat Keamanan Tingkat Baris pada teks JSON, dan sebagainya.

Berikut adalah beberapa kasus penggunaan yang menunjukkan bagaimana Anda dapat menggunakan dukungan JSON bawaan di SQL Server.

Menyimpan dan mengindeks data JSON di SQL Server

JSON adalah format tekstual sehingga dokumen JSON dapat disimpan dalam NVARCHAR kolom dalam SQL Database. Karena NVARCHAR jenis didukung di semua subsistem SQL Server, Anda dapat menempatkan dokumen JSON dalam tabel dengan indeks penyimpan kolom berkluster, tabel memori yang dioptimalkan, atau file eksternal yang dapat dibaca menggunakan OPENROWSET atau PolyBase.

Untuk mempelajari selengkapnya tentang opsi Anda untuk menyimpan, mengindeks, dan mengoptimalkan data JSON di SQL Server, lihat artikel berikut ini:

• Menyimpan dokumen JSON di SQL Server atau SQL Database
• Mengindeks data JSON
• Mengoptimalkan pemrosesan JSON dengan OLTP dalam memori

Memuat file JSON ke SQL Server

Anda dapat memformat informasi yang disimpan dalam file sebagai JSON standar atau JSON yang dibatasi baris. SQL Server dapat mengimpor konten file JSON, mengurainya dengan menggunakan OPENJSON fungsi atau JSON_VALUE , dan memuatnya ke dalam tabel.

• Jika dokumen JSON Anda disimpan dalam file lokal, pada drive jaringan bersama, atau di lokasi Azure Files yang dapat diakses oleh SQL Server, Anda dapat menggunakan impor massal untuk memuat data JSON Anda ke SQL Server.

• Jika file JSON yang dibatasi baris disimpan di penyimpanan Azure Blob atau sistem file Hadoop, Anda dapat menggunakan PolyBase untuk memuat teks JSON, mengurainya dalam kode Transact-SQL, dan memuatnya ke dalam tabel.

Mengimpor data JSON ke dalam tabel SQL Server

Jika Anda harus memuat data JSON dari layanan eksternal ke SQL Server, Anda dapat menggunakan OPENJSON untuk mengimpor data ke SQL Server alih-alih mengurai data di lapisan aplikasi.

Dalam platform yang didukung, gunakan jenis data json asli alih-alih nvarchar(maks) untuk meningkatkan performa dan penyimpanan yang lebih efisien.

DECLARE @jsonVariable NVARCHAR(MAX);

SET @jsonVariable = 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
    }
  }
]';

-- INSERT INTO <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, N'$') WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData;

Anda dapat menyediakan konten variabel JSON oleh layanan REST eksternal, mengirimkannya sebagai parameter dari kerangka kerja JavaScript sisi klien, atau memuatnya dari file eksternal. Anda dapat dengan mudah menyisipkan, memperbarui, atau menggabungkan hasil dari teks JSON ke dalam tabel SQL Server.

Menganalisis data JSON dengan kueri SQL

Jika Anda harus memfilter atau menggabungkan data JSON untuk tujuan pelaporan, Anda dapat menggunakan OPENJSON untuk mengubah JSON ke format relasional. Anda kemudian dapat menggunakan transact-SQL standar dan fungsi bawaan untuk menyiapkan laporan.

SELECT Tab.Id,
    SalesOrderJsonData.Customer,
    SalesOrderJsonData.Date
FROM SalesOrderRecord AS Tab
CROSS APPLY OPENJSON(Tab.json, N'$.Orders.OrdersArray') WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'
ORDER BY JSON_VALUE(Tab.json, '$.Group'),
    Tab.DateModified;

Anda bisa menggunakan kolom tabel standar dan nilai dari teks JSON dalam kueri yang sama. Anda dapat menambahkan indeks pada JSON_VALUE(Tab.json, '$.Status') ekspresi untuk meningkatkan performa kueri. Untuk informasi selengkapnya, lihat Mengindeks data JSON.

Mengembalikan data dari tabel SQL Server yang diformat sebagai JSON

Jika Anda memiliki layanan web yang mengambil data dari lapisan database dan mengembalikannya dalam format JSON, atau jika Anda memiliki kerangka kerja atau pustaka JavaScript yang menerima data yang diformat sebagai JSON, Anda dapat memformat output JSON secara langsung dalam kueri SQL. Alih-alih menulis kode atau menyertakan pustaka untuk mengonversi hasil kueri tabular lalu membuat serialisasi objek ke format JSON, Anda dapat menggunakan FOR JSON untuk mendelegasikan pemformatan JSON ke SQL Server.

Misalnya, Anda mungkin ingin menghasilkan output JSON yang sesuai dengan spesifikasi OData. Layanan web mengharapkan permintaan dan respons dalam format berikut:

• Minta: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName

• Jawaban: {"@odata.context": "https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity", "ProductID": 1, "ProductName": "Chai"}

URL OData ini mewakili permintaan kolom ProductID dan ProductName untuk produk dengan ID 1. Anda dapat menggunakan FOR JSON untuk memformat output seperti yang diharapkan di SQL Server.

SELECT 'https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity' AS '@odata.context',
  ProductID,
  Name as ProductName
FROM Production.Product
WHERE ProductID = 1
FOR JSON AUTO;

Output kueri ini adalah teks JSON yang sepenuhnya sesuai dengan spesifikasi OData. Pemformatan dan pelepasan ditangani oleh SQL Server. SQL Server juga dapat memformat hasil kueri dalam format apa pun, seperti OData JSON atau GeoJSON.

Uji coba dukungan JSON bawaan dengan database sampel AdventureWorks

Untuk mendapatkan database sampel AdventureWorks, unduh setidaknya file database dan file sampel dan skrip dari GitHub.

Setelah Anda memulihkan database sampel ke instans SQL Server, ekstrak file sampel, lalu buka JSON Sample Queries procedures views and indexes.sql file dari folder JSON. Jalankan skrip dalam file ini untuk memformat ulang beberapa data yang ada sebagai data JSON, menguji kueri sampel dan laporan melalui data JSON, mengindeks data JSON, dan mengimpor dan mengekspor JSON.

Berikut adalah apa yang dapat Anda lakukan dengan skrip yang disertakan dalam file:

• Denormalisasi skema yang ada untuk membuat kolom data JSON.

  • Simpan informasi dari SalesReasonstabel , SalesOrderDetails, SalesPerson, Customer, dan lainnya yang berisi informasi yang terkait dengan pesanan penjualan ke dalam kolom JSON dalam SalesOrder_json tabel.

  • Simpan informasi dari EmailAddresses dan PersonPhone tabel dalam Person_json tabel sebagai array objek JSON.

• Buat prosedur dan tampilan yang mengkueri data JSON.

• Indeks data JSON. Buat indeks pada properti JSON dan indeks teks lengkap.

• Impor dan ekspor JSON. Buat dan jalankan prosedur yang mengekspor konten Person dan SalesOrder tabel sebagai hasil JSON, serta mengimpor dan memperbarui Person dan SalesOrder tabel dengan menggunakan input JSON.

• Jalankan contoh kueri. Jalankan beberapa kueri yang memanggil prosedur dan tampilan tersimpan yang Anda buat di langkah 2 dan 4.

• Bersihkan skrip. Jangan jalankan bagian ini jika Anda ingin menyimpan prosedur dan tampilan tersimpan yang Anda buat di langkah 2 dan 4.

Konten terkait

• SELECT - UNTUK Klausul (Transact-SQL)
• OPENJSON (Transact-SQL)
• Fungsi JSON (Transact-SQL)
• ISJSON (T-SQL)
• JSON_VALUE (T-SQL)
• JSON_OBJECT (T-SQL)
• JSON_ARRAY (T-SQL)
• JSON_QUERY (T-SQL)
• JSON_MODIFY (T-SQL)