SQL ServerのOPENJSON関数でデータ連携を効率化する

はじめに

SQL Serverでは、JSON形式のデータを扱うための関数としてOPENJSON関数が提供されています。
この記事では、OPENJSON関数の基本的な使い方から実践的な活用例、そして従来のOPENXML関数との比較について紹介します。

OPENJSONとは

OPENJSON関数は、JSON形式のテキストデータを解析し、行セット（テーブルのような形式のデータ）に変換するテーブル値関数です。これにより、JSONデータをリレーショナルデータベースのテーブルと同じように扱い、SELECT文のFROM句などで利用できるようになります。

利用条件

互換性レベル130 (SQL Server 2016) 以降であることが必要です。

公式ドキュメント: OPENJSON (Transact-SQL)

基本的な使い方

以下は、配列形式のJSON データをOPENJSONで解析する例です。

DECLARE @json nvarchar(max) = N'
[
  { "OrderID": 1, "Amount": 2000, "Status": "shipped" },
  { "OrderID": 2, "Amount": 1500, "Status": "pending" }
]';

SELECT
    o.OrderID,
    o.Amount,
    o.Status
FROM OPENJSON(@json)
WITH (
    OrderID int           '$.OrderID',
    Amount  int           '$.Amount',
    Status  nvarchar(50)  '$.Status'
) AS o;

このクエリを実行すると、JSONデータが以下のようなテーブル形式で取得できます。

OrderID	Amount	Status
1	2000	shipped
2	1500	pending

実践的な活用例

1. ストアドプロシージャ間でのデータ受け渡し

複数の値や複雑な構造を持つデータをストアドプロシージャ間で受け渡す際、OPENJSONを使うとシンプルかつ柔軟に実現できます。

送信側のクエリ例

SET NOCOUNT ON;

-- 注文データをJSON形式で取得
DECLARE @OrderData NVARCHAR(MAX);
SET @OrderData = (
    SELECT TOP (100)
        [OrderID] AS OrderID,
        [Amount] AS Amount,
        [Status] AS Status,
        o.CustomerID AS CustomerID
    FROM
        [Orders] AS o
        INNER JOIN [OrderShippingStatuses] AS oss
            ON oss.OrderID = o.OrderID
    WHERE
        o.RegisteredAt > DATEADD(day, -1, GETDATE())
    FOR JSON PATH
);

-- ストアドプロシージャを呼び出し、JSONデータを渡す
EXEC _test_usp_ProcessJson @OrderData;

呼び出される側のストアドプロシージャ例

CREATE PROCEDURE _test_usp_ProcessJson
    @OrderJsonData NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- JSONデータをテーブル形式で処理
    SELECT
        OrderID,
        Amount,
        Status,
        sc.CustomerName
    FROM OPENJSON(@OrderJsonData)
    WITH (
        OrderID    INT          '$.OrderID',
        Amount     INT          '$.Amount',
        Status     NVARCHAR(50) '$.Status',
        CustomerID Int          '$.CustomerID'
    ) AS o
    -- 例: 別のテーブルとJOIN
    INNER JOIN SampleCustomers as sc
        ON sc.CustomerID = o.CustomerID
    ;

END
GO

2. データベース間でのデータ連携

上記のような仕組みをアプリケーション層を介して利用することで、データベース間でのデータ連携も可能です。

データベースを跨る通信方法としてリンクサーバーを使う方法もあります。
しかし、リンクサーバー経由ではオプティマイザによって自動生成されたクエリが実行されるため、場合によっては非常にパフォーマンスの悪いクエリや、LOOP JOIN時の大量のクエリ実行などが発生し、データベースに余計な負荷がかかってしまうことがあります。

参考: SQL Serverのリンクサーバーを深堀りした - Qiita

OPENXMLとの比較

従来はOPENXML関数を使って同様のことが可能でしたが、OPENJSONには以下のようなメリットがあります。

1. データベース負荷が小さい

OPENXMLの場合、sp_xml_preparedocument / sp_xml_removedocument によるドキュメントのメモリロードと解放が必要なため、一般的にOPENJSONと比較してオーバーヘッドが大きいとされています。

実際に以下の試験をしてみました。

• 試験内容: 同じデータを返すストアドプロシージャをOPENXML版とOPENJSON版で用意し、同一条件で負荷試験を実施
• 負荷量: 10並列で10,000リクエスト (合計100,000リクエスト)
• 試験環境: SQL Server 2016
• 確認方法: dm_exec_procedure_statsからTotalWorkerTime (CPU時間)や実行時間を比較

比較対象1: OPENXML

DECLARE @xml NVARCHAR(MAX) = N'
<root>
  <row>
    <OrderID>1</OrderID>
    <Amount>2000</Amount>
    <Status>shipped</Status>
  </row>
  <row>
    <OrderID>2</OrderID>
    <Amount>1500</Amount>
    <Status>pending</Status>
  </row>
</root>';

DECLARE @XmlHandle INT = NULL
EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @xml
SELECT
    x.OrderID,
    x.Amount,
    x.Status
FROM OPENXML(@XmlHandle, N'/root/row',1) WITH (
    OrderID INT,
    Amount INT,
    Status NVARCHAR(50)
) AS x;
EXEC sp_xml_removedocument @XmlHandle

比較対象2: OPENJSON

DECLARE @json nvarchar(max) = N'
[
  { "OrderID": 1, "Amount": 2000, "Status": "shipped" },
  { "OrderID": 2, "Amount": 1500, "Status": "pending" }
]';

SELECT
    o.OrderID,
    o.Amount,
    o.Status
FROM OPENJSON(@json)
WITH (
    OrderID int           '$.OrderID',
    Amount  int           '$.Amount',
    Status  nvarchar(50)  '$.Status'
) AS o;

結果は以下の通りです。
OPENJSONの方が論理読み込み量が少なく、CPU時間や実行時間が短いことがわかります。

	実行回数	total_worker_time[μs] (CPU時間)	total_elapsed_time[μs] (実行時間)	total_logical_writes[ページ] (論理書き込み)	total_logical_reads[ページ] (論理読み込み)
OpenXml	100000	53131380	56552551	0	300000
OpenJson	100000	7189096	7224437	0	0

2. 推定行数が少なくなる

JSONやXMLの中身に何行レコードが入っているかは、SQL Server がクエリをコンパイルする時点では分からないため、OPENJSONやOPENXMLなどの演算子では、固定的な行数を仮定してカーディナリティ推定が行われます。
この数値は公式ドキュメントでは明記されていませんが、ネット上の記事や実際の検証から、OPENXMLでは10,000行、OPENJSONでは50行と推定されることが分かっています。

	OPENXML	OPENJSON
実行計画の画像
推定行数	10,000	50

実際の行数が少ないにも関わらずOPENXMLを使用して1万行と推定されてしまうと、不適切な実行計画が選択されてパフォーマンスが悪化する可能性があります。
少量のレコードを扱う場合、OPENJSONを使用することで推定行数が大幅に減少し、より適切な実行計画が選択されやすくなります。

まとめ

本記事では、SQL Server の OPENJSON 関数について、基本的な使い方から実践的な活用例、OPENXML との比較まで紹介しました。

• JSON を行セットとして扱える
  OPENJSON を使うことで、JSON 形式の文字列をテーブルのような形で扱い、SELECT 文の FROM 句にそのまま利用できます。配列形式の JSON を、WITH 句でスキーマを定義しつつ安全に展開できるのがポイントです。

• ストアドプロシージャ間・データベース間のデータ連携がシンプルに
  複数テーブルにまたがるデータや複雑な構造を、1 つの NVARCHAR(MAX) パラメータ（JSON）として受け渡しできるため、テーブル型変数や一時テーブルを共有するよりも柔軟に設計できます。アプリケーション層を経由すれば、データベース間連携の手段としても有効です。

• OPENXML と比べたときの性能面の優位性
  OPENXML では XML ドキュメントの事前ロード／解放が必要になる一方、OPENJSON ではそれが不要です。実際の負荷試験でも、OPENJSON の方が CPU 時間・実行時間・論理読み込みが小さい結果となりました。また、推定行数が OPENXML よりも小さくなるため、少量のレコードを扱うシナリオでは、より現実的な実行計画が選ばれやすくなります。

SQL Server 2016 以降（互換性レベル 130 以上）を利用している環境で、JSON 形式でのデータ連携やストアドプロシージャ間のパラメータ受け渡しを検討している場合は、OPENXML ではなく OPENJSON を第一候補として検討してみてください。