Bagikan melalui

Pengindeksan data JSON

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed Instance SQL databasedi Microsoft Fabric

Anda dapat mengoptimalkan kueri Anda melalui dokumen JSON menggunakan indeks standar.

Note

Di SQL Server 2025 (17.x), Anda dapat menggunakan fitur CREATE JSON INDEX (Transact-SQL).

Indeks bekerja dengan cara yang sama pada data JSON di varchar/nvarchar atau jenis data json asli.

Indeks database meningkatkan performa operasi filter dan pengurutan. Tanpa indeks, SQL Server harus melakukan pemindaian tabel penuh setiap kali Anda mengkueri data.

Note

Jenis data JSON:

  • umumnya tersedia untuk Azure SQL Database dan Azure SQL Managed Instance dengan kebijakan pembaruanSQL Server 2025 atau Always-up-to-date.
  • dalam pratinjau untuk SQL Server 2025 (17.x) dan database SQL di Fabric.

Mengindeks properti JSON dengan menggunakan kolom komputasi

Saat Anda menyimpan data JSON di SQL Server, biasanya Anda ingin memfilter atau mengurutkan hasil kueri menurut satu atau beberapa properti dokumen JSON.

Example

Dalam contoh ini, asumsikan bahwa AdventureWorks.SalesOrderHeader tabel memiliki Info kolom yang berisi berbagai informasi dalam format JSON tentang pesanan penjualan. Misalnya, berisi data yang tidak terstruktur tentang pelanggan, sales person, alamat pengiriman dan penagihan, dan sebagainya. Anda dapat menggunakan nilai dari Info kolom untuk memfilter pesanan penjualan untuk pelanggan.

Secara default, kolom yang digunakan tidak ada, kolom Info dapat dibuat dalam AdventureWorks database dengan kode berikut. Contoh berikut tidak berlaku untuk AdventureWorksLT rangkaian database sampel.

IF NOT EXISTS (SELECT *
               FROM sys.columns
               WHERE object_id = OBJECT_ID('[Sales].[SalesOrderHeader]')
                     AND name = 'Info')
    ALTER TABLE [Sales].[SalesOrderHeader]
        ADD [Info] NVARCHAR (MAX) NULL;
GO

UPDATE h
SET [Info] =
(
    SELECT [Customer.Name] = concat(p.FirstName, N' ', p.LastName), 
           [Customer.ID] = p.BusinessEntityID, 
           [Customer.Type] = p.[PersonType], 
           [Order.ID] = soh.SalesOrderID, 
           [Order.Number] = soh.SalesOrderNumber, 
           [Order.CreationData] = soh.OrderDate, 
           [Order.TotalDue] = soh.TotalDue
    FROM [Sales].SalesOrderHeader AS soh
        INNER JOIN [Sales].[Customer] AS c
            ON c.CustomerID = soh.CustomerID
        INNER JOIN [Person].[Person] AS p
            ON p.BusinessEntityID = c.CustomerID
    WHERE soh.SalesOrderID = h.SalesOrderID
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
FROM [Sales].SalesOrderHeader AS h;

Kueri untuk dioptimalkan

Berikut adalah contoh jenis kueri yang ingin Anda optimalkan dengan menggunakan indeks.

SELECT SalesOrderNumber,
       OrderDate,
       JSON_VALUE(Info, '$.Customer.Name') AS CustomerName
FROM Sales.SalesOrderHeader
WHERE JSON_VALUE(Info, '$.Customer.Name') = N'Aaron Campbell';

Contoh indeks

Jika Anda ingin mempercepat filter atau ORDER BY klausul atas suatu properti di dalam dokumen JSON, Anda bisa menggunakan indeks yang sama dengan yang sudah Anda gunakan pada kolom lain. Namun, Anda tidak dapat langsung mereferensikan properti dalam dokumen JSON.

  1. Pertama, buat "kolom virtual" yang mengembalikan nilai yang ingin Anda gunakan untuk pemfilteran.
  2. Kemudian, buat indeks pada kolom virtual tersebut.

Contoh berikut membuat kolom komputasi yang dapat digunakan untuk pengindeksan. Kemudian membuat indeks pada kolom komputasi baru. Contoh ini membuat kolom yang memperlihatkan nama pelanggan, yang disimpan di jalur $.Customer.Name dalam data JSON.

ALTER TABLE Sales.SalesOrderHeader
    ADD vCustomerName AS JSON_VALUE(Info, '$.Customer.Name');

CREATE INDEX idx_soh_json_CustomerName
    ON Sales.SalesOrderHeader(vCustomerName);

Pernyataan ini mengembalikan peringatan berikut:

Warning! The maximum key length for a nonclustered index is 1700 bytes.
The index 'vCustomerName' has maximum length of 8000 bytes.
For some combination of large values, the insert/update operation will fail.

Fungsi JSON_VALUE mungkin mengembalikan nilai teks hingga 8000 byte (misalnya, sebagai jenis nvarchar(4000 ). Namun, nilai yang lebih panjang dari 1700 byte tidak dapat diindeks. Jika Anda mencoba memasukkan nilai di kolom komputasi terindeks yang lebih panjang dari 1700 byte, operasi bahasa manipulasi data (DML) gagal.

Untuk performa yang lebih baik, cobalah untuk mentransmisikan nilai yang Anda ekspos menggunakan kolom komputasi ke dalam jenis data terkecil yang berlaku. Gunakan jenis int dan datetime2 alih-alih jenis string.

Info selengkapnya tentang kolom terhitung

Kolom komputasi tidak bertahan. Kolom komputasi hanya dihitung ketika indeks perlu dibangun kembali. Ini tidak menempati ruang tambahan dalam tabel.

Penting bagi Anda untuk membuat kolom komputasi dengan ekspresi yang sama dengan yang Anda rencanakan untuk digunakan dalam kueri Anda - dalam contoh ini, ekspresinya adalah JSON_VALUE(Info, '$.Customer.Name').

Anda tidak perlu menulis ulang kueri Anda. Jika Anda menggunakan ekspresi dengan JSON_VALUE fungsi , seperti yang diperlihatkan dalam kueri contoh sebelumnya, SQL Server melihat bahwa ada kolom komputasi yang setara dengan ekspresi yang sama dan menerapkan indeks jika memungkinkan.

Rencana eksekusi untuk contoh ini

Berikut adalah rencana eksekusi untuk kueri dalam contoh ini.

Cuplikan layar memperlihatkan rencana eksekusi untuk contoh ini.

Alih-alih pemindaian tabel penuh, SQL Server menggunakan pencarian indeks ke dalam indeks non-kluster dan menemukan baris yang memenuhi kondisi yang ditentukan. Kemudian menggunakan pencarian kunci dalam SalesOrderHeader tabel untuk mengambil kolom lain yang dirujuk dalam kueri - dalam contoh ini, SalesOrderNumber dan OrderDate.

Optimalkan indeks lebih lanjut dengan kolom yang disertakan

Jika Anda menambahkan kolom yang diperlukan dalam indeks, Anda dapat menghindari pencarian tambahan ini dalam tabel. Anda dapat menambahkan kolom ini sebagai kolom standar yang disertakan, seperti yang diperlihatkan dalam contoh berikut, yang memperluas contoh sebelumnya CREATE INDEX .

CREATE INDEX idx_soh_json_CustomerName
    ON Sales.SalesOrderHeader(vCustomerName)
    INCLUDE(SalesOrderNumber, OrderDate);

Dalam hal ini, SQL Server tidak perlu membaca lebih banyak data dari SalesOrderHeader tabel karena semua yang dibutuhkan disertakan dalam indeks JSON non-kluster. Jenis indeks ini adalah cara yang baik untuk menggabungkan JSON dan data kolom dalam kueri dan untuk membuat indeks optimal untuk beban kerja Anda.

Indeks JSON adalah indeks yang paham pengurutan

Fitur penting dari indeks atas data JSON adalah bahwa indeks tersebut memperhitungkan pengurutan. Hasil JSON_VALUE fungsi yang Anda gunakan saat membuat kolom komputasi adalah nilai teks yang mewarisi kolasenya dari ekspresi input. Oleh karena itu, nilai dalam indeks diurutkan menggunakan aturan kolase yang ditentukan dalam kolom sumber.

Untuk menunjukkan bahwa indeks menyadari pengurutan, contoh berikut membuat tabel koleksi sederhana dengan kunci primer dan konten JSON.

CREATE TABLE JsonCollection
(
    id INT IDENTITY CONSTRAINT PK_JSON_ID PRIMARY KEY,
    [json] NVARCHAR (MAX) COLLATE SERBIAN_CYRILLIC_100_CI_AI
        CONSTRAINT [Content should be formatted as JSON] CHECK (ISJSON(json) > 0)
);

Perintah sebelumnya menentukan penyortiran Sirilik Serbia untuk kolomjson. Contoh berikut mengisi tabel dan membuat indeks pada properti nama.

INSERT INTO JsonCollection
VALUES
    (N'{"name":"Иво","surname":"Андрић"}'),
    (N'{"name":"Андрија","surname":"Герић"}'),
    (N'{"name":"Владе","surname":"Дивац"}'),
    (N'{"name":"Новак","surname":"Ђоковић"}'),
    (N'{"name":"Предраг","surname":"Стојаковић"}'),
    (N'{"name":"Михајло","surname":"Пупин"}'),
    (N'{"name":"Борислав","surname":"Станковић"}'),
    (N'{"name":"Владимир","surname":"Грбић"}'),
    (N'{"name":"Жарко","surname":"Паспаљ"}'),
    (N'{"name":"Дејан","surname":"Бодирога"}'),
    (N'{"name":"Ђорђе","surname":"Вајферт"}'),
    (N'{"name":"Горан","surname":"Бреговић"}'),
    (N'{"name":"Милутин","surname":"Миланковић"}'),
    (N'{"name":"Никола","surname":"Тесла"}');
GO

ALTER TABLE JsonCollection
    ADD vName AS JSON_VALUE(json, '$.name');

CREATE INDEX idx_name
    ON JsonCollection(vName);

Perintah sebelumnya membuat indeks standar pada kolom vNamekomputasi , yang mewakili nilai dari properti JSON $.name . Di halaman kode Sirilik Serbia, urutan huruf adalah А, , , БВГ, Д, Ђ, Е, dll. Urutan item dalam indeks sesuai dengan aturan Sirilik Serbia karena hasil JSON_VALUE fungsi mewarisi kolasenya dari kolom sumber. Contoh berikut mengkueri koleksi ini dan mengurutkan hasil berdasarkan nama.

SELECT JSON_VALUE(json, '$.name'),
       *
FROM JsonCollection
ORDER BY JSON_VALUE(json, '$.name');

Jika Anda melihat rencana pelaksanaan yang sebenarnya, Anda akan melihat bahwa paket tersebut menggunakan nilai yang sudah diurutkan dari indeks non-kluster.

Cuplikan layar memperlihatkan rencana eksekusi yang menggunakan nilai yang diurutkan dari indeks nonkluster.

Meskipun kueri memiliki klausa ORDER BY , rencana eksekusi tidak menggunakan operator Urutkan. Indeks JSON sudah diurutkan sesuai dengan aturan huruf Sirilik Serbia. Oleh karena itu SQL Server dapat menggunakan indeks nonclustered tempat hasil sudah diurutkan.

Namun, jika Anda mengubah kolasi ekspresi ORDER BY - misalnya, jika Anda menambahkan COLLATE French_100_CI_AS_SC setelah fungsi JSON_VALUE - Anda mendapatkan rencana pelaksanaan kueri yang berbeda.

Cuplikan layar memperlihatkan rencana eksekusi yang berbeda.

Karena urutan nilai dalam indeks tidak sesuai dengan aturan kolase Prancis, SQL Server tidak dapat menggunakan indeks untuk mengurutkan hasil. Oleh karena itu, ini menambahkan operator Sort yang mengurutkan hasil menggunakan aturan kolasi Prancis.

Video Microsoft

Untuk pengenalan visual dukungan JSON bawaan, lihat video berikut ini:

Konten terkait

  • Last updated on