SQL Server の JSON データ

[アーティクル]
02/13/2024

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

JSON は、最新の Web アプリケーションとモバイルアプリケーションでデータを交換するために使用される、一般的なテキスト形式のデータ形式です。また、JSON はログファイル内の非構造化データや Microsoft Azure Cosmos DB のような NoSQL データベースを格納するためも使用されます。 REST Web サービスの多くは結果を JSON テキスト形式で返し、データを JSON 形式で受け取ります。たとえば、Azure Search、Azure Storage、Azure Cosmos DB などの Azure のほとんどのサービスには、JSON を返すか使用する REST エンドポイントがあります。 JSON は、AJAX 呼び出しを使用して Web ページおよび Web サーバー間でデータをやり取りするための主な形式でもあります。

SQL Server 2016(13.x) で初めて導入された JSON 関数を使用すると、NoSQL とリレーショナルの概念を同じデータベースに結合できます。従来のリレーショナル列と JSON テキスト形式のドキュメントを含む列を同じテーブルに結合したり、JSON ドキュメントを解析してリレーショナル構造にインポートしたり、リレーショナルデータを JSON テキストに書式設定できます。

Note

JSON のサポートには、データベース互換レベル 130 以上が必要です。

JSON テキストの例を次に示します。

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

SQL Server の組み込みの関数と演算子を使用すると、JSON テキストで次の操作を実行できます。

JSON テキストを解析し、値を読み取るか、変更する。
JSON オブジェクトの配列を表形式に変換する。
変換された JSON オブジェクトに対して任意の Transact-SQL クエリを実行する。
Transact-SQL クエリの結果を JSON 形式で書式設定する。

Diagram showing the overview of built-in JSON support.

SQL Server と SQL Database の主な JSON 機能

SQL Server がその組み込みの JSON サポートで提供する主な機能について以下に説明します。

JSON テキストから値を抽出し、それらをクエリで使用する

データベーステーブルに格納されている JSON テキストがある場合は、組み込み関数を使用して JSON テキスト内の値を読み取るか、変更することができます。

ISJSON (Transact-SQL) は、文字列に有効な JSON が含まれているかどうかをテストします。
JSON_VALUE (Transact-SQL) は、JSON 文字列からスカラー値を抽出します。
JSON_QUERY (Transact-SQL) は、JSON 文字列からオブジェクトまたは配列を抽出します。
JSON_MODIFY (Transact-SQL) は、JSON 文字列の値を変更します。

例

次の例のクエリでは、jsonCol テーブルからリレーショナルデータと (People という名前の列に格納された) JSON データの両方を使用しています。

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 列から取得した値であるかの見分けは付きません。 JSON テキストからの値は、Transact-SQL クエリのすべての部分で使用できます (例: WHERE 句、ORDER BY 句、または GROUP BY 句、ウィンドウの集計など)。 JSON 関数は、JavaScript のような構文を使用して JSON テキスト内の値を参照します。

詳細については、「組み込み関数を使用した JSON データの検証、クエリ、変更 (SQL Server)」、「JSON_VALUE (Transact-SQL)」、「JSON_QUERY (Transact-SQL)」を参照してください。

JSON 値の変更

JSON テキストの一部を修正する必要がある場合は、JSON_MODIFY (Transact-SQL) 関数を使用して JSON 文字列のプロパティの値を更新し、更新された JSON 文字列を返します。次の例では、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;

結果セットは次のようになります。

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

JSON コレクションを行セットに変換する

SQL Server で JSON のクエリを実行するのにカスタムクエリ言語は不要です。 JSON データのクエリを実行するには、標準の T-SQL を使用することができます。 JSON データのクエリまたはレポートを作成する必要がある場合は、 OPENJSON 行セット関数を呼び出して JSON データを簡単に行と列に変換できます。詳細については、「OPENJSON で JSON データを解析し、変換する」を参照してください。

次の例では、OPENJSON を呼び出し、@json 変数に格納されたオブジェクトの配列を、標準の Transact-SQL SELECT ステートメントを使用してクエリを実行できる行セットに変換します。

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

結果セットは次のようになります。

ID	firstName	lastName	年齢	dateOfBirth
2	John	Smith	25
5	Jane	Smith		2005-11-04T12:00:00

OPENJSON では、JSON オブジェクトの配列がテーブルに変換され、各オブジェクトが 1 つの行として表わされ、キーと値のペアがセルとして返されます。出力は、次の規則を監視します。

OPENJSON は JSON 値を WITH 句で指定された型に変換します。
OPENJSON は、フラットなキーと値のペアと、入れ子になった階層構造のオブジェクトの両方を処理できます。
JSON テキストに含まれるすべてのフィールドを返す必要はありません。
JSON 値が存在しない場合、OPENJSON は NULL 値を返します。
オプションで型指定の後にパスを指定して、入れ子にされたプロパティを参照するか、異なる名前のプロパティを参照できます。
パス内の省略可能な strict プレフィックスでは、指定されたプロパティの値が JSON テキストに存在していなければならないことを指定します。

詳細については、「OPENJSON で JSON データを解析し、変換する」および「OPENJSON (Transact-SQL)」を参照してください。

JSON ドキュメントには、サブ要素と、標準のリレーショナル列に直接マップできない階層データがあります。ここでは、親エンティティとサブ配列を結合することで JSON 階層をフラット化することができます。

次の例では、配列内の 2 番目のオブジェクトは、個人のスキルを表すサブ配列を持っています。追加の OPENJSON 関数呼び出しを使用してすべてのサブオブジェクトを解析できます。

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 id,
    firstName,
    lastName,
    age,
    dateOfBirth,
    skill
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 テキストフラグメントとして返され、 OPENJSON 演算子を使用して別の APPLY 関数に渡されます。 2 番目の OPENJSON 関数は、JSON 配列を解析し、最初の OPENJSON の結果と結合される 1 つの列の行セットとして文字列値を返します。

結果セットは次のようになります。

ID	firstName	lastName	年齢	dateOfBirth	skill
2	John	Smith	25
5	Jane	Smith		2005-11-04T12:00:00	SQL
5	Jane	Smith		2005-11-04T12:00:00	C#
5	Jane	Smith		2005-11-04T12:00:00	Azure

OUTER APPLY OPENJSON は、最初のレベルのエンティティをサブ配列と結合し、フラット化された結果セットを返します。 JOIN により、すべてのスキルで 2 番目の行が繰り返されます。

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

Note

Azure Synapse Analytics データの JSON への変換や、JSON のエクスポートは、サポートされていません。

FOR JSON 句を SELECT ステートメントに追加して、SQL Server データまたは SQL クエリの結果を JSON として書式設定します。 FOR JSON を使用して、JSON 出力の形式設定をクライアントアプリケーションから SQL Server に委任します。詳細については、「FOR JSON を使用してクエリ結果を JSON として書式設定する」を参照してください。

次の例では、PATH モードを FOR JSON 句とともに使用しています。

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

FOR JSON 句は SQL 結果を JSON テキスト形式に設定し、JSON を理解するすべてのアプリケーションで使用できます。 PATH オプションでは、SELECT 句にドット区切りのエイリアスを使用して、クエリ結果内のオブジェクトを入れ子にします。

結果セットは次のようになります。

[
  {
    "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 として書式設定する」および「FOR 句 (Transact-SQL)」を参照してください。

SQL Server での JSON データのユースケース

SQL Server と Azure SQL Database での JSON のサポートにより、リレーショナルと NoSQL の概念が統合されます。リレーショナルから半構造化データへの変換、およびその逆が簡単にできます。しかし、JSON は既存のリレーショナルモデルに置き換わるものではありません。ここでは、SQL Server と SQL Database での JSON サポートによる利点が得られるユースケースをいくつか紹介します。

複雑なデータモデルを簡略化する

複数の子テーブルの代わりに JSON フィールドでデータモデルを非正規化することを検討してください。

小売および e コマースのデータを格納する

柔軟性のために非正規化されたモデルにさまざまな変数属性による製品情報を格納します。

ログとテレメトリデータの処理

Transact-SQL 言語を活用して JSON ファイルとして格納されたログデータの読み込み、クエリ、分析を行います。

半構造化された IoT データを格納する

IoT データのリアルタイム分析が必要なときは、受信データをストレージの場所にステージングせずに、データベースに直接読み込みます。

REST API の開発を簡略化する

データベースのリレーショナルデータを、Web サイトをサポートする REST API で使用される JSON 形式に簡単に変換します。

リレーショナルデータと JSON データを結合する

SQL Server は、標準の Transact-SQL 言語を使用してリレーショナルデータと JSON データの両方を格納および処理するハイブリッドモデルを提供します。テーブル内の JSON ドキュメントのコレクションを整理し、それらの間のリレーションシップを確立、テーブルに格納された厳密に型指定されたスカラー列を JSON 列に格納された柔軟なキーと値のペアに結合し、完全な Transact-SQL を使用して 1 つ以上のテーブルのスカラー値と JSON 値の両方についてクエリを実行します。

JSON テキストは VARCHAR または NVARCHAR 列に格納され、プレーンテキストとしてインデックスが付けられます。テキストをサポートするすべての SQL Server の機能またはコンポーネントは JSON をサポートします。そのため、JSON とその他の SQL Server 機能間の対話には、ほとんど制約がありません。 JSON はメモリ内テーブルやテンポラルテーブルに格納できるほか、行レベルのセキュリティの述語を JSON テキストに適用することなどができます。

ここでは、SQL Server で組み込みの JSON サポートの使用方法を示すユースケースを紹介します。

JSON データの SQL Server への格納とインデックスの追加

JSON はテキスト形式なので、SQL Database の NVARCHAR 列に JSON ドキュメントを格納できます。 NVARCHAR 型は、すべての SQL Server サブシステムでサポートされているので、クラスター化列ストアインデックスが付いたテーブル、メモリ最適化テーブル、または OPENROWSET または PolyBase を使用して読み取ることができる外部ファイルに JSON ドキュメントを格納することができます。

JSON データの SQL Server への保存、インデックスの追加、最適化のオプションに関する詳細については、次の記事を参照してください。

SQL Server に JSON ファイルを読み込む

ファイルに格納された情報は、標準の JSON または行区切りの JSON 形式に書式設定できます。 SQL Server は JSON ファイルのコンテンツをインポートし、OPENJSON または JSON_VALUE 関数を使用してそれを解析してテーブルに読み込むことができます。

JSON ドキュメントが SQL Server がアクセスできるローカルファイル、共有ネットワークドライブ、Azure File の場所に格納されている場合は、一括インポートを使用して SQL Server に JSON データを読み込むことができます。
行区切りの JSON ファイルが Azure Blob Storage または Hadoop ファイルシステムに格納されている場合、PolyBase を使用して JSON テキストを読み込み、Transact-SQL コードでそれを解析してテーブルに読み込むことができます。

SQL Server に JSON データをインポートする

JSON データを外部サービスから SQL Server に読み込む必要がある場合は、アプリケーションレイヤー内でデータを解析する代わりに、OPENJSON を使用してデータを SQL Server にインポートできます。

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 <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, N'$') 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 フレームワークからのパラメーターとして送信されたり、外部ファイルから読み込まれたりします。結果は、JSON テキストから SQL Server テーブルに簡単に挿入、更新、マージできます。

SQL クエリで JSON データを分析する

レポート作成のために JSON データをフィルター処理または集計する必要がある場合は、OPENJSON を使用して JSON をリレーショナル形式に変換できます。その後、標準の Transact-SQL と組み込み関数を使ってレポートを用意することができます。

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 テキストからの値の両方を使用できます。 JSON_VALUE(Tab.json, '$.Status') 式にインデックスを追加して、クエリのパフォーマンスを向上させることができます。詳細については、「JSON データへのインデックスの追加」を参照してください。

JSON として書式設定された SQL Server テーブルからデータを返す

データベース層からデータを取得し、それを JSON 形式で返す Web サービスがある場合、または JSON 形式に設定されたデータを受け取る JavaScript フレームワークまたはライブラリがある場合、JSON 出力を SQL クエリで直接設定できます。コードを記述するかライブラリを追加して、表形式のクエリ結果を変換し、それからオブジェクトを JSON 形式にシリアル化する代わりに、FOR JSONを使用して JSON 形式への設定を SQL Server に委任できます。

たとえば、OData の仕様に準拠した JSON 出力を生成するとします。 Web サービスは、次の形式の要求と応答を求めています。

要求: /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"}

この OData URL は、ID が 1 の製品の ProductID 列と ProductName 列に対する要求を表します。 FOR JSON を使用して、出力を 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 によって処理されます。 SQL Server ではクエリ結果を OData JSON や GeoJSON など、任意の形式に設定することもできます。

AdventureWorks サンプルデータベースを使用して、組み込みの JSON サポートを試用できます。

AdventureWorks サンプルデータベースを入手するには、GitHub から少なくともデータベースファイルとサンプルおよびスクリプトファイルをダウンロードしてください。

SQL Server のインスタンスにサンプルデータベースを復元したら、サンプル JSON Sample Queries procedures views and indexes.sql ファイルを展開し、JSON フォルダーからファイルを開きます。このファイルのスクリプトを実行して JSON データとして既存のデータの一部の形式を再度設定し、JSON データに対してサンプルクエリとレポートをテストしてから、JSON データにインデックスを付けて JSON をインポートおよびエクスポートします。

ファイルに含まれているスクリプトでは、次のことを実行できます。

既存のスキーマを非正規化して JSON データの列を作成する。
- SalesReasons、SalesOrderDetails、SalesPerson、Customer、およびその他の販売注文に関する情報を含むテーブルからの情報を、SalesOrder_json テーブルの JSON 列に格納します。
- EmailAddresses および PersonPhone テーブルからの情報を、Person_json テーブルに JSON オブジェクトの配列として格納します。
JSON データに対してクエリを実行するプロシージャとビューを作成する。
JSON データにインデックスを追加する。 JSON のプロパティとフルテキストインデックスにインデックスを作成する。
JSON をインポートおよびエクスポートする。 Person テーブルと SalesOrder テーブルのコンテンツを JSON の結果としてエクスポートする、および JSON 入力をインポートして Person テーブルと SalesOrder テーブルを更新するプロシージャを作成して実行します。
サンプルクエリを実行する。手順 2 と 4 で作成したストアドプロシージャとビューを呼び出すいくつかのクエリを実行します。
スクリプトをクリーンアップする。手順 2 と 4 で作成したストアドプロシージャとビューを保持する場合は、この部分を実行しないでください。

SQL Server と Azure SQL Database の JSON の詳細情報

Microsoft ビデオ

SQL Server と Azure SQL Database に組み込まれている JSON のサポートの概要は、次のビデオをご覧ください。

SQL Server の JSON データ

SQL Server と SQL Database の主な JSON 機能

JSON テキストから値を抽出し、それらをクエリで使用する

JSON 値の変更

JSON コレクションを行セットに変換する

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

SQL Server での JSON データのユースケース

複雑なデータモデルを簡略化する

小売および e コマースのデータを格納する

ログとテレメトリデータの処理

半構造化された IoT データを格納する

REST API の開発を簡略化する

リレーショナルデータと JSON データを結合する

JSON データの SQL Server への格納とインデックスの追加

SQL Server に JSON ファイルを読み込む

SQL Server に JSON データをインポートする

SQL クエリで JSON データを分析する

JSON として書式設定された SQL Server テーブルからデータを返す

AdventureWorks サンプルデータベースを使用して、組み込みの JSON サポートを試用できます。

SQL Server と Azure SQL Database の JSON の詳細情報

Microsoft ビデオ

フィードバック

フィードバック

その他のリソース

SQL Server の JSON データ

SQL Server と SQL Database の主な JSON 機能

JSON テキストから値を抽出し、それらをクエリで使用する

JSON 値の変更

JSON コレクションを行セットに変換する

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

SQL Server での JSON データのユース ケース

複雑なデータ モデルを簡略化する

小売および e コマースのデータを格納する

ログとテレメトリ データの処理

半構造化された IoT データを格納する

REST API の開発を簡略化する

リレーショナル データと JSON データを結合する

JSON データの SQL Server への格納とインデックスの追加

SQL Server に JSON ファイルを読み込む

SQL Server に JSON データをインポートする

SQL クエリで JSON データを分析する

JSON として書式設定された SQL Server テーブルからデータを返す

AdventureWorks サンプル データベースを使用して、組み込みの JSON サポートを試用できます。

SQL Server と Azure SQL Database の JSON の詳細情報

Microsoft ビデオ

関連するコンテンツ

フィードバック

フィードバック

その他のリソース

SQL Server での JSON データのユースケース

複雑なデータモデルを簡略化する

ログとテレメトリデータの処理

リレーショナルデータと JSON データを結合する

AdventureWorks サンプルデータベースを使用して、組み込みの JSON サポートを試用できます。