SQL Server 中的 JSON 資料JSON data in SQL Server

適用於: 是SQL Server (從 2016 開始) 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2016) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

JSON 是種熱門的文字資料格式,用於在新式 Web 和行動應用程式中交換資料。JSON is a popular textual data format that's used for exchanging data in modern web and mobile applications. 其也可用於將非結構化的資料儲存在記錄檔或是類似 Microsoft Azure Cosmos DB 的 NoSQL 資料庫中。JSON is also used for storing unstructured data in log files or NoSQL databases such as Microsoft Azure Cosmos DB. 許多 REST Web 服務會傳回已格式化為 JSON 文字的結果,或接受已格式化為 JSON 的資料。Many REST web services return results that are formatted as JSON text or accept data that's formatted as JSON. 例如,大部分的 Azure 服務 (例如 Azure 搜尋服務、Azure 儲存體和 Azure Cosmos DB) 都具有傳回或取用 JSON 的 REST 端點。For example, most Azure services, such as Azure Search, Azure Storage, and Azure Cosmos DB, have REST endpoints that return or consume JSON. JSON 也是用於透過 AJAX 呼叫在網頁和 Web 伺服器之間交換資料的主要格式。JSON is also the main format for exchanging data between webpages and web servers by using AJAX calls.

SQL Server 中的 JSON 函數可讓您將 NoSQL 與關聯式概念結合在同一個資料庫中。JSON functions in SQL Server enable you to combine NoSQL and relational concepts in the same database. 現在,您可以將傳統關聯式資料行與包含採用 JSON 文字格式之文件的資料行結合在同一個資料表中、剖析並匯入關聯式結構中的 JSON 文件,或讓關聯式資料採用 JSON 文字格式。Now you can combine classic relational columns with columns that contain documents formatted as JSON text in the same table, parse and import JSON documents in relational structures, or format relational data to JSON text. 在下列影片中,您將了解在 SQL Server 和 Azure SQL Database 中,JSON 函式如何連接關聯式與 NoSQL 概念:You see how JSON functions connect relational and NoSQL concepts in SQL Server and Azure SQL Database in the following video:

NoSQL 與關聯式領域之間的橋樑 JSONJSON as a bridge between NoSQL and relational worlds

以下是 JSON 文字範例︰Here's an example of JSON text:

[{
    "name": "John",
    "skills": ["SQL", "C#", "Azure"]
}, {
    "name": "Jane",
    "surname": "Doe"
}]

您可使用 SQL Server 內建函式和運算子,以 JSON 文字執行下列作業:By using SQL Server built-in functions and operators, you can do the following things with JSON text:

  • 剖析 JSON 文字,並讀取或修改值。Parse JSON text and read or modify values.
  • 將 JSON 物件的陣列轉換成資料表格式。Transform arrays of JSON objects into table format.
  • 在已轉換的 JSON 物件上執行任何 Transact SQL 查詢。Run any Transact-SQL query on the converted JSON objects.
  • 以 JSON 格式格式化 Transact-SQL 查詢的結果。Format the results of Transact-SQL queries in JSON format.

內建 JSON 支援的概觀Overview of built-in JSON support

SQL Server 和 SQL Database 的主要 JSON 功能Key JSON capabilities of SQL Server and SQL Database

下一節說明 SQL Server 以內建 JSON 支援提供的主要功能。The next sections discuss the key capabilities that SQL Server provides with its built-in JSON support. 在下列影片中,您可以了解如何使用 JSON 函數和運算子:You can see how to use JSON functions and operators in the following video:

SQL Server 2016 和 JSON 支援SQL Server 2016 and JSON Support

從 JSON 文字中擷取值,然後在查詢中使用它們Extract values from JSON text and use them in queries

如有儲存在資料庫資料表中的 JSON 文字,您可以使用下列內建函式來讀取或修改 JSON 文字中的值:If you have JSON text that's stored in database tables, you can read or modify values in the JSON text by using the following built-in functions:

範例Example

在下列範例中,查詢會使用來自資料表的關聯式資料和 JSON 資料 (儲存在名為 jsonCol 的資料行中):In the following example, the query uses both relational and JSON data (stored in a column named jsonCol) from a table:

SELECT Name,Surname,
 JSON_VALUE(jsonCol,'$.info.address.PostCode') AS PostCode,
 JSON_VALUE(jsonCol,'$.info.address."Address Line 1"')+' '
  +JSON_VALUE(jsonCol,'$.info.address."Address Line 2"') AS Address,
 JSON_QUERY(jsonCol,'$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol)>0
 AND JSON_VALUE(jsonCol,'$.info.address.Town')='Belgrade'
 AND Status='Active'
ORDER BY JSON_VALUE(jsonCol,'$.info.address.PostCode')

應用程式和工具分辨不出從純量資料表資料行取得的值和從 JSON 資料行取得的值有何不同。Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. 您可以在 Transact-SQL 查詢的任意部分 (包括 WHERE、ORDER BY 或 GROUP BY 子句、範圍彙總等等),使用來自 JSON 文字的值。You can use values from JSON text in any part of a Transact-SQL query (including WHERE, ORDER BY, or GROUP BY clauses, window aggregates, and so on). JSON 函式會使用類似 JavaScript 的語法來參考 JSON 文字內的值。JSON functions use JavaScript-like syntax for referencing values inside JSON text.

如需詳細資訊,請參閱使用內建函式驗證、查詢及變更 JSON 資料 (SQL Server)JSON_VALUE (Transact-SQL)JSON_QUERY (Transact-SQL)For more information, see Validate, query, and change JSON data with built-in functions (SQL Server), JSON_VALUE (Transact-SQL), and JSON_QUERY (Transact-SQL).

變更 JSON 值Change JSON values

如果您必須修改部分 JSON 文字,可以使用 JSON_MODIFY (Transact-SQL) 函式來更新 JSON 字串中的屬性值,並傳回更新的 JSON 字串。If you must modify parts of JSON text, you can use the JSON_MODIFY (Transact-SQL) function to update the value of a property in a JSON string and return the updated JSON string. 下列範例示範在包含 JSON 的變數中更新屬性的值:The following example updates the value of a property in a variable that contains JSON:

DECLARE @json NVARCHAR(MAX);
SET @json = '{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json,'$.info.address[1].town','London');
SELECT modifiedJson = @json;

結果Results

modifiedJsonmodifiedJson
{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}

將 JSON 集合轉換為資料列集Convert JSON collections to a rowset

您不需要自訂查詢語言也能在 SQL Server 中查詢 JSON。You don't need a custom query language to query JSON in SQL Server. 您可以使用標準的 T-SQL 查詢 JSON 資料。To query JSON data, you can use standard T-SQL. 如果您必須建立 JSON 資料的查詢或報表,可以呼叫 OPENJSON 資料列集函式,輕鬆地將 JSON 資料轉換成資料列和資料行。If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. 如需詳細資訊,請參閱使用 OPENJSON 將 JSON 資料轉換成資料列和資料行 (SQL Server)For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

下列範例示範呼叫 OPENJSON,並將儲存於 @json 變數中的物件陣列轉換為可使用標準 SQL SELECT 陳述式查詢的資料列集︰The following example calls OPENJSON and transforms the array of objects that is stored in the @json variable to a rowset that can be queried with a standard SQL SELECT statement:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob')  

結果Results

idid firstNamefirstName lastNamelastName ageage dateOfBirthdateOfBirth
22 JohnJohn SmithSmith 2525
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00

OPENJSON 會將 JSON 物件的陣列將轉換為資料表,每個物件顯示為一個資料列,而索引鍵/值組會以資料格形式傳回。OPENJSON transforms the array of JSON objects into a table in which each object is represented as one row, and key/value pairs are returned as cells. 輸出會遵循下列規則:The output observes the following rules:

  • OPENJSON 將 JSON 值轉換為 WITH 子句中指定的類型。OPENJSON converts JSON values to the types that are specified in the WITH clause.
  • OPENJSON 可以處理單層式金鑰值組和巢狀階層組織的物件。OPENJSON can handle both flat key/value pairs and nested, hierarchically organized objects.
  • 您不必傳回 JSON 文字包含的所有欄位。You don't have to return all the fields that are contained in the JSON text.
  • 如果 JSON 值不存在,OPENJSON 會傳回 NULL 值。If JSON values don't exist, OPENJSON returns NULL values.
  • 您可以選擇在類型規格之後指定路徑,以參考巢狀的屬性或參考不同名稱的屬性。You can optionally specify a path after the type specification to reference a nested property or to reference a property by a different name.
  • JSON 文字中必須有在路徑中指定指定屬性值的選擇性 嚴格 前置詞。The optional strict prefix in the path specifies that values for the specified properties must exist in the JSON text.

如需詳細資訊,請參閱使用 OPENJSON 將 JSON 資料轉換成資料列和資料行 (SQL Server)OPENJSON (Transact-SQL)For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server) and OPENJSON (Transact-SQL).

JSON 文件可能會有不能直接對應到標準關聯式資料行的子項目和階層式資料。JSON documents may have sub-elements and hierarchical data that cannot be directly mapped into the standard relational columns. 在此情況下,您可以藉由聯結父實體和子陣列來壓平合併 JSON 階層。In this case, you can flatten JSON hierarchy by joining parent entity with sub-arrays.

在下列範例中,陣列中的第二個物件具有代表人員技能的子陣列。In the following example, the second object in the array has sub-array representing person skills. 每個子物件都可以使用其他 OPENJSON 函式呼叫加以剖析:Every sub-object can be parsed using additional OPENJSON function call:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"] }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob',
    skills nvarchar(max) '$.info.skills' as json) 
    outer apply openjson( skills ) 
                     with ( skill nvarchar(8) '$' )

第一個 OPENJSON 傳回的 skills 陣列作為原始的 JSON 文字片段,並使用 APPLY 運算子傳遞給另一個 OPENJSON 函式。skills array is returned in the first OPENJSON as original JSON text fragment and passed to another OPENJSON function using APPLY operator. 第二個 OPENJSON 函式會剖析 JSON 陣列並傳回字串值作為單一資料行資料列集,與第一個 OPENJSON 的結果聯結。The second OPENJSON function will parse JSON array and return string values as single column rowset that will be joined with the result of the first OPENJSON. 下表顯示此查詢的結果:The result of this query is shown in the following table:

結果Results

idid firstNamefirstName lastNamelastName ageage dateOfBirthdateOfBirth skillskill
22 JohnJohn SmithSmith 2525
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00 SQLSQL
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00 C#C#
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00 AzureAzure

OUTER APPLY OPENJSON 會聯結第一個層級的實體和子陣列,並傳回壓平合併的結果集。OUTER APPLY OPENJSON will join first level entity with sub-array and return flatten resultset. 因為「聯結」的緣故,每個技能都會重複第二個資料列。Due to JOIN, the second row will be repeated for every skill.

將 SQL Server 資料轉換為 JSON 或匯出 JSONConvert SQL Server data to JSON or export JSON

FOR JSON 子句加入至 SELECT 陳述式,以將 SQL Server 資料或 SQL 查詢結果格式化為 JSON。Format SQL Server data or the results of SQL queries as JSON by adding the FOR JSON clause to a SELECT statement. 使用 FOR JSON 將您用戶端應用程式的 JSON 輸出格式設定委派給 SQL Server。Use FOR JSON to delegate the formatting of JSON output from your client applications to SQL Server. 如需詳細資訊,請參閱使用 FOR JSON 將查詢結果格式化為 JSON (SQL Server)For more information, see Format Query Results as JSON with FOR JSON (SQL Server).

下列範例示範搭配 FOR JSON 子句使用 PATH 模式:The following example uses PATH mode with the FOR JSON clause:

SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth as dob  
FROM People  
FOR JSON PATH  

星期 FOR JSON 子句會將 SQL 結果格式化為 JSON 文字,提供給任何了解 JSON 的應用程式。The FOR JSON clause formats SQL results as JSON text that can be provided to any app that understands JSON. PATH 選項會在 SELECT 子句中使用點分隔的別名,巢狀化查詢結果中的物件。The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results.

結果Results

[{
    "id": 2,
    "info": {
        "name": "John",
        "surname": "Smith"
    },
    "age": 25
}, {
    "id": 5,
    "info": {
        "name": "Jane",
        "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
}] 

如需詳細資訊,請參閱使用 FOR JSON 將查詢結果格式化為 JSON (SQL Server)FOR 子句 (Transact-SQL)For more information, see Format query results as JSON with FOR JSON (SQL Server) and FOR Clause (Transact-SQL).

SQL Server 中 JSON 資料的使用案例Use cases for JSON data in SQL Server

SQL Server 與 Azure SQL Database 中的 JSON 支援,可讓您能結合關聯式概念與 NoSQL 概念。JSON support in SQL Server and Azure SQL Database lets you combine relational and NoSQL concepts. 您可以輕鬆地將關聯式資料轉換為半結構化的資料,反之亦然。You can easily transform relational to semi-structured data and vice-versa. 但 JSON 並非取代現有的關聯式模型。JSON is not a replacement for existing relational models, however. 以下是一些受益於 SQL Server 與 SQL Database 中 JSON 支援的特定使用案例。Here are some specific use cases that benefit from the JSON support in SQL Server and in SQL Database. 如需詳細資訊,請參閱 SQL Server 中的 JSON – 使用案例For more info, see JSON in SQL Server – Use cases.

簡化複雜的資料模型Simplify complex data models

請考慮將您的資料模型去除正規化,用 JSON 欄位來取代多個子資料表。Consider denormalizing your data model with JSON fields in place of multiple child tables. 如需詳細資訊,請參閱使用去除正規化模型來簡化資料存取For more info, see Simplify data access using de-normalized models.

儲存零售及電子商務資料Store retail and e-commerce data

將具有各式各樣變化特質的產品相關資訊,儲存在非正規化模型中以保留彈性。Store info about products with a wide range of variable attributes in a denormalized model for flexibility. 如需詳細資訊,請參閱使用 JSON 於 SQL Server 中設計產品類別目錄以及編製 JSON 產品類別目錄中資料的索引For more info, see Designing Product Catalogs in SQL Server using JSON and Indexing data in JSON product catalogs.

處理記錄檔與遙測資料Process log and telemetry data

載入、查詢及分析儲存為 JSON 檔案的記錄資料,同時具備 TRANSACT-SQL 語言的所有功能。Load, query, and analyze log data stored as JSON files with all the power of the Transact-SQL language. 如需詳細資訊,請參閱 SQL Server 中的 JSON – 使用案例內的記錄與遙測資料分析For more info, see the section Log and telemetry data analysis in JSON in SQL Server – Use cases.

儲存半結構化的 IoT 資料Store semi-structured IoT data

當您需要即時的 IoT 資料分析時,請將內送資料直接載入資料庫,而非將其暫置於儲存體位置。When you need real-time analysis of IoT data, load the incoming data directly into the database instead of staging it in a storage location. 如需詳細資訊,請參閱使用 Azure SQL Database 中的 Azure IoT 資料For more info, see Working with Azure IoT data in Azure SQL Database.

簡化 REST API 開發Simplify REST API development

輕鬆地將您資料庫中的關聯式資料,轉換為支援您的網站之 REST API 所使用的 JSON 格式。Transform relational data from your database easily into the JSON format used by the REST APIs that support your web site. 如需詳細資訊,請參閱利用 SQL Server 簡化現代化單一頁面應用程式的 REST API 開發For more info, see Simplify REST API development for modern Single-page apps with SQL Server.

合併關聯式資料和 JSON 資料Combine relational and JSON data

SQL Server 提供混合模型,讓您使用標準 Transact-SQL 語言儲存兼處理關聯式與 JSON 資料。SQL Server provides a hybrid model for storing and processing both relational and JSON data by using standard Transact-SQL language. 您可以使用完整的 Transact-SQL 在資料表中組織 JSON 文件的集合、建立它們之間的關聯性、結合儲存於資料表中的強類型純量資料行和儲存於 JSON 資料行中的彈性索引鍵/值組,以及在一或多個資料表中查詢純量和 JSON 值。You can organize collections of your JSON documents in tables, establish relationships between them, combine strongly typed scalar columns stored in tables with flexible key/value pairs stored in JSON columns, and query both scalar and JSON values in one or more tables by using full Transact-SQL.

JSON 文字儲存在 varchar 或 nvarchar 資料行中,並建立成純文字形式的索引。JSON text is stored in varchar or nvarchar columns and is indexed as plain text. 任何支援文字的 SQL Server 功能或元件都支援 JSON,因此 JSON 和其他 SQL Server 功能之間幾乎沒有任何互動限制。Any SQL Server feature or component that supports text supports JSON, so there are almost no constraints on interaction between JSON and other SQL Server features. 您可以將 JSON 儲存在記憶體內部或時態表中、在 JSON 文字上套用資料列層級安全性的述詞等。You can store JSON in In-memory or Temporal tables, apply Row-Level Security predicates on JSON text, and so on.

如果您有想要在其中使用專為處理 JSON 文件而設定之查詢語言的 JSON 工作負載,請考慮使用 Microsoft Azure Cosmos DBIf you have pure JSON workloads where you want to use a query language that's customized for the processing of JSON documents, consider Microsoft Azure Cosmos DB.

以下提供數個使用案例,示範如何在 [SQL Server]SQL Server中使用內建的 JSON 支援。Here are some use cases that show how you can use the built-in JSON support in [SQL Server]SQL Server.

在 SQL Server 中儲存 JSON 資料並編製索引Store and index JSON data in SQL Server

JSON 是文字格式,因此 JSON 文件都可以儲存在 SQL Database 的 NVARCHAR 資料行中。JSON is a textual format so the JSON documents can be stored in NVARCHAR columns in a SQL Database. 因為所有 SQL Server 子系統都支援 NVARCHAR 類型,所以您可以將 JSON 文件放在具有 CLUSTERED COLUMNSTORE 索引的資料表、記憶體最佳化資料表,或可以使用 OPENROWSET 或 Polybase 讀取的外部檔案中。Since NVARCHAR type is supported in all SQL Server sub-systems you can put JSON documents in tables with CLUSTERED COLUMNSTORE indexes, memory optimized tables, or external files that can be read using OPENROWSET or Polybase.

若要深入了解在 SQL Server 中將 JSON 儲存、編製索引和最佳化的選項,請參閱下列文章:To learn more about your options for storing, indexing, and optimizing JSON data in SQL Server, see the following articles:

將 JSON 檔案載入 SQL ServerLoad JSON files into SQL Server

您可以將儲存在檔案中的資訊格式化為標準 JSON 或以行分隔的 JSON。You can format information that's stored in files as standard JSON or line-delimited JSON. SQL Server 可匯入 JSON 檔案的內容,使用 OPENJSONJSON_VALUE 函式對其剖析,並將其載入資料表。SQL Server can import the contents of JSON files, parse it by using the OPENJSON or JSON_VALUE functions, and load it into tables.

  • 如果您的 JSON 文件儲存於本機檔案、共用網路磁碟機,或可透過 SQL Server 存取的 Azure 檔案位置,您就能使用大量匯入將 JSON 資料載入 SQL Server。If your JSON documents are stored in local files, on shared network drives, or in Azure Files locations that can be accessed by SQL Server, you can use bulk import to load your JSON data into SQL Server. 如需此案例的詳細資訊,請參閱使用 OPENROWSET (BULK) 將 JSON 檔案匯入 SQL ServerFor more information about this scenario, see Importing JSON files into SQL Server using OPENROWSET (BULK).

  • 如果以行分隔的 JSON 檔案儲存在 Azure Blob 儲存體或 Hadoop 檔案系統中,您就可以使用 Polybase 來載入 JSON 文字、在 Transact-SQL 程式碼中對其剖析,並將其載入資料表。If your line-delimited JSON files are stored in Azure Blob storage or the Hadoop file system, you can use PolyBase to load JSON text, parse it in Transact-SQL code, and load it into tables.

將 JSON 資料匯入 SQL Server 資料表Import JSON data into SQL Server tables

如果必須將外部服務的 JSON 資料載入 SQL Server,您可以改用 OPENJSON 將資料匯入 SQL Server,而不需在應用程式層剖析資料。If you must load JSON data from an external service into SQL Server, you can use OPENJSON to import the data into SQL Server instead of parsing the data in the application layer.

DECLARE @jsonVariable NVARCHAR(MAX)

SET @jsonVariable = N'[  
        {  
          "Order": {  
            "Number":"SO43659",  
            "Date":"2011-05-31T00:00:00"  
          },  
          "AccountNumber":"AW29825",  
          "Item": {  
            "Price":2024.9940,  
            "Quantity":1  
          }  
        },  
        {  
          "Order": {  
            "Number":"SO43661",  
            "Date":"2011-06-01T00:00:00"  
          },  
          "AccountNumber":"AW73565",  
          "Item": {  
            "Price":2024.9940,  
            "Quantity":3  
          }  
       }  
  ]'

INSERT INTO SalesReport  
SELECT SalesOrderJsonData.*  
FROM OPENJSON (@jsonVariable, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData;  

您可以透過外部 REST 服務提供 JSON 變數的內容、從用戶端的 JavaScript 架構以參數形式加以傳送,或從外部檔案載入。You can provide the content of the JSON variable by an external REST service, send it as a parameter from a client-side JavaScript framework, or load it from external files. 您可以輕鬆地將 JSON 文字的結果插入、更新或合併至 SQL Server 資料表。You can easily insert, update, or merge results from JSON text into a SQL Server table. 如需此案例的詳細資訊,請參閱下列部落格文章:For more information about this scenario, see the following blog posts:

使用 SQL 查詢分析 JSON 資料Analyze JSON data with SQL queries

如果您基於報表用途而必須篩選或彙總 JSON 資料,可以使用 OPENJSON,將 JSON 轉換為關聯式格式。If you must filter or aggregate JSON data for reporting purposes, you can use OPENJSON to transform JSON to relational format. 然後使用標準 Transact-SQLTransact-SQL 和內建函式來準備報表。You can then use standard Transact-SQLTransact-SQL and built-in functions to prepare the reports.

SELECT Tab.Id, SalesOrderJsonData.Customer, SalesOrderJsonData.Date  
FROM   SalesOrderRecord AS Tab  
          CROSS APPLY  
     OPENJSON (Tab.json, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData  
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'  
ORDER BY JSON_VALUE(Tab.json, '$.Group'), Tab.DateModified  

您可以在相同的查詢中同時使用 JSON 文字的標準資料表資料行和值。You can use both standard table columns and values from JSON text in the same query. 您可以在 JSON_VALUE(Tab.json, '$.Status') 運算式上新增索引,以提升查詢效能。You can add indexes on the JSON_VALUE(Tab.json, '$.Status') expression to improve the performance of the query. 如需詳細資訊,請參閱建立 JSON 資料的索引For more information, see Index JSON data.

傳回格式化為 JSON 的 SQL Server 資料表資料Return data from a SQL Server table formatted as JSON

如果您有從資料庫層取得資料,並將其以 JSON 格式傳回的 Web 服務,或有接受格式化為 JSON 之資料的 JavaScript 架構或程式庫,可以在 SQL 查詢中直接將 JSON 輸出格式化。If you have a web service that takes data from the database layer and returns it in JSON format, or if you have JavaScript frameworks or libraries that accept data formatted as JSON, you can format JSON output directly in a SQL query. 您可以使用 FOR JSON 將 JSON 格式設定委派給 SQL Server,而不需要撰寫程式碼或包含程式庫,以轉換表格式查詢結果,再將物件序列化為 JSON 格式。Instead of writing code or including a library to convert tabular query results and then serialize objects to JSON format, you can use FOR JSON to delegate the JSON formatting to SQL Server.

例如,您可能想要產生符合 OData 規格的 JSON 輸出。For example, you might want to generate JSON output that's compliant with the OData specification. Web 服務預期具備下列格式的要求和回應:The web service expects a request and response in the following format:

  • 要求: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductNameRequest: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName

  • 回應︰ {"@odata.context":"http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}Response: {"@odata.context":"http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}

此 OData URL 表示對於 id 1 的產品之 ProductID 和 ProductName 資料行的要求。This OData URL represents a request for the ProductID and ProductName columns for the product with id 1. 您可以使用 FOR JSON,以 SQL Server 的預期方式來格式化輸出。You can use FOR JSON to format the output as expected in SQL Server.

SELECT 'http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity'
 AS '@odata.context',   
 ProductID, Name as ProductName   
FROM Production.Product  
WHERE ProductID = 1  
FOR JSON AUTO  

此查詢的輸出是完全符合 OData 規格的 JSON 文字。格式設定和逸出是由 SQL Server 處理。The output of this query is JSON text that's fully compliant with the OData spec. Formatting and escaping are handled by SQL Server. SQL Server 也能以任何格式將查詢結果格式化,例如 OData JSON 或 GeoJSON。SQL Server can also format query results in any format, such as OData JSON or GeoJSON. 如需詳細資訊,請參閱 Returning spatial data in GeoJSON format (以 GeoJSON 格式傳回空間資料)。For more information, see Returning spatial data in GeoJSON format.

隨附 AdventureWorks 範例資料庫的內建 JSON 支援試用產品Test drive built-in JSON support with the AdventureWorks sample database

若要取得 AdventureWorks 範例資料庫,至少要從 Microsoft 下載中心下載資料庫檔案、資料庫範本和指令碼檔案。To get the AdventureWorks sample database, download at least the database file and the samples and scripts file from Microsoft Download Center.

將範例資料庫還原到 SQL Server 2016 的執行個體後,請將範例檔案解壓縮,並從 JSON 資料夾開啟 JSON Sample Queries procedures views and indexes.sql 檔案。After you restore the sample database to an instance of SQL Server 2016, extract the samples file, and then open the JSON Sample Queries procedures views and indexes.sql file from the JSON folder. 執行此檔案中的指令碼,將部分存在的資料重新格式化為 JSON 資料、測試有關 JSON 資料的範例查詢和報告、編製 JSON 資料的索引,以及匯入和匯出 JSON。Run the scripts in this file to reformat some existing data as JSON data, test sample queries and reports over the JSON data, index the JSON data, and import and export JSON.

您可以使用檔案中所含指令碼執行的動作如下:Here's what you can do with the scripts that are included in the file:

  • 將現有的結構描述反正規化來建立 JSON 資料的資料行。Denormalize the existing schema to create columns of JSON data.

    • 將來自 SalesReasons、SalesOrderDetails、SalesPerson、Customer,以及其他包含銷售訂單相關資訊的資料表儲存於 SalesOrder_json 資料表的 JSON 資料行中。Store information from SalesReasons, SalesOrderDetails, SalesPerson, Customer, and other tables that contain information related to sales order into JSON columns in the SalesOrder_json table.

    • 將 Person_json 資料表中 EmailAddresses/PersonPhone 資料表的資訊儲存為 JSON 物件的陣列。Store information from EmailAddresses/PersonPhone tables in the Person_json table as arrays of JSON objects.

  • 建立查詢 JSON 資料的程序和檢視。Create procedures and views that query JSON data.

  • 建立 JSON 資料的索引。Index JSON data. 在 JSON 屬性上建立索引及建立全文檢索索引。Create indexes on JSON properties and full-text indexes.

  • 匯入和匯出 JSON。Import and export JSON. 建立並執行將 Person 和 SalesOrder 資料表內容匯出為 JSON 結果的程序,以及使用 JSON 輸入匯入並更新 Person 和 SalesOrder 資料表的程序。Create and run procedures that export the content of the Person and the SalesOrder tables as JSON results, and import and update the Person and the SalesOrder tables by using JSON input.

  • 執行查詢範例。Run query examples. 執行某些查詢來呼叫在步驟 2 和 4 中建立的預存程序和檢視。Run some queries that call the stored procedures and views that you created in steps 2 and 4.

  • 清除指令碼。Clean up scripts. 如果您想要保留在步驟 2 和 4 中建立的預存程序和檢視,則不執行這部分。Don't run this part if you want to keep the stored procedures and views that you created in steps 2 and 4.

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

Microsoft 部落格文章Microsoft blog posts

如需特定的解決方案、使用案例和建議,請參閱這些部落格文章,了解 SQL Server 和 Azure SQL Database 中的內建 JSON 支援。For specific solutions, use cases, and recommendations, see these blog posts about the built-in JSON support 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 video:

使用 SQL Server 2016 和 Azure SQL Database 中的 JSONUsing JSON in SQL Server 2016 and Azure SQL Database

使用 SQL Server 以 JSON 函數建置 REST APIBuilding REST API with SQL Server using JSON functions

參考文章Reference articles