共用方式為


使用內建函數來驗證、查詢以及變更 JSON 資料 (SQL Server)

適用於: SQL Server 2016 (13.x) 和更新版本 Azure SQL 資料庫 Azure SQL 受控執行個體

內建的 JSON 支援包含下列在本文中簡述的內建功能。

  • ISJSON 可測試字串是否包含有效的 JSON。
  • JSON_VALUE 可從 JSON 字串擷取純量值。
  • JSON_QUERY 可從 JSON 字串擷取物件或陣列。
  • JSON_MODIFY 可更新 JSON 字串中的屬性值,並傳回更新後的 JSON 字串。

針對所有 JSON 函數,請詳閱 JSON 函數

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022AdventureWorksDW2022 範例資料庫,從 Microsoft SQL Server Samples 和 Community Projects (Microsoft SQL Server 範例和社群專案)首頁即可下載。

此頁面上之範例的 JSON 文字

此頁面上範例會使用類似於下列範例中所示內容的 JSON 文字:

{
    "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
}

此 JSON 文件 (包含巢狀複雜元素) 儲存在下列範例資料表中:

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

無論 JSON 文件是以 varcharnvarchar 或原生 json 資料類型儲存,JSON 函數的運作方式皆相同。

使用 ISJSON 函數來驗證 JSON 文字

ISJSON 函數可測試字串是否包含有效的 JSON。

下列範例會傳回 JSON 資料行包含有效 JSON 文字的資料列。 若無明確的 JSON 限制,您可以在 Nvarchar 資料行中輸入任何文字:

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

如需詳細資訊,請參閱 ISJSON

使用 JSON_VALUE 函數,從 JSON 文字中擷取值

JSON_VALUE 函數可從 JSON 字串擷取純量值。 下列查詢會傳回 id JSON 欄位符合值 DesaiFamily 且依據 citystate JSON 欄位排序的文件:

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

下表顯示此查詢的結果:

名稱 City 縣市
DesaiFamily NY Manhattan

如需詳細資訊,請參閱 JSON_VALUE

使用 JSON_QUERY 函數,從 JSON 文字擷取物件或陣列

JSON_QUERY 函數可從 JSON 字串擷取物件或陣列。 下列範例示範如何在查詢結果中傳回 JSON 片段。

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';

下表顯示此查詢的結果:

位址 Parents Parent0
{ "state": "NY", "county": "Manhattan", "city": "NY" } [ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ] { "familyName": "Desai", "givenName": "Prashanth" }

如需詳細資訊,請參閱 JSON_QUERY

剖析巢狀 JSON 集合

OPENJSON 函數可讓您將 JSON 子陣列轉換成資料列集,然後將其與父元素聯結。 例如,您可以傳回所有家族文件,並將其與儲存為內部 JSON 陣列的 children 物件「聯結」:

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

下表顯示此查詢的結果:

名稱 City givenName grade
DesaiFamily NY Jesse 1
DesaiFamily NY Lisa 8

系統會傳回兩個資料列,因為一個父資料列已聯結兩個子資料列,而這兩個子資料列是由剖析 children 子陣列的兩個元素所產生。 OPENJSON 函式會剖析 doc 資料行中的 children 片段,並以一組資料列形式傳回每個元素的 gradegivenName。 這個資料列集可以與父文件聯結。

查詢巢狀階層式 JSON 子陣列

您可以套用多個 CROSS APPLY OPENJSON 呼叫,以便查詢巢狀 JSON 結構。 此範例中所使用 JSON 文件具有稱為 children 的巢狀陣列,其中每個兒童都有 pets 的巢狀陣列。 下列查詢會剖析每個文件的 children,將每個陣列物件傳回為資料列,然後剖析 pets 陣列:

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;

第一個 OPENJSON 呼叫會使用 AS JSON 子句傳回 children 陣列的片段。 此陣列片段將提供給第二個 OPENJSON 函數,該函式會傳回每個兒童的 givenNamefirstName,以及 pets 的陣列。 pets 的陣列將提供給第三個 OPENJSON 函數,此函數會傳回寵物的 givenName

下表顯示此查詢的結果:

familyName childGivenName petName
Desai Jesse Goofy
Desai Jesse Shadow
Desai Lisa NULL

根文件會與第一個 OPENJSON(children) 呼叫所傳回的兩個 children 資料列聯結,形成兩個資料列 (或元組)。 然後,每個資料列會與 OPENJSON(pets) 使用 OUTER APPLY 運算子所產生的新資料列聯結。 Jesse 有兩隻寵物,因此 (Desai, Jesse) 會與針對 GoofyShadow 產生的兩個資料列聯結。 Lisa 沒有寵物,因此這個元組沒有 OPENJSON(pets) 傳回的任何資料列。 然而,因為我們使用 OUTER APPLY,所以將在資料行中取得 NULL。 如果我們使用 CROSS APPLY 而不是 OUTER APPLY,則結果中不會傳回 Lisa,因為沒有任何可與這個元組聯結的寵物資料列。

JSON_VALUE 與 JSON_QUERY 的比較

JSON_VALUEJSON_QUERY 的主要差別是 JSON_VALUE 傳回純量值,而 JSON_QUERY 傳回物件或陣列。

請參考下列 JSON 文字範例:

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

在這個 JSON 文字範例中,資料成員 "a" 和 "c" 為字串值,而資料成員 "b" 是陣列。 JSON_VALUEJSON_QUERY 傳回下列結果:

路徑 JSON_VALUE 傳回 JSON_QUERY 傳回
$ NULL 或錯誤 { "a": "[1,2]", "b": [1, 2], "c": "hi" }
$.a [1,2] NULL 或錯誤
$.b NULL 或錯誤 [1,2]
$.b[0] 1 NULL 或錯誤
$.c hi NULL 或錯誤

使用 AdventureWorks 範例資料庫,測試 JSON_VALUE 與 JSON_QUERY

依據本文中所述,使用 AdventureWorks2022 範例資料庫,執行下列範例,以測試內建函數。 如需有關如何新增 JSON 資料以供測試的詳細資訊,請參閱測試磁碟機內建的 JSON 支援

在下列範例中,SalesOrder_json資料表中的 Info 資料行包含 JSON 文字。

範例 1 - 傳回標準的資料行和 JSON 資料

下列查詢會傳回來自標準關聯式資料行及 JSON 資料行的值。

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;

範例 2 - 彙總並篩選 JSON 值

下列查詢會依據客戶名稱和狀態來彙總小計 (客戶名稱是儲存在 JSON 中,而狀態是儲存在一般資料行中)。 然後依縣市 (儲存在 JSON 中) 及 OrderDate (儲存在一般資料行中) 篩選結果。

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 函數來更新 JSON 文字中的屬性值

JSON_MODIFY 函數會更新 JSON 字串中屬性的值,並傳回更新的 JSON 字串。

下列範例會更新包含 JSON 的變數中,JSON 屬性的值。

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

如需詳細資訊,請參閱 JSON_MODIFY