使用內建函數，驗證、查詢以及變更 JSON 資料 (SQL Server)Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)

06/03/2020

適用範圍：Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本適用範圍：Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later

內建的 JSON 支援包含下列在此主題中簡述的內建功能。The built-in support for JSON includes the following built-in functions described briefly in this topic.

ISJSON 可測試字串是否包含有效的 JSON。ISJSON tests whether a string contains valid JSON.
JSON_VALUE 可從 JSON 字串擷取純量值。JSON_VALUE extracts a scalar value from a JSON string.
JSON_QUERY 可從 JSON 字串擷取物件或陣列。JSON_QUERY extracts an object or an array from a JSON string.
JSON_MODIFY 可更新 JSON 字串中的屬性值，並傳回更新後的 JSON 字串。JSON_MODIFY updates the value of a property in a JSON string and returns the updated JSON string.

此頁面上之範例的 JSON 文字JSON text for the examples on this page

此頁面上範例會使用類似於下列範例中所示內容的 JSON 文字：The examples on this page use the JSON text similar to the content shown in the following example:

{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

此 JSON 文件 (包含巢狀複雜元素) 儲存在下列範例資料表中：This JSON document, which contains nested complex elements, is stored in the following sample table:

CREATE TABLE Families (
   id int identity constraint PK_JSON_ID primary key,
   doc nvarchar(max)
)

使用 ISJSON 函數來驗證 JSON 文字Validate JSON text by using the ISJSON function

ISJSON 函數可測試字串是否包含有效的 JSON。The ISJSON function tests whether a string contains valid JSON.

下列範例會傳回 JSON 資料行包含有效 JSON 文字的資料列。The following example returns rows in which the JSON column contains valid JSON text. 請注意，如果沒有明確的 JSON 限制，您可以在 NVARCHAR 資料行中輸入任何文字：Note that without explicit JSON constraint, you can enter any text in the NVARCHAR column:

SELECT *
FROM Families
WHERE ISJSON(doc) > 0

如需詳細資訊，請參閱 ISJSON (Transact-SQL)。For more info, see ISJSON (Transact-SQL).

使用 JSON_VALUE 函數，從 JSON 文字中擷取值Extract a value from JSON text by using the JSON_VALUE function

JSON_VALUE 函數可從 JSON 字串擷取純量值。The JSON_VALUE function extracts a scalar value from a JSON string. 下列查詢會傳回 id JSON 欄位符合值 AndersenFamily 且依據 city 和 state JSON 欄位排序的文件：The following query will return the documents where the id JSON field matches the value AndersenFamily, ordered by city and state JSON fields:

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'AndersenFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC, JSON_VALUE(f.doc, '$.address.state') ASC

下表顯示此查詢的結果：The results of this query are shown in the following table:

名稱Name	CityCity	郡/縣County
AndersenFamilyAndersenFamily	NYNY	ManhattanManhattan

如需詳細資訊，請參閱 JSON_VALUE (Transact-SQL)。For more info, see JSON_VALUE (Transact-SQL).

使用 JSON_QUERY 函數，從 JSON 文字擷取物件或陣列Extract an object or an array from JSON text by using the JSON_QUERY function

JSON_QUERY 函數可從 JSON 字串擷取物件或陣列。The JSON_QUERY function extracts an object or an array from a JSON string. 下列範例示範如何在查詢結果中傳回 JSON 片段。The following example shows how to return a JSON fragment in query results.

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

下表顯示此查詢的結果：The results of this query are shown in the following table:

位址Address	ParentsParents	Parent0Parent0
{ "state":"NY", "county":"Manhattan", "city":"NY" }{ "state": "NY", "county": "Manhattan", "city": "NY" }	[{ "familyName":"Wakefield", "givenName":"Robin" }, {"familyName":"Miller", "givenName":"Ben" } ][{ "familyName": "Wakefield", "givenName": "Robin" }, {"familyName": "Miller", "givenName": "Ben" } ]	{ "familyName":"Wakefield", "givenName":"Robin" }{ "familyName": "Wakefield", "givenName": "Robin" }

如需詳細資訊，請參閱 JSON_QUERY (Transact-SQL)。For more info, see JSON_QUERY (Transact-SQL).

剖析巢狀 JSON 集合Parse nested JSON collections

OPENJSON 函式可讓您將 JSON 子陣列轉換成資料列集，然後將其與父元素聯結。OPENJSON function enables you to transform JSON sub-array into the rowset and then join it with the parent element. 例如，您可以傳回所有家族文件，並將其與儲存為內部 JSON 陣列的 children 物件「聯結」：As an example, you can return all family documents, and "join" them with their children objects that are stored as an inner JSON array:

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

下表顯示此查詢的結果：The results of this query are shown in the following table:

名稱Name	CityCity	givenNamegivenName	gradegrade
AndersenFamilyAndersenFamily	NYNY	JesseJesse	11
AndersenFamilyAndersenFamily	NYNY	LisaLisa	88

我們會取得兩個資料列，因為一個父資料列已聯結兩個子資料列，而這兩個子資料列是由剖析 children 子陣列的兩個元素所產生。We are getting two rows as a result because one parent row is joined with two child rows produced by parsing two elements of the children subarray. OPENJSON 函式會剖析 doc 資料行中的 children 片段，並以一組資料列形式傳回每個元素的 grade 和 givenName。OPENJSON function parses children fragment from the doc column and returns grade and givenName from each element as a set of rows. 這個資料列集可以與父文件聯結。This rowset can be joined with the parent document.

查詢巢狀階層式 JSON 子陣列Query nested hierarchical JSON sub-arrays

您可以套用多個 CROSS APPLY OPENJSON 呼叫，以便查詢巢狀 JSON 結構。You can apply multiple CROSS APPLY OPENJSON calls in order to query nested JSON structures. 此範例中所使用 JSON 文件具有稱為 children 的巢狀陣列，其中每個兒童都有 pets 的巢狀陣列。The JSON document used in this example has a nested array called children, where each child has nested array of pets. 下列查詢會剖析每個文件的 children，將每個陣列物件傳回為資料列，然後剖析 pets 陣列：The following query will parse children from each document, return each array object as row, and then parse pets array:

SELECT  familyName,
    c.givenName AS childGivenName,
    c.firstName AS childFirstName,
    p.givenName AS petName 
FROM Families f 
    CROSS APPLY OPENJSON(f.doc) 
        WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
        CROSS APPLY OPENJSON(children) 
        WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
            OUTER APPLY OPENJSON (pets)
            WITH (givenName nvarchar(100))  as p

第一個 OPENJSON 呼叫會使用 AS JSON 子句傳回 children 陣列的片段。The first OPENJSON call will return fragment of children array using AS JSON clause. 此陣列片段將提供給第二個 OPENJSON 函式，該函式會傳回每個兒童的 givenName、firstName，以及 pets 的陣列。This array fragment will be provided to the second OPENJSON function that will return givenName, firstName of each child, as well as the array of pets. pets 的陣列將提供給第三個 OPENJSON 函式，此函式會傳回寵物的 givenName。The array of pets will be provided to the third OPENJSON function that will return the givenName of the pet. 下表顯示此查詢的結果：The results of this query are shown in the following table:

familyNamefamilyName	childGivenNamechildGivenName	childFirstNamechildFirstName	petNamepetName
AndersenFamilyAndersenFamily	JesseJesse	MerriamMerriam	GoofyGoofy
AndersenFamilyAndersenFamily	JesseJesse	MerriamMerriam	陰影Shadow
AndersenFamilyAndersenFamily	LisaLisa	MillerMiller	`NULL`

根文件會與第一個 OPENJSON(children) 呼叫所傳回的兩個 children 資料列聯結，形成兩個資料列 (或元組)。The root document is joined with two children rows returned by first OPENJSON(children) call making two rows (or tuples). 然後，每個資料列會與 OPENJSON(pets) 使用 OUTER APPLY 運算子所產生的新資料列聯結。Then each row is joined with the new rows generated by OPENJSON(pets) using OUTER APPLY operator. Jesse 有兩隻寵物，因此 (AndersenFamily, Jesse, Merriam) 會與針對 Goofy 和 Shadow 產生的兩個資料列聯結。Jesse has two pets, so (AndersenFamily, Jesse, Merriam) is joined with two rows generated for Goofy and Shadow. Lisa 沒有寵物，因此這個元組沒有 OPENJSON(pets) 傳回的任何資料列。Lisa doesn't have the pets, so there are no rows returned by OPENJSON(pets) for this tuple. 不過，因為我們使用 OUTER APPLY，所以將在資料行中取得 NULL。However, since we are using OUTER APPLY we are getting NULL in the column. 如果我們使用 CROSS APPLY 而不是 OUTER APPLY，則結果中不會傳回 Lisa，因為沒有任何可與這個元組聯結的寵物資料列。If we put CROSS APPLY instead of OUTER APPLY, Lisa would not be returned in the result because there are no pets rows that could be joined with this tuple.

JSON_VALUE 與 JSON_QUERY 的比較Compare JSON_VALUE and JSON_QUERY

JSON_VALUE 和 JSON_QUERY 的主要差別是 JSON_VALUE 傳回純量值，而 JSON_QUERY 傳回物件或陣列。The key difference between JSON_VALUE and JSON_QUERY is that JSON_VALUE returns a scalar value, while JSON_QUERY returns an object or an array.

請參考下列 JSON 文字範例：Consider the following sample JSON text.

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

在這個 JSON 文字範例中，資料成員 "a" 和 "c" 為字串值，而資料成員 "b" 是陣列。In this sample JSON text, data members "a" and "c" are string values, while data member "b" is an array. JSON_VALUE 和 JSON_QUERY 傳回下列結果︰JSON_VALUE and JSON_QUERY return the following results:

路徑Path	JSON_VALUE 傳回JSON_VALUE returns	JSON_QUERY 傳回JSON_QUERY returns
$	NULL 或錯誤NULL or error	`{ "a": "[1,2]", "b": [1,2], "c":"hi"}`
$.a$.a	[1,2][1,2]	NULL 或錯誤NULL or error
$.b$.b	NULL 或錯誤NULL or error	[1,2][1,2]
$.b[0]$.b[0]	11	NULL 或錯誤NULL or error
$.c$.c	hihi	NULL 或錯誤NULL or error

使用 AdventureWorks 範例資料庫，測試 JSON_VALUE 與 JSON_QUERYTest JSON_VALUE and JSON_QUERY with the AdventureWorks sample database

依據本主題中所述，使用 AdventureWorks 範例資料庫，執行下列範例，以測試內建函式。Test the built-in functions described in this topic by running the following examples with the AdventureWorks sample database. 如需何處取得 AdventureWorks 及如何新增 JSON 資料以供測試的資訊，請參閱測試磁碟機內建的 JSON 支援。For info about where to get AdventureWorks, and about how to add JSON data for testing by running a script, see Test drive built-in JSON support.

在下列範例中，SalesOrder_json資料表中的 Info 資料行包含 JSON 文字。In the following examples, the Info column in the SalesOrder_json table contains JSON text.

範例 1 - 傳回標準的資料行和 JSON 資料Example 1 - Return both standard columns and JSON data

下列查詢會傳回來自標準關聯式資料行及 JSON 資料行的值。The following query returns values from both standard relational columns and from a JSON column.

SELECT SalesOrderNumber, OrderDate, Status, ShipDate, Status, 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 值Example 2- Aggregate and filter JSON values

下列查詢會依據客戶名稱和狀態來彙總小計 (客戶名稱是儲存在 JSON 中，而狀態是儲存在一般資料行中)。The following query aggregates subtotals by customer name (stored in JSON) and status (stored in an ordinary column). 然後依縣市 (儲存在 JSON 中) 及 OrderDate (儲存在一般資料行中) 篩選結果。Then it filters the results by city (stored in JSON) and OrderDate (stored in an ordinary column).

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 文字中的屬性值Update property values in JSON text by using the JSON_MODIFY function

JSON_MODIFY 函式會更新 JSON 字串中屬性的值，並傳回更新的 JSON 字串。The JSON_MODIFY function updates the value of a property in a JSON string and returns the updated JSON string.

下列範例會更新包含 JSON 的變數中，JSON 屬性的值。The following example updates the value of a JSON property in a variable that contains JSON.

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

如需詳細資訊，請參閱 JSON_MODIFY (Transact-SQL)。For more info, see JSON_MODIFY (Transact-SQL).

深入了解 SQL Server 和 Azure SQL Database 中的 JSONLearn more about JSON in SQL Server and Azure SQL Database

Microsoft 影片Microsoft videos

如需 SQL Server 和 Azure SQL Database 中內建 JSON 支援的觀看式簡介，請參閱下列影片：For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

使用內建函數，驗證、查詢以及變更 JSON 資料 (SQL Server)Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)

此頁面上之範例的 JSON 文字JSON text for the examples on this page

使用 ISJSON 函數來驗證 JSON 文字Validate JSON text by using the ISJSON function

使用 JSON_VALUE 函數，從 JSON 文字中擷取值Extract a value from JSON text by using the JSON_VALUE function

使用 JSON_QUERY 函數，從 JSON 文字擷取物件或陣列Extract an object or an array from JSON text by using the JSON_QUERY function

剖析巢狀 JSON 集合Parse nested JSON collections

查詢巢狀階層式 JSON 子陣列Query nested hierarchical JSON sub-arrays

JSON_VALUE 與 JSON_QUERY 的比較Compare JSON_VALUE and JSON_QUERY

使用 AdventureWorks 範例資料庫，測試 JSON_VALUE 與 JSON_QUERYTest JSON_VALUE and JSON_QUERY with the AdventureWorks sample database

範例 1 - 傳回標準的資料行和 JSON 資料Example 1 - Return both standard columns and JSON data

範例 2 - 彙總並篩選 JSON 值Example 2- Aggregate and filter JSON values

使用 JSON_MODIFY 函數來更新 JSON 文字中的屬性值Update property values in JSON text by using the JSON_MODIFY function

深入了解 SQL Server 和 Azure SQL Database 中的 JSONLearn more about JSON in SQL Server and Azure SQL Database

Microsoft 影片Microsoft videos

另請參閱See Also

此頁面有所助益嗎？

意見反應

此頁面有所助益嗎？