JSON verilerini dizine ekleme

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve üzeri Azure SQL VeritabanıAzure SQL Yönetilen Örneği

Standart dizinleri kullanarak JSON belgeleri üzerinden sorgularınızı iyileştirebilirsiniz. SQL Server'ın özel JSON dizinleri yok.

• Şu anda SQL Server json yerleşik bir veri türü değildir.
• JSON veri türü şu anda Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği için önizleme aşamasındadır (Always-up-to-date güncelleştirme ilkesi) ile yapılandırılmıştır.

Dizinler, varchar/nvarchar veya yerel json veri türüJSON veri türündeki JSON verilerinde aynı şekilde çalışır.

Veritabanı dizinleri, filtre ve sıralama işlemlerinin performansını artırır. Dizinler olmadan, SQL Server'ın verileri her sorguladığınızda tam tablo taraması yapması gerekir.

Hesaplanan sütunları kullanarak JSON özelliklerini dizine ekleme

JSON verilerini SQL Server'da depolarken, genellikle sorgu sonuçlarını JSON belgelerinin bir veya daha fazla özelliğine göre filtrelemek veya sıralamak istersiniz.

Örnek

Bu örnekte, AdventureWorks.SalesOrderHeader tablosunun satış siparişleri hakkında JSON biçiminde çeşitli bilgiler içeren bir Info sütunu olduğunu varsayalım. Örneğin müşteri, satış elemanı, sevkiyat ve faturalama adresleri vb. hakkında yapılandırılmamış veriler içerir. Müşterinin satış siparişlerini filtrelemek için Info sütunundaki değerleri kullanabilirsiniz.

Varsayılan olarak, kullanılan sütun Info mevcut değildir; AdventureWorks veritabanında aşağıdaki kodla oluşturulabilir. Aşağıdaki örnekler AdventureWorksLT örnek veritabanları serisi için geçerli değildir.

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; 

en iyi duruma getirmek için sorgu

Aşağıda, dizin kullanarak iyileştirmek istediğiniz sorgu türüne bir örnek verilmiştır.

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

Örnek dizin

JSON belgesindeki bir özellik üzerinde filtrelerinizi veya ORDER BY yan tümcelerinizi hızlandırmak istiyorsanız, diğer sütunlarda zaten kullandığınız dizinleri kullanabilirsiniz. Ancak, JSON belgelerindeki özelliklere doğrudan referans veremezsiniz.

1. İlk olarak, filtreleme için kullanmak istediğiniz değerleri döndüren bir "sanal sütun" oluşturun.
2. Ardından, bu sanal sütunda bir dizin oluşturun.

Aşağıdaki örnek, dizin oluşturmak için kullanılabilecek hesaplanan bir sütun oluşturur. Ardından yeni hesaplanan sütunda bir dizin oluşturur. Bu örnek, JSON verilerindeki $.Customer.Name yolunda depolanan müşteri adını kullanıma sunan bir sütun oluşturur.

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

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)  

Bu ifade aşağıdaki uyarıyı döndürür:

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.

JSON_VALUE işlevi 8000 bayta kadar metin değeri döndürebilir (örneğin, nvarchar(4000) türü olarak). Ancak, 1700 bayttan uzun değerler dizine alınamaz. 1700 bayttan uzun olan dizinlenmiş hesaplanan sütuna değeri girmeye çalışırsanız, veri işleme dili (DML) işlemi başarısız olur.

Daha iyi performans için, hesaplanan bir sütun kullanarak ortaya çıkardığınız değeri en küçük geçerli veri türüne dönüştürmeyi deneyin. Dize türleri yerine int ve datetime2 türlerini kullanın.

Hesaplanan sütun hakkında daha fazla bilgi

Hesaplanan sütun kalıcı değildir. Bir bilgisayar sütunu yalnızca dizinin yeniden oluşturulması gerektiğinde hesaplanır. Tabloda ek alan kaplamaz.

Hesaplanan sütunu sorgularınızda kullanmayı planladığınız ifadeyle oluşturmanız önemlidir; bu örnekte ifade JSON_VALUE(Info, '$.Customer.Name').

Sorgularınızı yeniden yazmanız gerekmez. Yukarıdaki örnek sorguda gösterildiği gibi JSON_VALUE işleviyle ifadeler kullanırsanız, SQL Server aynı ifadeye sahip eşdeğer bir hesaplanan sütun olduğunu görür ve mümkünse bir dizin uygular.

Bu örnek için yürütme planı

Bu örnekteki sorgunun yürütme planı aşağıda verilmiştır.

TAM tablo taraması yerine SQL Server, kümelenmemiş dizinde bir dizin araması kullanır ve belirtilen koşulları karşılayan satırları bulur. Ardından sorguda başvuruda bulunan diğer sütunları getirmek için SalesOrderHeader tablosundaki bir anahtar aramasını kullanır; bu örnekte SalesOrderNumber ve OrderDate.

Dahil edilen sütunlarla dizini daha da iyileştirin

Dizine gerekli sütunları eklerseniz, tabloda bu ek aramayı önleyebilirsiniz. Yukarıdaki CREATE INDEX örneği genişleten aşağıdaki örnekte gösterildiği gibi, bu sütunları standart eklenen sütunlar olarak ekleyebilirsiniz.

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

Bu durumda, SQL Server'ın SalesOrderHeader tablosundaki ek verileri okuması gerekmez çünkü gerekli olan her şey kümelenmemiş JSON dizinine eklenir. Bu dizin türü, sorgulardaki JSON ve sütun verilerini birleştirmek ve iş yükünüz için en uygun dizinleri oluşturmak için iyi bir yoldur.

JSON dizinleri harmanlama kullanan dizinlerdir

JSON verilerine göre dizinlerin önemli bir özelliği, dizinlerin harmanlama duyarlı olmasıdır. Hesaplanan sütunu oluştururken kullandığınız JSON_VALUE işlevinin sonucu, harmanlamasını giriş ifadesinden devralan bir metin değeridir. Bu nedenle, dizindeki değerler kaynak sütunlarda tanımlanan harmanlama kuralları kullanılarak sıralanır.

Dizinlerin harmanlama duyarlı olduğunu göstermek için aşağıdaki örnek, birincil anahtar ve JSON içeriğine sahip basit bir koleksiyon tablosu oluşturur.

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)
 ) 

Yukarıdaki komut, json sütunu için Sırp Kiril harmanlamasını belirtir. Aşağıdaki örnek, tabloyu doldurur ve name özelliğinde bir dizin oluşturur.

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)

Yukarıdaki komutlar, vNamehesaplanan sütununda JSON $.name özelliğindeki değeri temsil eden standart bir dizin oluşturur. Sırp Kiril kod sayfasında, harflerin sırası А, Б, В, Г, Д, Ђ, Еvb. dizindeki öğelerin sırası Sırp Kiril kurallarıyla uyumludur çünkü JSON_VALUE işlevinin sonucu harmanlamasını kaynak sütundan devralır. Aşağıdaki örnek bu koleksiyonu sorgular ve sonuçları ada göre sıralar.

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

Gerçek yürütme planına bakarsanız, kümelenmemiş dizinden sıralanmış değerler kullandığını görürsünüz.

Sorgunun bir ORDER BY yan tümcesi olsa da yürütme planı Sort işleci kullanmaz. JSON dizini Sırp Kiril kurallarına göre zaten sıralanmış durumda. Bu nedenle SQL Server, sonuçların zaten sıralandığı kümelenmemiş dizini kullanabilir.

Ancak, ORDER BY ifadesinin harmanlamasını değiştirirseniz (örneğin, JSON_VALUE işlevinden sonra COLLATE French_100_CI_AS_SC eklerseniz) farklı bir sorgu yürütme planı elde edersiniz.

Dizindeki değerlerin sırası Fransızca harmanlama kurallarıyla uyumlu olmadığından SQL Server sonuçları sıralamak için dizini kullanamaz. Bu nedenle, Fransızca harmanlama kurallarını kullanarak sonuçları sıralayan bir Sıralama işleci ekler.

Microsoft videoları

SQL Server ve Azure SQL Veritabanı'nda yerleşik JSON desteğine görsel bir giriş için aşağıdaki videolara bakın:

• NoSQL ile ilişkisel dünyalar arasında bir köprü olarak JSON

İlgili içerik

• Bellek içi OLTP ile JSON işlemeyi iyileştirme
• SQL Server içindeki JSON verileri
• JSON veri türü