SQL ServerでJSONデータを扱う便利な関数の活用方法

現代のウェブ開発において、データの形式としてJSONはその柔軟性とシンプルさから広く採用されています。SQL Serverが提供するJSON関数は、この重要なデータ形式を扱う際に大きな助けとなります。これらの関数を使えば、データベースとアプリケーション間のデータのやり取りが容易になり、開発プロセスがよりスムーズかつ効率的に進むでしょう。以下では、SQL ServerのJSON関数を用いた具体的な用途と例を紹介します。

SQL ServerでのJSON関数の活用方法

SQL ServerでのJSON関数の活用方法

FOR JSON：テーブルデータをJSON形式に変換

FOR JSON句は、リレーショナルテーブルのデータをJSON形式に自動的に変換し、アプリケーションがこの形式を要求する場合に応答データを提供する手段を提供します。この機能は、データをAPI経由で外部のシステムやクライアントに送信する際に特に便利です。
FOR JSON句を使用すると、SELECTクエリの結果をJSON形式で出力できます。この句は主に二つのモード、「AUTO」と「PATH」を提供しています。AUTOモードは、クエリの結果を基にして自動的にJSONオブジェクトを生成します。一方、PATHモードでは、より複雑なJSON構造を指定することが可能で、出力するJSONの形状を細かく制御できます。

テーブル: Products

ProductID	ProductName	Price
1	Apple	100
2	Banana	50

SQL文:

Autoモード

SELECT ProductID, ProductName, Price
FROM (VALUES (1, 'Apple', 100), (2, 'Banana', 50)) Products(ProductID, ProductName, Price)
FOR JSON AUTO;

SELECT ProductID, ProductName, Price

FROM (VALUES (1, 'Apple', 100), (2, 'Banana', 50)) Products(ProductID, ProductName, Price)

FOR JSON AUTO;

出力結果:

[
  {"ProductID":1,"ProductName":"Apple","Price":100},
  {"ProductID":2,"ProductName":"Banana","Price":50}
]

[

{"ProductID":1,"ProductName":"Apple","Price":100},

{"ProductID":2,"ProductName":"Banana","Price":50}

]

PATHモード

SELECT ProductName AS "Product.Name", Price AS "Product.Price"
FROM (VALUES ('Apple', 100), ('Banana', 50)) Products(ProductName, Price)
FOR JSON PATH;

SELECT ProductName AS "Product.Name", Price AS "Product.Price"

FROM (VALUES ('Apple', 100), ('Banana', 50)) Products(ProductName, Price)

FOR JSON PATH;

出力結果:

[
  {"Product":{"Name":"Apple","Price":100}},
  {"Product":{"Name":"Banana","Price":50}}
]

[

{"Product":{"Name":"Apple","Price":100}},

{"Product":{"Name":"Banana","Price":50}}

]

OPENJSON：JSON文字列をテーブル形式に変換

OPENJSON関数は、JSON形式のデータをテーブル形式に変換し、SQLクエリを通じてその内容を簡単に解析できるようにする機能を提供します。この変換プロセスにより、JSONデータをSQL Serverのテーブルに格納することが可能になり、SQLの強力なクエリ機能を用いてデータの分析や操作が行えるようになります。
OPENJSONは、JSONデータを行と列の形式に変換する際に使用されるテーブル値関数です。この関数を使用すると、JSONテキストをSQLテーブルのように扱い、SELECT文を使ってデータをクエリしたり、JOINやAGGREGATE関数などのSQL操作を実行することができます。

JSON文字列:

'[{"ProductName": "Apple", "Price": 100}, {"ProductName": "Banana", "Price": 50}]'

1	'[{"ProductName": "Apple", "Price": 100}, {"ProductName": "Banana", "Price": 50}]'

SQL文:

SELECT *
FROM OPENJSON(@json)
WITH (ProductName NVARCHAR(100), Price INT);

SELECT *

FROM OPENJSON(@json)

WITH (ProductName NVARCHAR(100), Price INT);

出力結果:

ProductName	Price
Apple	100
Banana	50

JSON_VALUE：JSONから特定の値を取得

JSON_VALUE関数は、この形式のデータから特定の値を簡単に取り出すことができるツールです。この関数は、JSONデータ内の特定のプロパティに対応する値を抽出する際に使用されます。これにより、複雑なデータ構造内から必要な情報を効率的に取得でき、データの処理や分析を容易に行うことができます。
たとえば、顧客情報を管理するアプリケーションがあり、そのデータがJSON形式で保存されているとします。顧客の名前、年齢、住所などが含まれている場合、特定の顧客の名前を取得したい場合にJSON_VALUE関数を使用します。

SQL文:

SELECT JSON_VALUE(@json, '$.name') AS Name

1	SELECT JSON_VALUE(@json, '$.name') AS Name

出力結果:

John

John

JSON_QUERY：JSONからオブジェクトまたは配列を取得

JSON_QUERY関数は、JSONデータからオブジェクトや配列を取得するために特化したツールです。この関数は、JSON形式のデータ内に含まれるオブジェクト全体や配列を抽出する際に使用され、単一の値を取り出すJSON_VALUE関数とは異なります。
たとえば、Webアプリケーションがユーザー情報をJSON形式で管理しており、ユーザーの住所情報が配列として保存されている場合、JSON_QUERY関数を使用してその配列全体を取得できます。

SQL文:

SELECT JSON_QUERY(@json, '$.addresses') AS Addresses

1	SELECT JSON_QUERY(@json, '$.addresses') AS Addresses

出力結果:

[{"street":"123 Main St", "city":"New York"}]

1	[{"street":"123 Main St", "city":"New York"}]

JSON_MODIFY：JSONデータ内の値を更新

JSON_MODIFY関数は、既存のJSONデータ内の値を更新する強力なツールです。この関数を使用すると、指定したパスの位置にある値を新しい値で置き換えることができ、JSONデータの動的な編集が可能になります。
たとえば、ユーザープロファイル情報を管理するアプリケーションがあり、ユーザーが自分の名前を更新したい場合を考えてみましょう。次のSQL文は、JSONデータ内の「name」プロパティの値を「Jane」に更新します。

SQL文:

SET @json = JSON_MODIFY(@json, '$.name', 'Jane')

1	SET @json = JSON_MODIFY(@json, '$.name', 'Jane')

出力結果:

{"name":"Jane", "age":30}

1	{"name":"Jane", "age":30}

おすすめ情報

ある程度SQLに慣れてくるとより複雑なSQLを組むようになり、分析関数（ウインドウ関数）の壁にぶち当たります。本書は私が初級から抜け出すのになったきっかけの本です。比較的新しい関数なので古いSQLの本には載ってません。昔ながらのSQLを書く人はウインドウ関数の理解に苦しむケースが多いと思います。一歩先に進むためにも苦手意識を克服したいですね。