Yerleşik işlevlerle JSON verilerini işleme
E-ticaret uygulamanızın müşteri tercihlerini ve sipariş meta verilerini JSON belgeleri olarak depoladığı bir senaryo düşünün. Mobil uygulama alışveriş sepeti verilerini JSON biçiminde gönderir ve raporlama sisteminizin ürün kataloglarını bir web API'si için JSON olarak dışarı aktarması gerekir. Veritabanınızda doğrudan JSON ile çalışmak, uygulama katmanı dönüştürme gereksinimini ortadan kaldırır ve veri işlemenizi verimli tutar.
Doku'daki SQL Server, Azure SQL ve SQL veritabanları, JSON verilerini doğrudan T-SQL'de ayrıştırmanıza, sorgulamanıza, oluşturmanıza ve dönüştürmenize olanak tanıyan yerleşik JSON desteği sağlar. Bu ünitede değerleri ayıklamak, JSON çıktısı oluşturmak, verileri JSON dizilerinde toplamak ve JSON içeriğini doğrulamak için JSON işlevlerini kullanmayı öğreneceksiniz.
JSON_VALUE ve JSON_QUERY ile değerleri ayıkla
Veritabanınızda depolanan JSON ile çalışırken filtreleme, birleştirme veya görüntüleme için belirli değerleri ayıklamanız gerekir. SQL Server bu amaçla iki işlev sağlar:
JSON_VALUE() bir JSON dizesinden bir skaler değer (dize, sayı, boole) ayıklar:
DECLARE @json NVARCHAR(MAX) = N'{
"customer": {
"id": 12345,
"name": "Contoso Ltd",
"active": true
},
"orderTotal": 1599.99
}';
SELECT
JSON_VALUE(@json, '$.customer.id') AS CustomerID,
JSON_VALUE(@json, '$.customer.name') AS CustomerName,
JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;
Sonuç kümesi şöyle olacaktır:
CustomerID CustomerName OrderTotal
---------- ------------ ----------
12345 Contoso Ltd 1599.99
İşlev, yol ifadesini kullanarak JSON yapısında gezinerek değeri dize NVARCHAR(4000) olarak döndürür. Hesaplamalar veya karşılaştırmalar için gerektiğinde sonucu diğer veri türlerine dönüştürebilirsiniz.
JSON_QUERY() bir JSON nesnesini veya dizisini (nonscalar değerler) ayıklar:
DECLARE @json NVARCHAR(MAX) = N'{
"customer": {
"id": 12345,
"name": "Contoso Ltd"
},
"items": [
{"product": "Widget", "qty": 5},
{"product": "Gadget", "qty": 3}
]
}';
SELECT
JSON_QUERY(@json, '$.customer') AS CustomerObject,
JSON_QUERY(@json, '$.items') AS ItemsArray;
Sonuç kümesi şöyle olacaktır:
CustomerObject ItemsArray
-------------------------------------- ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"} [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]
aksine JSON_VALUE(), JSON_QUERY() JSON yapısını korur, nesneleri ve dizileri depolayabileceğiniz, diğer işlevlere geçirebileceğiniz veya uygulamalara döndürebileceğiniz geçerli JSON dizeleri olarak döndürür.
Yol ifadesi, kök öğeyi temsil etmek için $ kullanır; iç içe geçmiş özellikler için nokta gösterimi ve aşağıdaki örnekte olduğu gibi dizi öğeleri için köşeli parantez gösterimi kullanılır.
-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;
Sonuç şu şekilde olur:
FirstProduct
------------
Widget
Dizi dizinleri 0'da başlar, bu nedenle $.items[0] ilk öğeye başvurur. Belirli öğeleri konumlarını bildiğinizde ayıklamak için veya tüm dizi öğelerini işlemeniz gerektiğinde OPENJSON ile birleştirmek için bu söz dizimini kullanın.
Tavsiye
Karşılaştırmalar veya hesaplamalar için skaler değere ihtiyacınız olduğunda kullanın JSON_VALUE() . İç içe nesnelerin veya dizilerin JSON yapısını korumanız gerektiğinde kullanın JSON_QUERY() .
JSON dizilerini ile ayrıştırma OPENJSON
OPENJSON , JSON verilerini ilişkisel satır kümesine dönüştüren tablo değerli bir işlevdir. JSON verilerini ilişkisel tablolarla birleştirmek veya dizi öğelerini ayrı ayrı işlemek için bu işlevi kullanın.
Aşağıdaki sorgu bir JSON dizisini varsayılan şemaya sahip satırlar halinde ayrıştırmaktadır:
DECLARE @json NVARCHAR(MAX) = N'[
{"id": 1, "name": "Widget", "price": 29.99},
{"id": 2, "name": "Gadget", "price": 49.99},
{"id": 3, "name": "Gizmo", "price": 19.99}
]';
SELECT * FROM OPENJSON(@json);
Sonuç kümesi şöyle olacaktır:
key value type
--- -------------------------------------------- ----
0 {"id": 1, "name": "Widget", "price": 29.99} 5
1 {"id": 2, "name": "Gadget", "price": 49.99} 5
2 {"id": 3, "name": "Gizmo", "price": 19.99} 5
Şema olmadan üç OPENJSON sütun döndürür: key (dizi dizini veya özellik adı), value (JSON içeriği) ve type (JSON veri türünü gösteren sayı: 0=null, 1=dize, 2=sayı, 3=boolean, 4=dizi, 5=nesne).
Aşağıdaki sorgu, uygun veri türlerine sahip belirli sütunları ayıklamak için açık bir şema tanımlar:
SELECT
ProductID,
ProductName,
Price
FROM OPENJSON(@json)
WITH (
ProductID INT '$.id',
ProductName NVARCHAR(100) '$.name',
Price DECIMAL(10,2) '$.price'
);
Sonuç kümesi şöyle olacaktır:
ProductID ProductName Price
--------- ----------- ------
1 Widget 29.99
2 Gadget 49.99
3 Gizmo 19.99
WITH yan tümcesi, JSON özelliklerini tür atanmış sütunlara eşler. Bu yaklaşım, hesaplamalar ve karşılaştırmalar için uygun veri türlerini sağlar ve yalnızca ihtiyacınız olan özellikleri seçmenize olanak tanır.
Tablo verileriyle OPENJSON kullanarak birleşim yapınCROSS APPLY.
-- Assuming Orders table has a JSON column called OrderDetails
SELECT
o.OrderID,
o.CustomerID,
items.ProductName,
items.Quantity,
items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
ProductName NVARCHAR(100) '$.product',
Quantity INT '$.qty',
UnitPrice DECIMAL(10,2) '$.price'
) AS items;
Uyarı
OPENJSON ile CROSS APPLY kullanıldığında, ana tablodaki NULL değerine sahip olan veya JSON değerleri boş olan satırlar sonuçlarda görünmez. JSON verileri olmayan satırları eklemeniz gerekiyorsa kullanın OUTER APPLY .
JSON_OBJECT ve JSON_ARRAY ile JSON oluşturma
SQL Server 2022'nin kullanımına sunulan JSON_OBJECT ve JSON_ARRAY sezgisel JSON oluşturma işlevleri:
JSON_OBJECT() anahtar-değer çiftlerinden bir JSON nesnesi oluşturur, aşağıdaki örnekte bir ürün için JSON nesnesinin nasıl derlenmiyor olduğu gösterilmektedir:
SELECT JSON_OBJECT(
'id': ProductID,
'name': Name,
'price': ListPrice,
'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;
Sonuç şu şekilde olur:
ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}
İşlev, veri türü dönüştürmeyi ve dize değerlerindeki özel karakterler için uygun JSON kaçışını otomatik olarak işler.
JSON_ARRAY() değerlerden bir JSON dizisi oluşturur, aşağıdaki örnek bir JSON dizisi oluşturur:
SELECT JSON_ARRAY(
'SQL Server',
'Azure SQL Database',
'SQL Database in Fabric'
) AS Platforms;
Sonuç şu şekilde olur:
Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]
sütun değerlerini, değişkenleri veya değişmez değerleri öğesine JSON_ARRAY()geçirebilirsiniz. İşlev, giriş türlerinden bağımsız olarak düzgün biçimlendirilmiş bir JSON dizisi oluşturur.
Ardından iç içe JSON yapıları oluşturmak için bu işlevleri birleştirin. Aşağıdaki örnek, müşteri ve toplam bilgileriyle eksiksiz bir sipariş JSON nesnesi oluşturur:
SELECT JSON_OBJECT(
'orderId': soh.SalesOrderID,
'orderDate': soh.OrderDate,
'customer': JSON_OBJECT(
'id': c.CustomerID,
'name': c.CompanyName
),
'totals': JSON_OBJECT(
'subtotal': soh.SubTotal,
'tax': soh.TaxAmt,
'total': soh.TotalDue
)
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;
Sonuç şu şekilde olur:
OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}
İç içe JSON_OBJECT çağrılar, uygulamanızın beklenen biçimiyle eşleşen hiyerarşik yapılar oluşturur. Bu yaklaşım dize birleştirmeden daha temizdir ve geçerli JSON çıkışı sağlar.
JSON_ARRAYAGG ile veri birleştirme
JSON_ARRAYAGG birden çok satırdan değerleri tek bir JSON dizisine toplar. Bu işlev normalleştirilmiş ilişkisel verilerden normalleştirilmiş JSON çıkışı oluşturmak için kullanışlıdır:
SELECT
c.CustomerID,
c.CompanyName,
JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;
Sonuç şu şekilde olur:
CustomerID CompanyName OrderIds
---------- ------------------- ------------------
29825 Contoso Retail [71774,71776,71780]
29847 Adventure Works [71782,71784]
işlevi, gruplandırılmış satırlardan tüm eşleşen değerleri toplar ve bunları tek bir JSON dizisinde birleştirir. Bu, normalleştirilmiş veritabanı tablolarından denormalize edilmiş API yanıtlarını oluşturmak için kullanışlıdır.
JSON_ARRAYAGG ve JSON_OBJECT ile karmaşık nesne dizileri oluşturabilirsiniz.
SELECT
pc.Name AS Category,
JSON_ARRAYAGG(
JSON_OBJECT(
'id': p.ProductID,
'name': p.Name,
'price': p.ListPrice
)
) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;
Aşağıdaki sonuç şöyle olacaktır:
Category Products
-------------- --------------------------------------------------------------------------
Road Bikes [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]
Önemli
JSON_ARRAYAGG ve JSON_OBJECT/JSON_ARRAY işlevleri SQL Server 2022 ve sonraki sürümlerde, Azure SQL Veritabanı'nda ve Microsoft Fabric'teki SQL veritabanlarında kullanılabilir. Önceki sürümler için benzer işlevler için kullanın FOR JSON PATH .
JSON_CONTAINS ile JSON doğrulayın ve denetleyin
Dış kaynaklardan gelen JSON verileri hatalı biçimlendirilmiş, beklenen özellikler eksik veya beklenmeyen değerler içerebilir. Geçersiz JSON'dan veya eksik yollardan değer ayıklamaya çalışmak sorgu hatalarına neden olabilir veya veri sorunlarını maskeleyen NULL yanıltıcı sonuçlar döndürebilir.
Sağlam JSON işleme için savunma kodlaması gerekir: JSON'un ayrıştırmadan önce iyi biçimlendirilmiş olduğunu doğrulayın, değerleri ayıklamadan önce beklenen yolların mevcut olup olmadığını denetleyin ve iş mantığında kullanmadan önce değerlerin beklentilerinizle eşleşip eşleşmediğini doğrulayın. SQL Server, işlemenin her aşamasında JSON içeriğini doğrulamanıza yardımcı olan çeşitli işlevler sağlar.
Gevşek ve katı yol modlarını anlama
JSON yol ifadelerini, hata işlemeyi denetleen iki modda kullanabilirsiniz:
DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';
-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;
-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;
Sonuç şu şekilde olur:
LaxResult
---------
NULL
-- Strict mode raises: Property cannot be found on the specified JSON path.
Eksik özelliklerin beklenmesi ve NULL döndürmesi gerektiğinde, varsayılan olan lax modu kullanın. Eksik özellikler bir veri sorununu işaret ettiğinde ve hataya neden olması gerektiğinde strict modunu kullanın.
ISJSON bir dizenin geçerli JSON içerip içermediğini doğrular. Aşağıdaki örnekte ISJSONnasıl kullanılacağı gösterilmektedir:
SELECT
ISJSON('{"name": "test"}') AS ValidJson, -- Returns 1
ISJSON('not valid json') AS InvalidJson, -- Returns 0
ISJSON(NULL) AS NullJson; -- Returns NULL
Sonuç şu şekilde olur:
ValidJson InvalidJson NullJson
--------- ----------- --------
1 0 NULL
ISJSON içeren yan tümcelerle geçerli JSON içeren satırları filtrelemek veya WHERE ifadelerini kullanarak geçersiz verileri düzgün bir şekilde işlemek için kullanın CASE.
JSON_PATH_EXISTS aşağıdaki örnekte olduğu gibi JSON belgesinde belirli bir yolun var olup olmadığını denetler:
DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';
SELECT
JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;
Sonuç şu şekilde olur:
HasName HasEmail
------- --------
1 0
Bu işlev, yol varsa 1, yoksa 0 döndürür. Kullanın, katı modda JSON_VALUE çağırmadan önce veya JSON'ı farklı yapılarla koşullu olarak işlemek için.
JSON belgesinin aşağıdaki örnekte olduğu gibi belirli bir değer veya nesne içerip içermediğini denetlemek için kullanın JSON_CONTAINS :
DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';
SELECT
JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;
Sonuç şu şekilde olur:
HasSqlTag HasPythonTag
--------- ------------
1 0
Hesaplanan sütunlarla JSON sorgularını iyileştirme
Belirli JSON özelliklerini sık sık sorguladığınızda, veritabanı altyapısının her sorgudaki her satır için JSON belgesini ayrıştırması gerekir. Binlerce veya milyonlarca satırı olan tablolar için bu yinelenen ayrıştırma önemli ek yük oluşturur. Hesaplanan sütunlar, JSON değerlerini bir kez ayıklamanıza ve bunları dizin oluşturmayı destekleyen sorgulanabilir bir biçimde depolamanıza olanak sağlar.
JSON ayrıştırma performansı neden etkiler?
Her satırın ürün özniteliklerine sahip bir JSON belgesi içerdiği 100.000 ürün kaydına sahip bir tablo düşünün. Kategoriye göre filtrelenmesi gereken bir sorgu
- Tablodaki her satırı oku
- Kategori özelliğini bulmak için JSON belgesini ayrıştırma
- Değeri ayıkla ve karşılaştır
İyileştirme olmadan, basit filtreler bile her satırda JSON ayrıştırma ile tam tablo taramaları gerektirir.
JSON özellikleri için hesaplanan sütunlar oluşturma
Hesaplanan sütun, bir JSON özelliğini otomatik olarak ayıklar ve aşağıdaki örnekte olduğu gibi normal bir sütun olarak kullanılabilir hale getirir:
-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');
-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';
Sonuç şu şekilde olur:
ProductID ProductName ProductCategory
--------- ------------------- ---------------
101 Wireless Mouse Electronics
102 USB Keyboard Electronics
103 HD Monitor Electronics
Varsayılan olarak, hesaplanan sütunlar sanaldır. Veritabanı sorgu zamanında değeri hesaplar ancak JSON ayıklamasını iyileştirebilir. Daha da iyi performans için aşağıdaki örnekte olduğu gibi hesaplanan sütunu kalıcı hale ekleyebilirsiniz:
-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;
Kalıcı sütunlar ayıklanan değeri diskte depolar, bu nedenle JSON, sadece INSERT ve UPDATE işlemleri sırasında SELECT ayrıştırılır, sorgular sırasında değil.
Daha hızlı filtreleme için dizin ekleme
Gerçek performans kazancı, hesaplanan sütunları dizinlemeden gelir:
-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);
-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';
Dizin olmadan, sorgu 100.000 satırın tümünü tarar. Dizinle, sorgu altyapısı bir dizin araması gerçekleştirir ve yalnızca eşleşen satırları alır. Bu, sorgu süresini saniyelerden milisaniyeye düşürebilir.
Birden çok JSON özelliğini dizine ekleme
Birden çok JSON özelliğini filtreleyen sorgular için hesaplanan sütunlar ve bileşik dizin oluşturun:
-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;
-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);
-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);
Artık kategoriye ve markaya göre filtreleme veya fiyata göre sıralama sorguları bu dizinleri verimli bir şekilde kullanabilir.
Tavsiye
Sık erişilen JSON özellikleri için dizinleri olan hesaplanan sütunlar, sorgu zamanında JSON ayrıştırma ile karşılaştırıldığında sorgu performansını iyileştirebilir. Sorgu desenlerinizi izleyin ve WHERE, JOIN veya ORDER BY yan tümcelerinde kullanılan özellikler için hesaplanan sütunlar oluşturun.
FOR JSON ile ilişkisel verileri JSON'a dönüştürme
Sorgulardan kapsamlı JSON çıkışı almak için FOR JSON PATH veya FOR JSON AUTO kullanın.
SELECT
p.ProductID,
p.Name,
p.ListPrice,
pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');
Sonuç şu şekilde olur:
{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}
FOR JSON PATH sütun diğer adları aracılığıyla JSON yapısı üzerinde denetim sağlar. İç içe nesneler oluşturmak için diğer adlarda nokta gösterimi kullanın:
SELECT
p.ProductID AS 'product.id',
p.Name AS 'product.name',
pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;
Sonuç şu şekilde olur:
[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]
Sütun takma adı 'product.id', id özelliğine sahip product iç içe bir nesne oluşturur. Bu teknik, çıktıyı işlem sonrası olmadan API'nizin beklenen biçimiyle eşleşecek şekilde şekillendirmenize olanak tanır.
SQL Server'daki JSON işlevleri hakkında daha fazla bilgi için bkz. SQL Server'da JSON verileri ve JSON İşlevleri.