OPENJSON で JSON データを解析し、変換する

  • [アーティクル]

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

OPENJSON 行セット関数は、JSON TEXT を ROWS と COLUMN のセットに変換します。 OPENJSON を使用して JSON コレクションを行セットに変換したら、返されたデータで SQL クエリを実行したり、行セットを SQL Server テーブルに挿入したりできます。 SQL Server データベース エンジンでの JSON データの操作の詳細については、「SQL Server の JSON データ」を参照してください。

OPENJSON 関数は、1 つまたは集合の JSON オブジェクトを受け取り、1 つまたは複数の ROWS に変換します。 既定では、OPENJSON 関数が次のデータを返します。

  • JSON オブジェクトからは、最初のレベルで検出されたすべてのキーと値のペアを返します。
  • JSON 配列からは、配列のすべての要素とそれらのインデックスを返します。

オプションの WITH 句を追加して、出力の構造を明示的に定義するスキーマを指定できます。

デフォルト出力を指定した OPENJSON

結果の明示的なスキーマ (つまり WITH の後の OPENJSON 句) を指定せずに OPENJSON 関数を使用すると、次の 3 つの COLUMN を含むテーブルを返します。

  1. 入力オブジェクト (または入力配列の要素のインデックス) のプロパティの name。
  2. プロパティまたは配列要素の value。
  3. type (たとえば、文字列、数値、ブール値、ARRAY、オブジェクト)。

OPENJSON は JSON オブジェクトの各プロパティ、または ARRAY の各要素を個別の ROW として返します。

これは、デフォルトスキーマ (つまり、オプションの WITH 句を指定しない) で OPENJSON を使用した簡単な例です。この例では JSON オブジェクトのプロパティごとに 1 つの ROW が返されています。

DECLARE @json NVARCHAR(MAX);

SET @json='{ "name": "John", "surname": "Doe", "age": 45, "skills": [ "SQL", "C#", "MVC" ]}';

SELECT *
FROM OPENJSON(@json);

結果セットは次のようになります。

key 値 type
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

詳細と例については、「デフォルトスキーマで OPENJSON を使用する」を参照してください。

構文と使用法については、「OPENJSON」をご覧ください。

明示的な構造を指定した OPENJSON 出力

OPENJSON 関数の WITH 句を使用して結果のスキーマを指定すると、WITH 句に定義した COLUMN のみを持つテーブルを返します。 オプションの WITH 句では、いくつかの出力列、各 COLUMN の型、各出力 VALUE の JSON ソース プロパティのパスを指定します。 OPENJSON は JSON オブジェクトの ARRAY を繰り返し処理し、COLUMN ごとに指定されたパスで VALUE を読み取り、VALUE を指定の TYPE に変換します。

次の例では、OPENJSON 句で明示的に指定した出力のスキーマで WITH を使用します。

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2024-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    },
    {
        "Order": {
            "Number": "SO43661",
            "Date": "2024-06-01T00:00:00"
        },
        "AccountNumber": "AW73565",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 3
        }
    }
]';

SELECT *
FROM OPENJSON(@json) WITH (
    Number VARCHAR(200) '$.Order.Number',
    DATE DATETIME '$.Order.Date',
    Customer VARCHAR(200) '$.AccountNumber',
    Quantity INT '$.Item.Quantity'
);

結果セットは次のようになります。

番号 Date Customer Quantity
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-06-01T00:00:00 AW73565 3

この関数は JSON 配列の要素を返し、書式設定します。

  • JSON ARRAY の要素ごとに、OPENJSON によって出力テーブルに新しい ROW が生成されます。 JSON 配列の 2 つの要素が、返されるテーブルで、2 つの行に変換されます。

  • colName type json_path 構文を使用して指定された COLUMN ごとに、OPENJSON は指定されたパスの配列要素で検出された VALUE を指定の TYPE に変換します。 この例では、Date 列の値がパス $.Order.Date の各要素から取得され、datetime 値に変換されます。

詳細と例については、「明示的なスキーマで OPENJSON を使用する (SQL Server)」を参照してください。

構文と使用法については、「OPENJSON」をご覧ください。

OPENJSON には、互換性レベル 130 が必要です。

OPENJSON 関数は、 互換性レベル 130 以上でのみ使用できます。 データベース互換レベルが 130 よりも低い場合、SQL Server は OPENJSON 関数を見つけて実行することができません。 他の組込み JSON 関数は、すべての互換性レベルで使用できます。

次のコマンドを使用して、sys.databases ビューまたはデータベースのプロパティで互換性レベルをチェックし、データベースの互換性レベルを変更できます。

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

関連するコンテンツ