組み込み関数を使用して JSON データを処理する
ヒント
詳細については、「 テキストと画像 」タブを参照してください。
eコマース アプリケーションで顧客設定と注文メタデータを JSON ドキュメントとして格納するシナリオを考えてみましょう。 モバイル アプリは JSON 形式でショッピング カート データを送信し、レポート システムは Web API の JSON として製品カタログをエクスポートする必要があります。 データベースで JSON を直接操作すると、アプリケーション層変換が不要になり、データ処理の効率が維持されます。
Fabric の SQL Server、Azure SQL、および SQL データベースには、T-SQL で JSON データを直接解析、クエリ、作成、変換できる組み込みの JSON サポートが用意されています。 このユニットでは、JSON 関数を使用して、値の抽出、JSON 出力の構築、JSON 配列へのデータの集計、JSON コンテンツの検証を行う方法について説明します。
JSON_VALUEとJSON_QUERYを使用して値を抽出する
データベースに格納されている JSON を使用する場合は、フィルター処理、結合、または表示のために特定の値を抽出する必要があります。 SQL Server には、この目的のために次の 2 つの関数が用意されています。
JSON_VALUE() JSON 文字列からスカラー値 (文字列、数値、ブール値) を抽出します。
DECLARE @json NVARCHAR(MAX) = N'{
"customer": {
"id": 12345,
"name": "Contoso Ltd",
"active": true
},
"orderTotal": 1599.99
}';
SELECT
JSON_VALUE(@json, '$.customer.id') AS CustomerID,
JSON_VALUE(@json, '$.customer.name') AS CustomerName,
JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;
結果セットは次のようになります。
CustomerID CustomerName OrderTotal
---------- ------------ ----------
12345 Contoso Ltd 1599.99
この関数は、パス式を使用して JSON 構造体内を移動し、値を NVARCHAR(4000) 文字列として返します。 計算や比較に必要に応じて、結果を他のデータ型にキャストできます。
JSON_QUERY() JSON オブジェクトまたは配列 (非スカラー値) を抽出します。
DECLARE @json NVARCHAR(MAX) = N'{
"customer": {
"id": 12345,
"name": "Contoso Ltd"
},
"items": [
{"product": "Widget", "qty": 5},
{"product": "Gadget", "qty": 3}
]
}';
SELECT
JSON_QUERY(@json, '$.customer') AS CustomerObject,
JSON_QUERY(@json, '$.items') AS ItemsArray;
結果セットは次のようになります。
CustomerObject ItemsArray
-------------------------------------- ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"} [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]
JSON_VALUE()とは異なり、JSON_QUERY()は JSON 構造を保持し、オブジェクトと配列を有効な JSON 文字列として返します。この文字列は、格納、他の関数への渡し、またはアプリケーションへの戻りを行うことができます。
パス式では、次の例のように、 $ を使用してルート要素を表し、入れ子になったプロパティのドット表記と配列要素の角かっこ表記を使用します。
-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;
結果は次のようになります。
FirstProduct
------------
Widget
配列インデックスは 0 から始まるため、 $.items[0] は最初の要素を参照します。 この構文を使用して、位置がわかっているときに特定の項目を抽出したり、すべての配列要素を処理する必要がある場合に OPENJSON と組み合わせたりします。
ヒント
比較または計算にスカラー値が必要な場合は、 JSON_VALUE() を使用します。 入れ子になったオブジェクトまたは配列の JSON 構造を保持する必要がある場合は、 JSON_QUERY() を使用します。
を使用して JSON 配列を解析する OPENJSON
OPENJSON は、JSON データをリレーショナル行セットに変換するテーブル値関数です。 JSON データをリレーショナル テーブルと結合したり、配列要素を個別に処理したりするには、この関数を使用します。
次のクエリでは、JSON 配列が既定のスキーマを持つ行に解析されます。
DECLARE @json NVARCHAR(MAX) = N'[
{"id": 1, "name": "Widget", "price": 29.99},
{"id": 2, "name": "Gadget", "price": 49.99},
{"id": 3, "name": "Gizmo", "price": 19.99}
]';
SELECT * FROM OPENJSON(@json);
結果セットは次のようになります。
key value type
--- -------------------------------------------- ----
0 {"id": 1, "name": "Widget", "price": 29.99} 5
1 {"id": 2, "name": "Gadget", "price": 49.99} 5
2 {"id": 3, "name": "Gizmo", "price": 19.99} 5
スキーマがない場合、 OPENJSON は、 key (配列インデックスまたはプロパティ名)、 value (JSON コンテンツ)、 type (JSON データ型を示す数値: 0=null、1=string、2=number、3=boolean、4=array、5=object) の 3 つの列を返します。
次のクエリでは、適切なデータ型を持つ特定の列を抽出する明示的なスキーマを定義します。
SELECT
ProductID,
ProductName,
Price
FROM OPENJSON(@json)
WITH (
ProductID INT '$.id',
ProductName NVARCHAR(100) '$.name',
Price DECIMAL(10,2) '$.price'
);
結果セットは次のようになります。
ProductID ProductName Price
--------- ----------- ------
1 Widget 29.99
2 Gadget 49.99
3 Gizmo 19.99
WITH句は、JSON プロパティを型指定された列にマップします。 この方法では、計算と比較に適したデータ型が提供され、必要なプロパティのみを選択できます。
OPENJSONを使用してテーブル データとCROSS APPLYを組み合わせる:
-- Assuming Orders table has a JSON column called OrderDetails
SELECT
o.OrderID,
o.CustomerID,
items.ProductName,
items.Quantity,
items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
ProductName NVARCHAR(100) '$.product',
Quantity INT '$.qty',
UnitPrice DECIMAL(10,2) '$.price'
) AS items;
注
コードでOPENJSONでCROSS APPLYを使用する場合、NULLまたは空の JSON 値を持つメイン テーブルの行は結果に表示されません。 JSON データのない行を含める必要がある場合は、 OUTER APPLY を使用します。
JSON_OBJECTを使用して JSON を構築するJSON_ARRAY
SQL Server 2022 では、直感的な JSON 構築のための JSON_OBJECT 関数と JSON_ARRAY 関数が導入されました。
JSON_OBJECT() は、キーと値のペアから JSON オブジェクトを作成します。 次の例は、製品の JSON オブジェクトを構築する方法を示しています。
SELECT JSON_OBJECT(
'id': ProductID,
'name': Name,
'price': ListPrice,
'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;
結果は次のようになります。
ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}
この関数は、データ型の変換と、文字列値の特殊文字に対する適切な JSON エスケープを自動的に処理します。
JSON_ARRAY() は、値から JSON 配列を作成します。 次の例では、JSON 配列を構築します。
SELECT JSON_ARRAY(
'SQL Server',
'Azure SQL Database',
'SQL Database in Fabric'
) AS Platforms;
結果は次のようになります。
Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]
列の値、変数、またはリテラル値を JSON_ARRAY()に渡すことができます。 この関数は、入力の種類に関係なく、適切に書式設定された JSON 配列を作成します。
次に、これらの関数を組み合わせて、入れ子になった JSON 構造を構築します。 次の例では、顧客と合計情報を含む完全な注文 JSON オブジェクトを作成します。
SELECT JSON_OBJECT(
'orderId': soh.SalesOrderID,
'orderDate': soh.OrderDate,
'customer': JSON_OBJECT(
'id': c.CustomerID,
'name': c.CompanyName
),
'totals': JSON_OBJECT(
'subtotal': soh.SubTotal,
'tax': soh.TaxAmt,
'total': soh.TotalDue
)
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;
結果は次のようになります。
OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}
JSON_OBJECT呼び出しを入れ子にすることで、アプリケーションの想定される形式に一致する階層構造が作成されます。 この方法は文字列連結よりもクリーンであり、有効な JSON 出力が保証されます。
を使用してデータを集計する JSON_ARRAYAGG
JSON_ARRAYAGG は、複数の行の値を 1 つの JSON 配列に集計します。 この関数は、正規化されたリレーショナル データから非正規化された JSON 出力を作成する場合に役立ちます。
SELECT
c.CustomerID,
c.CompanyName,
JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;
結果は次のようになります。
CustomerID CompanyName OrderIds
---------- ------------------- ------------------
29825 Contoso Retail [71774,71776,71780]
29847 Adventure Works [71782,71784]
この関数は、グループ化された行から一致するすべての値を収集し、それらを 1 つの JSON 配列に結合します。 このメソッドは、正規化されたデータベース テーブルから非正規化された API 応答を作成する場合に役立ちます。
JSON_ARRAYAGGとJSON_OBJECTを組み合わせて、複雑なオブジェクトの配列を作成できます。
SELECT
pc.Name AS Category,
JSON_ARRAYAGG(
JSON_OBJECT(
'id': p.ProductID,
'name': p.Name,
'price': p.ListPrice
)
) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;
次の結果が得られます。
Category Products
-------------- --------------------------------------------------------------------------
Road Bikes [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]
Important
JSON_ARRAYAGG および JSON_OBJECT/JSON_ARRAY 関数は、SQL Server 2022 以降、Azure SQL Database、および Microsoft Fabric の SQL データベースで使用できます。 以前のバージョンでは、同様の機能に FOR JSON PATH を使用します。
で JSON を検証して確認する JSON_CONTAINS
外部ソースからの JSON データの形式が正しくないか、予期されるプロパティが見つからないか、予期しない値が含まれている可能性があります。 無効な JSON またはパスがないから値を抽出しようとすると、クエリエラーが発生したり、データの問題を隠す誤解を招く NULL 結果が返されたりする可能性があります。
堅牢な JSON 処理では、防御的なコーディングが必要です。JSON が適切な形式であることを検証してから解析します。 次に、値を抽出する前に予想されるパスが存在することを確認し、値がビジネス ロジックで使用する前に期待値と一致することを確認します。 SQL Server には、処理の各段階で JSON コンテンツを検証するのに役立ついくつかの関数が用意されています。
lax と strict のパス モードを理解する
エラー処理を制御する 2 つのモードで JSON パス式を使用できます。
DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';
-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;
-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;
結果は次のようになります。
LaxResult
---------
NULL
-- Strict mode raises: Property cannot be found on the specified JSON path.
不足しているプロパティが必要であり、laxを返す必要がある場合は、NULL モード (既定) を使用します。 不足しているプロパティがエラーを発生させるデータの問題を示す場合は、 strict モードを使用します。
ISJSON は、文字列に有効な JSON が含まれているかどうかを検証します。 次の例は、ISJSON を使用する方法を示しています。
SELECT
ISJSON('{"name": "test"}') AS ValidJson, -- Returns 1
ISJSON('not valid json') AS InvalidJson, -- Returns 0
ISJSON(NULL) AS NullJson; -- Returns NULL
結果は次のようになります。
ValidJson InvalidJson NullJson
--------- ----------- --------
1 0 NULL
無効なデータを適切に処理するには、WHERE 句で有効な JSON を含む行をフィルター処理するため、または CASE 式で ISJSON を使用します。
JSON_PATH_EXISTS は、次の例のように、JSON ドキュメントに特定のパスが存在するかどうかを確認します。
DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';
SELECT
JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;
結果は次のようになります。
HasName HasEmail
------- --------
1 0
この関数は、パスが存在する場合は 1 を返し、存在しない場合は 0 を返します。 厳密モードで JSON_VALUE を呼び出す前に使用するか、さまざまな構造で JSON を条件付きで処理する場合に使用します。
次の例のように、 JSON_CONTAINS を使用して、JSON ドキュメントに特定の値またはオブジェクトが含まれているかどうかを確認します。
DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';
SELECT
JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;
結果は次のようになります。
HasSqlTag HasPythonTag
--------- ------------
1 0
計算列を使用して JSON クエリを最適化する
特定の JSON プロパティを頻繁にクエリする場合、データベース エンジンは、すべてのクエリのすべての行の JSON ドキュメントを解析する必要があります。 数千行または数百万行のテーブルの場合、この繰り返しの解析によって大幅なオーバーヘッドが発生します。 計算列を使用すると、JSON 値を 1 回抽出し、インデックス作成をサポートするクエリ可能な形式で格納できます。
JSON 解析がパフォーマンスに影響を与える理由
各行に製品属性を含む JSON ドキュメントが含まれる 100,000 件の製品レコードを含むテーブルを考えてみましょう。 カテゴリ別のクエリ フィルター処理では、次の条件を満たす必要があります。
- テーブルから各行を読み取る
- category プロパティを検索するには、JSON ドキュメントを解析します
- 値を抽出して比較する
最適化を行わないと、単純なフィルターでも、すべての行で JSON 解析を行う完全なテーブル スキャンが必要になります。
JSON プロパティの計算列を作成する
計算列は JSON プロパティを自動的に抽出し、次の例のように通常の列として使用できるようにします。
-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');
-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';
結果は次のようになります。
ProductID ProductName ProductCategory
--------- ------------------- ---------------
101 Wireless Mouse Electronics
102 USB Keyboard Electronics
103 HD Monitor Electronics
既定では、計算列は仮想です。 データベースはクエリ時に値を計算しますが、JSON 抽出を最適化できます。 パフォーマンスをさらに向上させるには、次の例のように計算列を保持できます。
-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;
永続化された列は抽出された値をディスクに格納するため、JSON は INSERT 操作と UPDATE 操作中にのみ解析され、 SELECT クエリ中には解析されません。
インデックスを追加してフィルター処理を高速化する
実際のパフォーマンスの向上は、計算列のインデックス作成によって得られます。
-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);
-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';
インデックスがない場合、クエリは 100,000 行すべてをスキャンします。 インデックスを使用すると、クエリ エンジンはインデックスシークを実行し、一致する行のみを取得します。 このインデックスを使用すると、クエリ時間を秒単位からミリ秒に短縮できます。
複数の JSON プロパティのインデックスを作成する
複数の JSON プロパティでフィルター処理するクエリの場合は、計算列と複合インデックスを作成します。
-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;
-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);
-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);
カテゴリとブランドでフィルター処理したり、価格で並べ替えたりするクエリで、これらのインデックスを効率的に使用できるようになりました。
ヒント
頻繁にアクセスされる JSON プロパティの場合、インデックスを持つ計算列は、クエリ時の JSON の解析と比較してクエリのパフォーマンスを向上させることができます。 クエリ パターンを監視し、 WHERE、 JOIN、または ORDER BY 句で使用されるプロパティの計算列を作成します。
FOR JSON を使用してリレーショナル データを JSON に変換する
クエリからの包括的な JSON 出力には、 FOR JSON PATH または FOR JSON AUTOを使用します。
SELECT
p.ProductID,
p.Name,
p.ListPrice,
pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');
結果は次のようになります。
{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}
FOR JSON PATH では、列のエイリアスを使用して JSON 構造を制御できます。 エイリアスでドット表記を使用して、入れ子になったオブジェクトを作成します。
SELECT
p.ProductID AS 'product.id',
p.Name AS 'product.name',
pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;
結果は次のようになります。
[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]
列の別名'product.id'は、product プロパティを持つ入れ子になったid オブジェクトを作成します。 この手法を使用すると、後処理を行わずに、API の予想される形式に合わせて出力を整形できます。
SQL Server の JSON 関数の詳細については、「SQL Server の JSON データ 」と 「JSON 関数」を参照してください。