Yerleşik işlevlerle JSON verilerini doğrulama, sorgulama ve değiştirme

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümler Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

SQL Veritabanı Altyapısı'ndaki JSON için yerleşik destek aşağıdaki işlevleri içerir:

• ISJSON , bir dizenin geçerli JSON içerip içermediğini test ediyor.
• JSON_VALUE bir JSON dizesinden skaler değer ayıklar.
• JSON_QUERY bir JSON dizesinden bir nesne veya dizi ayıklar.
• JSON_MODIFY bir JSON dizesindeki bir özelliğin değerini güncelleştirir ve güncelleştirilmiş JSON dizesini döndürür.

Tüm JSON işlevleri için JSON işlevlerini (Transact-SQL) gözden geçirin.

Bu makaledeki kod örnekleri, AdventureWorks2025 giriş sayfasından indirebileceğiniz AdventureWorksDW2025 veya örnek veritabanını kullanır.

Bu sayfadaki örnekler için JSON metni

Bu sayfadaki örneklerde, aşağıdaki örnekte gösterilen içeriğe benzer JSON metni kullanılır:

{
    "id": "DesaiFamily",
    "parents": [
        { "familyName": "Desai", "givenName": "Prashanth" },
        { "familyName": "Miller", "givenName": "Helen" }
    ],
    "children": [
        {
            "familyName": "Desai",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1,
            "pets": [
                { "givenName": "Goofy" },
                { "givenName": "Shadow" }
            ]
        },
        {
            "familyName": "Desai",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
    ],
    "address": {
        "state": "NY",
        "county": "Manhattan",
        "city": "NY"
    },
    "creationDate": 1431620462,
    "isRegistered": false
}

İç içe karmaşık öğeler içeren bu JSON belgesi aşağıdaki örnek tabloda depolanır:

CREATE TABLE Families (
    id INT identity CONSTRAINT PK_JSON_ID PRIMARY KEY,
    [doc] NVARCHAR(MAX)
);

JSON işlevleri, JSON belgesinin varchar, nvarchar veya yerel json veri türünde depolanmasıyla aynı şekilde çalışır.

ISJSON işlevini kullanarak JSON metnini doğrulama

İşlev, bir dizenin ISJSON geçerli JSON içerip içermediğini sınar.

Aşağıdaki örnek, JSON sütununun geçerli JSON metni içerdiği satırları döndürür. Açık JSON kısıtlaması olmadan nvarchar sütununa herhangi bir metin girebilirsiniz:

SELECT *
FROM Families
WHERE ISJSON(doc) > 0;

Daha fazla bilgi için bkz . ISJSON (Transact-SQL).

JSON_VALUE işlevini kullanarak JSON metninden değer ayıklama

JSON_VALUE işlevi bir JSON dizesinden skaler bir değer ayıklar. Aşağıdaki sorgu, `id` JSON alanının `DesaiFamily` değerine eşleştiği belgeleri döndürür ve sonuçları `city` ve `state` JSON alanlarına göre sıralar.

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC,
    JSON_VALUE(f.doc, '$.address.state') ASC

Bu sorgunun sonuçları aşağıdaki tabloda gösterilmiştir:

Name	City	County
DesaiFamily	NY	Manhattan

Daha fazla bilgi için bkz. JSON_VALUE.

JSON_QUERY işlevini kullanarak JSON metninden nesne veya dizi ayıklama

İşlev, JSON_QUERY JSON dizesinden bir nesne veya dizi ayıklar. Aşağıdaki örnekte, sorgu sonuçlarında JSON parçasının nasıl döndürüleceği gösterilmektedir.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
    JSON_QUERY(f.doc, '$.parents') AS Parents,
    JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily';

Bu sorgunun sonuçları aşağıdaki tabloda gösterilmiştir:

Address	Parents	Ana0
{ "state": "NY", "county": "Manhattan", "city": "NY" }	[ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ]	{ "familyName": "Desai", "givenName": "Prashanth" }

Daha fazla bilgi için bkz. JSON_QUERY.

İç içe JSON koleksiyonlarını ayrıştırma

OPENJSON işlevi, JSON alt dizisini satır kümesine dönüştürmenizi ve ardından üst öğeyle birleştirmenizi sağlar. Örneğin, tüm aile belgelerini döndürebilir ve bunları iç JSON dizisi olarak depolanan nesneleriyle children "birleştirebilirsiniz":

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    c.givenName,
    c.grade
FROM Families f
CROSS APPLY OPENJSON(f.doc, '$.children') WITH (
    grade INT,
    givenName NVARCHAR(100)
) c

Bu sorgunun sonuçları aşağıdaki tabloda gösterilmiştir:

Name	City	givenName	grade
DesaiFamily	NY	Jesse	1
DesaiFamily	NY	Lisa	8

Bir ebeveyn satır, alt dizinin iki öğesi ayrıştırılarak üretilen iki alt satırla birleştirildiğinden iki satır döndürülür. OPENJSON işlevi, children sütunundaki parçayı ayrıştırır ve her öğeden doc ile grade'ü birer satır kümesi olarak döndürür. Bu satır kümesi üst belgeyle birleştirilebilir.

İç içe hiyerarşik JSON alt dizilerini sorgulama

İç içe JSON yapılarını sorgulamak için birden çok CROSS APPLY OPENJSON çağrı uygulayabilirsiniz. Bu örnekte kullanılan JSON belgesinde, her biri children adında iç içe geçmiş bir diziye sahip olan pets adlı iç içe geçmiş bir dizi bulunmaktadır. Aşağıdaki sorgu, her belgedeki alt öğeleri ayrıştırır, ardından her dizi nesnesini satır olarak döndürür ve pets dizisini ayrıştırır.

SELECT c.familyName,
    c.givenName AS childGivenName,
    p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (
    familyName NVARCHAR(100),
    children NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY OPENJSON(children) WITH (
    familyName NVARCHAR(100),
    givenName NVARCHAR(100),
    pets NVARCHAR(max) AS JSON
) AS c
OUTER APPLY OPENJSON(pets) WITH (givenName NVARCHAR(100)) AS p;

İlk OPENJSON çağrı children dizisinin bir parçasını AS JSON yan tümcesini kullanarak döndürür. Bu dizi bölümü, OPENJSON döndüren ikinci givenName işlevi, her alt öğenin firstName ve ayrıca pets dizisini sağlar. pets dizisi, evcil hayvanın OPENJSON döndüren üçüncü givenName fonksiyonuna sağlanır.

Bu sorgunun sonuçları aşağıdaki tabloda gösterilmiştir:

familyName	ÇocukAdı	petName
Desai	Jesse	Goofy
Desai	Jesse	Shadow
Desai	Lisa	NULL

Kök belge, ilk children çağrısı tarafından döndürülen iki OPENJSON(children) satırla (veya kayıt) birleştirilerek iki satır (veya kayıt) oluşturur. Daha sonra, her satır OPENJSON(pets) tarafından oluşturulan yeni satırlarla OUTER APPLY işleci kullanılarak birleştirilir. Jesse'nin iki evcil hayvanı vardır, bu nedenle (Desai, Jesse) ve Goofyiçin Shadow oluşturulan iki satırla birleştirilir. Lisa'nın evcil hayvanı yok, bu yüzden OPENJSON(pets) tarafından bu tanım grubu için satır döndürülmüyor. Ancak OUTER APPLY kullandığımızda sütunda NULL elde ediyoruz. Eğer CROSS APPLY yerine OUTER APPLY koyarsak, bu tanımlama kümesiyle birleştirilebilen evcil hayvan satırları olmadığından Lisa sonuçlarda yer almayacaktır.

JSON_VALUE ve JSON_QUERY karşılaştırma

JSON_VALUE ve JSON_QUERY arasındaki temel fark, JSON_VALUE bir skaler değer döndürürken JSON_QUERY bir nesne veya dizi döndürmesidir.

Aşağıdaki örnek JSON metnini göz önünde bulundurun.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}

Bu örnek JSON metninde veri üyeleri "a" ve "c" dize değerleridir, "b" veri üyesi ise bir dizidir. JSON_VALUE ve JSON_QUERY aşağıdaki sonuçları döndür:

Path	JSON_VALUE sonucu verilir	JSON_QUERY sonucu verilir
$	NULL veya bir hatası	{ "a": "[1,2]", "b": [1, 2], "c": "hi" }
$.a	[1,2]	NULL veya bir hatası
$.b	NULL veya bir hatası	[1,2]
$.b[0]	1	NULL veya bir hatası
$.c	hi	NULL veya bir hatası

AdventureWorks örnek veritabanıyla JSON_VALUE ve JSON_QUERY test edin

Örnek veritabanıyla aşağıdaki örnekleri çalıştırarak bu makalede açıklanan yerleşik işlevleri test edin AdventureWorks2025 . Betik çalıştırarak test için JSON verileri ekleme hakkında daha fazla bilgi için bkz Yerleşik JSON destek test etme sürümü.

Aşağıdaki örneklerde, tablodaki InfoSalesOrder_json sütun JSON metni içerir.

Örnek 1 - Hem standart sütunları hem de JSON verilerini döndürme

Aşağıdaki sorgu hem standart ilişkisel sütunlardan hem de bir JSON sütunundan değerler döndürür.

SELECT SalesOrderNumber,
    OrderDate,
    Status,
    ShipDate,
    AccountNumber,
    TotalDue,
    JSON_QUERY(Info, '$.ShippingInfo') ShippingInfo,
    JSON_QUERY(Info, '$.BillingInfo') BillingInfo,
    JSON_VALUE(Info, '$.SalesPerson.Name') SalesPerson,
    JSON_VALUE(Info, '$.ShippingInfo.City') City,
    JSON_VALUE(Info, '$.Customer.Name') Customer,
    JSON_QUERY(OrderItems, '$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0;

Örnek 2- JSON değerlerini toplama ve filtreleme

Aşağıdaki sorgu, alt toplamları müşteri adına (JSON'da depolanır) ve duruma (sıradan bir sütunda depolanır) göre toplar. Ardından sonuçları şehre (JSON'da depolanır) ve OrderDate'a (sıradan bir sütunda depolanır) göre filtreler.

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid = 3;
SET @city = N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer,
    Status,
    SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID = @territoryid
    AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
    AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'),
    Status
HAVING SUM(SubTotal) > 1000;

JSON_MODIFY işlevini kullanarak JSON metnindeki özellik değerlerini güncelleştirme

İşlev, JSON_MODIFY JSON dizesindeki bir özelliğin değerini güncelleştirir ve güncelleştirilmiş JSON dizesini döndürür.

Aşağıdaki örnek, JSON içeren bir değişkendeki JSON özelliğinin değerini güncelleştirir.

SET @info = JSON_MODIFY(@jsonInfo, '$.info.address[0].town', 'London');

Daha fazla bilgi için bkz. JSON_MODIFY.

İlgili içerik

• ISJSON (Transact-SQL)
• JSON_VALUE (Transact-SQL)
• JSON_QUERY (Transact-SQL)
• JSON_MODIFY (Transact-SQL)
• JSON Yol İfadeleri
• JSON veri türü