JSON 列とインデックスを管理する

完了済み

リレーショナル データベースは、テーブル内のすべての行に同じ列がある場合に最適です。 構造体は 1 回定義し、すべてのレコードがそれに従います。 この設計は、フィールドが予測可能な顧客、注文、請求書などのデータに適しています。 ただし、一部のデータはレコードごとに異なります。 格納する必要がある属性は、アイテムの種類、データのソース、またはユーザーが行った選択によって異なります。 従来のテーブルデザインでは、ほとんどの行に空の列を多数作成するか、複数のテーブルにデータを分割する必要があります。 JSON 列には、予測可能な部分を通常の列に保持しながら、変数部分を JSON として格納するという別のオプションがあります。

たとえば、eコマース製品カタログには、製品名、価格、カテゴリなど、すべてのアイテムに適用される共通のフィールドがあります。 しかし、シャツにはサイズと色が必要であり、ラップトップにはプロセッサの速度と画面サイズが必要であり、書籍には著者やその他の属性が必要です。 JSON では、共通フィールドを列として格納し、カテゴリ固有の属性を JSON 列に格納します。 テーブル構造を変更せずに、新しい製品タイプを追加できます。

JSON 列を使用するタイミングを理解する

JSON 列 を使用すると、使い慣れた SQL 構文を使用して、半構造化データのクエリとインデックス作成を行うことができます。 柔軟なデータを処理するために、個別の NoSQL データベースは必要ありません。 次のシナリオでは JSON を検討してください。

  • ユーザー設定 - テーマ、言語、通知オプションなどの設定は、ユーザーごとに異なり、機能を追加すると変更されます。
  • API 応答 - 外部サービスからのデータには入れ子になった構造があり、プロバイダーが API を更新すると変更される可能性があります。
  • 監査ログ - テーブル スキーマの進化に合わせて、前後の状態をキャプチャするレコードを調整する必要があります。
  • マルチテナント アプリケーション - 顧客ごとに異なるユーザー設定フィールドが必要です。
  • 柔軟なメタデータ - レコードによって異なり、固定スキーマに適合しないタグ、ラベル、およびプロパティ。

JSON 列の作成とクエリ

SQL Server 2025 では、クエリと操作用に最適化されたバイナリ形式で JSON ドキュメントを格納するネイティブ JSON データ型が導入されています。 ネイティブ型は、より効率的な読み取り (ドキュメントは既に解析されています)、より効率的な書き込み (更新プログラムはドキュメント全体を書き換えることなく個々の値を変更できます)、および JSON を NVARCHAR(MAX)として格納する場合と比較して、より優れたストレージ圧縮を提供します。

以前のバージョンの SQL Server では、JSON を NVARCHAR(MAX) 列に格納します。

JSON から値を読み取るために、などの JSON_VALUEを使用して 1 つの値を抽出するか、オブジェクトまたは配列を返すJSON_QUERYします。 JSON プロパティのクエリを頻繁に実行する場合は、そのプロパティを抽出する計算列にインデックスを作成できます。

次の例では、JSON 列を含むテーブルを作成し、ドキュメントを挿入し、特定のプロパティに対してクエリを実行し、値を更新して、頻繁にアクセスするフィールドにインデックスを作成します。

-- Create table with native JSON type (SQL Server 2025+)
CREATE TABLE ConfigurationData (
    ConfigID INT PRIMARY KEY,
    ConfigSettings JSON NOT NULL
);

-- Insert JSON documents
INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (1, '{"theme":"dark","language":"en","notifications":true}');

INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (2, '{"theme":"light","language":"fr","notifications":false}');

-- Query JSON properties
SELECT ConfigID,
       JSON_VALUE(ConfigSettings, '$.theme') AS Theme,
       JSON_VALUE(ConfigSettings, '$.language') AS Language,
       JSON_QUERY(ConfigSettings, '$') AS FullConfig
FROM ConfigurationData;

-- Update a single property using the modify method (SQL Server 2025+ preview)
UPDATE ConfigurationData
SET ConfigSettings.modify('$.theme', 'light')
WHERE ConfigID = 1;

-- Alternative: JSON_MODIFY works with both JSON and NVARCHAR(MAX) columns
UPDATE ConfigurationData
SET ConfigSettings = JSON_MODIFY(CAST(ConfigSettings AS NVARCHAR(MAX)), '$.notifications', CAST(0 AS BIT))
WHERE ConfigID = 1;

-- Create index on frequently queried JSON property
ALTER TABLE ConfigurationData
ADD ThemeValue AS JSON_VALUE(ConfigSettings, '$.theme');

CREATE INDEX IX_Theme ON ConfigurationData(ThemeValue);

次の使用例は、ユーザー構成設定を格納する JSON 列を含むテーブルを作成します。 INSERT ステートメントは、JSON ドキュメントを文字列リテラルとして追加します。 特定の値を読み取るために、 JSON_VALUE はテーマや言語などのスカラー値を抽出し、 JSON_QUERY は JSON オブジェクト全体を返します。 .modify() メソッド (現在プレビュー段階) は、ドキュメント全体を書き換えずに 1 つのプロパティを更新します。 json型はインデックス キー列として使用できないため、この例では、テーマの値を抽出し、その計算列にインデックスを付ける計算列を作成します。

リレーショナル構造と JSON 構造を組み合わせる

JSON 列は、レコードによって異なるデータに最適です。 すべての行に一貫性のあるデータ型を持つ同じフィールドがある場合は、通常の列の方が適しています。 ネイティブ データ型の検証、JSON パス構文を使用しない単純なクエリ、列の直接インデックス作成を取得します。 柔軟性が必要なデータの部分には JSON を使用し、型指定された列に予測可能な部分を保持します。

リレーショナル構造と、変数メタデータを必要とする製品の JSON 柔軟性を組み合わせることができます。 次に例を示します。

-- Product with flexible metadata (SQL Server 2025+)
CREATE TABLE ProductMetadata (
    ProductID INT PRIMARY KEY,
    AdditionalAttributes JSON NOT NULL
        CHECK (JSON_PATH_EXISTS(AdditionalAttributes, '$.weight') = 1),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Store flexible product attributes
INSERT INTO ProductMetadata (ProductID, AdditionalAttributes) 
VALUES (1, '{"dimensions":{"length":10,"width":5,"height":8},"weight":2.5,"color":"blue"}');

-- Query nested JSON properties
SELECT ProductID,
       JSON_VALUE(AdditionalAttributes, '$.weight') AS Weight,
       JSON_VALUE(AdditionalAttributes, '$.dimensions.length') AS Length
FROM ProductMetadata;

JSON 設計の原則を検討する

JSON 列を実装する場合は、次の原則を適用します。

  • 半構造化データに JSON を使用 する - 一貫性のあるスキーマを持つデータではなく、レコードによって異なる柔軟なデータ構造を格納します。
  • 頻繁にクエリを実行するパスのインデックス 作成 - 頻繁にクエリを実行する JSON プロパティのインデックスを含む計算列を作成します。
  • 必須プロパティを検証する - CHECKJSON_PATH_EXISTS制約を使用して、必須フィールドが存在することを確認します。
  • 柔軟性と構造のバランスを取 る - 予測可能なデータを通常の列に保持し、可変部分にのみ JSON を使用します。

JSON 列は、SQL クエリ機能を維持しながら変数データのスキーマの柔軟性を提供しますが、構造化データのリレーショナル設計を置き換えるのではなく、補完する必要があります。