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

發行項
05/22/2024

適用於： 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 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 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 文件是以 varchar、nvarchar 或原生 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 且依據 city 和 state 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 片段，並以一組資料列形式傳回每個元素的 grade 和 givenName。這個資料列集可以與父文件聯結。

查詢巢狀階層式 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 函數，該函式會傳回每個兒童的 givenName、firstName，以及 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) 會與針對 Goofy 和 Shadow 產生的兩個資料列聯結。 Lisa 沒有寵物，因此這個元組沒有 OPENJSON(pets) 傳回的任何資料列。然而，因為我們使用 OUTER APPLY，所以將在資料行中取得 NULL。如果我們使用 CROSS APPLY 而不是 OUTER APPLY，則結果中不會傳回 Lisa，因為沒有任何可與這個元組聯結的寵物資料列。

JSON_VALUE 與 JSON_QUERY 的比較

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

請參考下列 JSON 文字範例：

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

在這個 JSON 文字範例中，資料成員 "a" 和 "c" 為字串值，而資料成員 "b" 是陣列。 JSON_VALUE 並 JSON_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。

共用方式為

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

此頁面上之範例的 JSON 文字

使用 ISJSON 函數來驗證 JSON 文字

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

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

剖析巢狀 JSON 集合

查詢巢狀階層式 JSON 子陣列

JSON_VALUE 與 JSON_QUERY 的比較

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

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

範例 2 - 彙總並篩選 JSON 值

使用 JSON_MODIFY 函數來更新 JSON 文字中的屬性值

意見反應

其他資源

共用方式為

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

此頁面上之範例的 JSON 文字

使用 ISJSON 函數來驗證 JSON 文字

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

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

剖析巢狀 JSON 集合

查詢巢狀階層式 JSON 子陣列

JSON_VALUE 與 JSON_QUERY 的比較

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

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

範例 2 - 彙總並篩選 JSON 值

使用 JSON_MODIFY 函數來更新 JSON 文字中的屬性值

相關內容

意見反應

其他資源