SQL Server で T-SQL を使って JSON を解析する方法：包括的なガイド

2024-06-16

SQL Server で T-SQL を使って JSON を解析する方法

JSON データを解析するには、主に以下の 2 つの方法があります。

OPENJSON 関数は、JSON テキストをテーブル形式に変換します。変換されたテーブルには、JSON オブジェクトのプロパティが列として、プロパティの値が行として格納されます。

SELECT *
FROM OPENJSON(@jsonText);

このクエリは、@jsonText 変数に格納されている JSON テキストを解析し、すべてのパラメータと値を返します。

JSON_VALUE 関数は、JSON データから特定の値を取得します。引数として JSON テキストと、取得する値のパスを指定します。

SELECT JSON_VALUE(@jsonText, '$.prop1');

JSON データの値を取得するには、OPENJSON または JSON_VALUE 関数を使用できます。これらの関数を使用して、JSON オブジェクトのプロパティの値を挿入、更新、削除することもできます。

例：name プロパティの値を "John Doe" に更新する

UPDATE myTable
SET jsonData = JSON_MODIFY(jsonData, '$.name', 'John Doe')
WHERE id = 123;

このクエリは、myTable テーブルの id が 123 である行の jsonData 列の name プロパティの値を "John Doe" に更新します。

JSON 配列は、OPENJSON 関数を使用してリレーショナルテーブルに変換できます。変換されたテーブルには、各要素が 1 行として格納されます。

例：JSON 配列を解析し、各要素を個別の行として表示する

SELECT *
FROM OPENJSON(@jsonArray);

その他の T-SQL JSON 関数

SQL Server には、JSON データの操作に使用できる他にもいくつかの関数があります。

ISJSON: JSON テキストかどうかを判定します。
JSON_QUERY: JSON データに対して XPath クエリを実行します。
JSON_MODIFY: JSON データを変更します。

これらの関数の詳細については、Microsoft のドキュメントを参照してください。

サンプルコード：T-SQL で JSON を解析する

サンプル 1：JSON テキストを解析してテーブルに格納する

この例では、次の JSON テキストを解析して Employees テーブルに格納します。

[
  { "id": 1, "name": "John Doe", "department": "IT" },
  { "id": 2, "name": "Jane Doe", "department": "Sales" },
  { "id": 3, "name": "Peter Jones", "department": "Marketing" }
]

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  { "id": 1, "name": "John Doe", "department": "IT" },
  { "id": 2, "name": "Jane Doe", "department": "Sales" },
  { "id": 3, "name": "Peter Jones", "department": "Marketing" }
]';

CREATE TABLE Employees (
  id INT PRIMARY KEY,
  name NVARCHAR(50),
  department NVARCHAR(50)
);

INSERT INTO Employees
SELECT id, name, department
FROM OPENJSON(@json);

このコードはまず、JSON テキストを @json 変数に格納します。次に、Employees テーブルを作成し、id、name、department の 3 つの列を定義します。最後に、OPENJSON 関数を使用して JSON テキストを解析し、Employees テーブルにデータを挿入します。

サンプル 2：JSON 値を取得する

DECLARE @jsonText NVARCHAR(MAX);

SET @jsonText = N'{ "name": "John Doe", "age": 30, "city": "Seattle" }';

SELECT JSON_VALUE(@jsonText, '$.name');

このコードはまず、JSON テキストを @jsonText 変数に格納します。次に、JSON_VALUE 関数を使用して name プロパティの値を取得します。

サンプル 3：JSON 配列を解析する

DECLARE @jsonArray NVARCHAR(MAX);

SET @jsonArray = N'[
  { "name": "Apple", "price": 9.99 },
  { "name": "Orange", "price": 4.99 },
  { "name": "Banana", "price": 2.99 }
]';

SELECT *
FROM OPENJSON(@jsonArray);

このコードはまず、JSON 配列を @jsonArray 変数に格納します。次に、OPENJSON 関数を使用して JSON 配列を解析し、各要素を個別の行として表示します。

サンプル 4：JSON データを更新する

DECLARE @jsonText NVARCHAR(MAX);

SET @jsonText = N'{ "name": "John Doe", "age": 30, "city": "Seattle" }';

UPDATE @jsonText = JSON_MODIFY(@jsonText, '$.age', 31);

SELECT @jsonText;

このコードはまず、JSON テキストを @jsonText 変数に格納します。次に、JSON_MODIFY 関数を使用して age プロパティの値を 31 に更新します。最後に、更新された JSON テキストを @jsonText 変数に格納します。

これらのサンプルは、T-SQL で JSON を解析する方法をいくつか示しています。詳細については、Microsoft のドキュメントを参照してください。

SQL Server で JSON を解析するその他の方法

JSON_QUERY 関数は、JSON データに対して XPath クエリを実行して、特定のノードまたは値を抽出します。この関数は、階層化された JSON データを解析するのに役立ちます。

例：customers 配列からすべての name プロパティを抽出する

SELECT JSON_QUERY(@jsonText, '$.customers[*].name')
FROM @jsonTable;

このクエリは、@jsonTable テーブルの jsonData 列に格納されている JSON テキストを解析し、customers 配列内のすべての name プロパティを抽出します。

FOR JSON 句は、SQL Server クエリの結果を JSON テキストに変換します。この句を使用して、JSON 配列やオブジェクトを生成できます。

例：Employees テーブルのデータを JSON 配列に変換する

SELECT *
FROM Employees
FOR JSON AUTO, PATH;

このクエリは、Employees テーブルのすべてのデータを選択し、それを JSON 配列に変換します。生成された JSON テキストは、@json 変数に格納されます。

OPENROWSET 関数と JSON ファイル

OPENROWSET 関数を使用して、JSON ファイルを SQL Server テーブルとして開くことができます。これにより、JSON ファイルのデータを SQL クエリで処理することができます。

例：data.json ファイルのデータを Employees テーブルにロードする

BULK INSERT Employees
FROM OPENROWSET('JSON;TextFile="C:\data.json"', FORMAT = 'JSON')
WITH (DATAFILETYPE = 'NVARCHAR(MAX)', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');