SQL Server の JSON データJSON data in SQL Server

適用対象: ○SQL Server (2016 以降) ○Azure SQL Database ○Azure SQL Data Warehouse XParallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2016) yesAzure SQL Database yesAzure 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. また、JSON はログ ファイル内の非構造化データや 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 Search、Azure Storage、Azure Cosmos DB などの Azure のほとんどのサービスには、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 ページおよび 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.
  • Transact-SQL クエリの結果を JSON 形式で書式設定する。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

次の例のクエリでは、テーブルからリレーショナル データと (jsonCol という名前の列に格納された) JSON データの両方を使用しています。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. JSON テキストからの値は、Transact-SQL クエリのすべての部分で使用できます (例: WHERE 句、ORDER BY 句、または GROUP BY 句、ウィンドウの集計など)。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. JSON データのクエリを実行するには、標準の T-SQL を使用することができます。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 オブジェクトの配列がテーブルに変換され、各オブジェクトが 1 つの行として表わされ、キーと値のペアがセルとして返されます。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.
  • パス内の省略可能な strict プレフィックスでは、指定されたプロパティの値が 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.

次の例では、配列内の 2 番目のオブジェクトは、個人のスキルを表すサブ配列を持っています。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) '$' )

skills 配列は、最初の OPENJSON で元の 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. 2 番目の OPENJSON 関数は、JSON 配列を解析し、最初の OPENJSON の結果と結合される 1 つの列の行セットとして文字列値を返します。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. 結合により、すべてのスキルで 2 番目の行が繰り返されます。Due to JOIN, the second row will be repeated for every skill.

SQL Server のデータを JSON に変換または JSON をエクスポートするConvert SQL Server data to JSON or export JSON

注意

Azure SQL Data Warehouse データの JSON への変換や、JSON のエクスポートは、サポートされていません。Converting Azure SQL Data Warehouse data to JSON or exporting JSON is not supported.

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).

次の例では、PATH モードを FOR JSON 句とともに使用しています。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.

複雑なデータ モデルを簡略化するSimplify complex data models

複数の子テーブルの代わりに JSON フィールドでデータ モデルを非正規化することを検討してください。Consider denormalizing your data model with JSON fields in place of multiple child tables.

小売および e コマースのデータを格納するStore retail and e-commerce data

柔軟性のために非正規化されたモデルにさまざまな変数属性による製品情報を格納します。Store info about products with a wide range of variable attributes in a denormalized model for flexibility.

ログとテレメトリ データの処理Process log and telemetry data

Transact-SQL 言語を活用して JSON ファイルとして格納されたログ データの読み込み、クエリ、分析を行います。Load, query, and analyze log data stored as JSON files with all the power of the Transact-SQL language.

半構造化された 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.

REST API の開発を簡略化するSimplify REST API development

データベースのリレーショナル データを、Web サイトをサポートする REST API で使用される JSON 形式に簡単に変換します。Transform relational data from your database easily into the JSON format used by the REST APIs that support your web site.

リレーショナル データと 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. テーブル内の JSON ドキュメントのコレクションを整理し、それらの間のリレーションシップを確立、テーブルに格納された厳密に型指定されたスカラー列を JSON 列に格納された柔軟なキーと値のペアに結合し、完全な Transact-SQL を使用して 1 つ以上のテーブルのスカラー値と 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 DB の使用をお勧めします。If 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 ServerSQL Serverで組み込みの JSON サポートの使用方法を示すユース ケースを紹介します。Here are some use cases that show how you can use the built-in JSON support in SQL ServerSQL Server.

JSON データの SQL Server への格納とインデックスの追加Store and index JSON data in SQL Server

JSON はテキスト形式なので、SQL Database の NVARCHAR 列に JSON ドキュメントを格納できます。JSON is a textual format so the JSON documents can be stored in NVARCHAR columns in a SQL Database. NVARCHAR 型は、すべての SQL Server サブシステムでサポートされているので、CLUSTERED COLUMNSTORE インデックスが付いたテーブル、メモリ最適化テーブル、または OPENROWSET または PolyBase を使用して読み取ることができる外部ファイルに JSON ドキュメントを格納することができます。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.

JSON データの SQL Server への保存、インデックスの追加、最適化のオプションに関する詳細については、次の記事を参照してください。To learn more about your options for storing, indexing, and optimizing JSON data in SQL Server, see the following articles:

SQL Server に JSON ファイルを読み込むLoad 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 ファイルのコンテンツをインポートし、OPENJSON または JSON_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 File の場所に格納されている場合は、一括インポートを使用して SQL Server に JSON データを読み込むことができます。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.

  • 行区切りの JSON ファイルが Azure Blob Storage または 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.

SQL Server に JSON データをインポートする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;  

JSON 変数の内容は、外部 REST サービスによって提供されたり、クライアント側 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.

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 フレームワークまたはライブラリがある場合、JSON 出力を SQL クエリで直接設定できます。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. コードを記述するかライブラリを追加して、表形式のクエリ結果を変換し、それからオブジェクトを JSON 形式にシリアル化する代わりに、FOR JSON を使用して JSON 形式への設定を SQL Server に委任できます。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":"https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}Response: {"@odata.context":"https://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 'https://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.

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.

    • EmailAddresses/PersonPhone テーブルからの情報を、Person_json に 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 の JSON の詳細情報Learn 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 video:

SQL Server 2016 と Azure SQL Database での JSON の使用Using JSON in SQL Server 2016 and Azure SQL Database

JSON 関数を使用した SQL Server での REST API の作成Building REST API with SQL Server using JSON functions

関連記事Reference articles