OPENJSON (Transact-SQL)

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Microsoft Fabric の SQL 分析エンドポイント Microsoft Fabric のウェアハウス

OPENJSON は、JSON テキストを解析し、JSON 入力からのオブジェクトとプロパティを行と列として返すテーブル値関数です。 つまり、OPENJSON は、JSON ドキュメントに対する行セット ビューを提供します。 行セット内の列と、それらの列に入力するために使用する JSON プロパティのパスを明示的に指定できます。 OPENJSON は一連の行を返すため、他のテーブル、ビュー、またはテーブル値関数で使用するのと同じように、OPENJSON を TransactSQL ステートメントの FROM 句で使用できます。

OPENJSON を使用して JSON データを SQL Server にインポートするか、JSON を直接使用できないアプリまたはサービスのために JSON データをリレーショナル形式に変換できます。

Transact-SQL 構文表記規則

構文

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

OPENJSON テーブル値関数は、最初の引数として指定された jsonExpression を解析し、式内の JSON オブジェクトからのデータを含む 1 つまたは複数の行を返します。 jsonExpression には、入れ子になったサブオブジェクトを含めることができます。 jsonExpression 内からサブオブジェクトを解析する場合は、JSON サブオブジェクトの path パラメーターを指定できます。

openjson

既定では、OPENJSON テーブル値関数は、jsonExpression で見つかった各 {key:value} ペアのキー名、値、および型を含む 3 つの列を返します。 別の方法として、with_clause を指定することで、OPENJSON が返す結果セットのスキーマを明示的に指定できます。

with_clause

with_clause には、OPENJSON が返す列とそれらの型の一覧が含まれます。 既定では、OPENJSON は、with_clause に指定された列名を持つ jsonExpression 内のキーと照合されます ( (この場合のキーの一致は、大文字と小文字の区別があるという意味を含みます)。 列名がキー名と一致しない場合は、省略可能な column_path を指定できます。これは jsonExpression 内のキーを参照する JSON パス式 です。

引数

jsonExpression

JSON テキストを含む Unicode 文字式です。

OPENJSON では、配列の要素または JSON 式内のオブジェクトのプロパティを反復処理し、各要素またはプロパティの 1 つの行を返します。 次の例では、jsonExpression として指定されたオブジェクトの各プロパティを返します。

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

結果:

• DoublePrecisionFloatingPoint_value は、IEEE 754 に準拠します。

path

jsonExpression 内のオブジェクトまたは配列を参照する省略可能な JSON パス式です。 OPENJSON は、指定された位置で JSON テキストをシークし、参照先のフラグメントのみを解析します。 詳細については、「JSON パス式 (SQL Server)」を参照してください。

SQL Server 2017 (14.x) と Azure SQL データベース では、path の値として変数を指定できます。

次の例では、path を指定することで、入れ子になったオブジェクトを返します。

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

結果

OPENJSON が JSON 配列を解析するとき、この関数は、JSON テキスト内の要素のインデックスをキーとして返します。

JSON の式のプロパティを持つパスの手順を照合に使用する比較では、大文字と小文字および照合順序に関係なく (つまり、BIN2 の比較)。

配列要素の ID

Azure Synapse Analytics のサーバーレス SQL プールの OPENJSON 関数を使用すると、結果として返される各行の ID を自動的に生成できます。 この ID 列は、列定義の後の JSON パスの式 $.sql:identity() を使用して指定されます。 JSON パス式のこの値を持つ列は、関数が解析する JSON 配列内の各要素に対して一意の 0 から始まる数値を生成します。 ID 値は、配列要素の位置またはインデックスを表します。

DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
               {"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
              ]';

SELECT * FROM OPENJSON(@array)
        WITH (  month VARCHAR(3),
                temp int,
                month_id tinyint '$.sql:identity()') as months

結果

ID は、Synapse Analytics のサーバーレス SQL プールでのみ使用できます。

with_clause

OPENJSON 関数が返す出力スキーマを明示的に定義します。 省略可能な with_clause には、次の要素を含めることができます。

colName。出力列の名前。

OPENJSON は既定では、列の名前を使用して、JSON テキストのプロパティと一致します。 たとえば、nameの列をスキーマを指定する場合、OPENJSON は JSON テキストには、"name"プロパティを使用して、この列を作成しようとします。 column_path 引数を使用することで、この既定のマッピングをオーバーライドすることができます。

type
出力列のデータ型。

column_path
指定した列で返されるプロパティを指定する JSON のパス。 詳細については、の説明を参照してください、path このトピックの前のパラメーター。

出力列の名前がプロパティの名前の一致しない場合に既定のマッピング ルールをオーバーライドするには、column_path を使用します。

JSON の式のプロパティを持つパスの手順を照合に使用する比較では、大文字と小文字および照合順序に関係なく (つまり、BIN2 の比較)。

パスの詳細については、「JSON パス式 (SQL Server)」を参照してください。

AS JSON
参照先のプロパティに内部 JSON オブジェクトまたは配列が含まれていることを指定するには、列定義の中で AS JSON を使用します。 AS JSON を指定する場合、列の型は NVARCHAR(MAX) にする必要があります。

• として AS JSON を指定しないと、列の場合、関数は、指定されたパスに指定された JSON プロパティからスカラー値 (たとえば、int、文字列、true、false) を返します。 パスがオブジェクトまたは配列を表しているときに、指定されたパスにプロパティが見つからない場合、関数は、lax モードでは null を、strict モードではエラーを返します。 この動作は、JSON_VALUE 関数の動作に似ています。

• AS JSON の列を指定する場合、関数は、指定したパス指定された JSON プロパティからは JSON フラグメントを返します。 パスがスカラー値を表しているときに、指定されたパスにプロパティが見つからない場合、関数は、lax モードでは null を、strict モードではエラーを返します。 この動作は、JSON_QUERY 関数の動作に似ています。

たとえば、次のクエリを返し、配列の要素の書式を設定します。

DECLARE @json NVARCHAR(MAX) = N'[  
  {  
    "Order": {  
      "Number":"SO43659",  
      "Date":"2011-05-31T00:00:00"  
    },  
    "AccountNumber":"AW29825",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":1  
    }  
  },  
  {  
    "Order": {  
      "Number":"SO43661",  
      "Date":"2011-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',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

結果

戻り値

OPENJSON 関数によって返される列は、WITH オプションによって異なります。

1. OPENJSON を既定のスキーマで呼び出した場合 (つまり、WITH 句に明示的にスキーマを指定しない場合)、関数は、次の列を持つテーブルを返します。

   1. [キー] 指定したプロパティの名前か、指定した配列内の要素のインデックスを含む nvarchar (4000) 値。 キー列は BIN2 照合順序を持っています。

   2. 値。 プロパティの値を含む、nvarchar (max) 値です。 [値] 列では、その照合順序を継承 jsonExpression から。

   3. [種類] 。 値の型を含む int 値。 型の列には、既定のスキーマを OPENJSON を使用する場合にのみが返されます。 型の列では、次の values. のことがあります。

      型の列の値	JSON データ型
      0	null
      1	string
      2	number
      3	true/false
      4	array
      5	object

   最初のレベル プロパティのみが返されます。 JSON のテキストが正しくフォーマットされていない場合、ステートメントが失敗します。

2. -OPENJSON を呼び出すし、WITH 句で、明示的なスキーマを指定する、するときに、WITH 句で定義したスキーマを持つテーブルを返します。

解説

OPENJSON の 2 番目の引数または with_clause で使用される json_path は、lax または strict キーワードで始めることができます。

• lax モードでは、指定のパスにオブジェクトまたは値が見つからない場合でも、OPENJSON はエラーを生成しません。 パスが見つからない場合、OPENJSON は、空の結果セットまたは NULL 値を返します。
• Strict モード では、パスが見つからない場合、OPENJSON はエラーを返します。

このページに示すいくつかの例では、パス モード (lax または strict) を明示的に指定しています。 パス モードの指定は必須ではありません。 パス モードを明示的に指定しない場合は、lax が既定のモードになります。 パス モードとパス式の詳細については、「JSON パス式 (SQL Server)」を参照してください。

with_clause の列名は、JSON テキスト内のキーと照合されます。 列名 [Address.Country] を指定した場合は、Address.Country キーと照合されます。 オブジェクト Address 内の入れ子になったキー Country を参照する場合は、パス $.Address.Country を列のパスに指定する必要があります。

json_path には、英数字を使用したキーを含めることがあります。 キーに特殊文字がある場合は、二重引用符を使用して、json_path 内でキー名をエスケープします。 たとえば、$."my key $1".regularKey."key with . dot" は、次の JSON テキストの値 1 と一致します。

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

例

例 2 - JSON 配列を一時テーブルに変換します。

次の例では、識別子の一覧を数値の JSON 配列として指定します。 このクエリは、JSON 配列を識別子のテーブルに変換し、すべての製品を指定の ID でフィルター処理します。

DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID = productTypes.value

このクエリは、次の例と同等です。 ただし、次の例では、数値をパラメーターとして渡す代わりに、クエリに埋め込む必要があります。

SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)

例 3 - 2 つの JSON オブジェクトからのマージ プロパティ

次の例では、2 つの JSON オブジェクトのすべてのプロパティの和集合を選択します。 2 つのオブジェクトでは、重複するnameプロパティがあります。 例では、キーの値を使って、結果から重複する行を除外しています。

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

例 3 - CROSS APPLY を使用してテーブルのセルに格納されている JSON データを行と結合する

次の例では、SalesOrderHeader テーブルには、SalesOrderReasons の配列を JSON 形式で含んでいるSalesReason テキスト列があります。 SalesOrderReasons オブジェクトには、Quality や Manufacturer などのプロパティが含まれます。 例では、すべての販売注文の行を、関連する販売理由と結合するレポートを作成しています。 OPENJSON 演算子では、理由が別の子テーブルに格納されていたかのように、販売理由の JSON 配列を展開します。 次に、CROSS APPLY 演算子では、OPENJSON テーブル値関数によって返される行に各販売注文の行を結合します。

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

返される行の明示的に定義されたスキーマを指定した OPENJSON を使用することで、同じクエリを書き直すことができます。

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')

この例では、$ パスは、配列内の各要素を参照します。 返される値を明示的にキャストする場合は、この種類のクエリを使用できます。

例 4 - CROSS APPLY とリレーショナル行と JSON の要素を結合する

次のクエリは、次の表に示すように、リレーショナル行と JSON 要素を結果内で結合します。

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street VARCHAR(500) ,  postcode VARCHAR(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

結果

例 5 - インポートには、SQL Server の JSON データ

次の例では、全体の JSON オブジェクトを SQL Server テーブルです。

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  
   
  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id INT,  
        firstName NVARCHAR(50), lastName NVARCHAR(50),   
        isAlive BIT, age INT,  
        dateOfBirth DATETIME, spouse NVARCHAR(50))

例 6 - JSON コンテンツを使用したシンプルな例

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

関連項目

• JSON パス式 (SQL Server)
• OPENJSON を使用して JSON データを行と列に変換する (SQL Server)
• 既定のスキーマを使用する OPENJSON の使用 (SQL Server)
• 明示的なスキーマで OPENJSON を使用する (SQL Server)