OPENJSON (Transact-SQL)

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

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

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

Note

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

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

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Transact-SQL 構文表記規則

Syntax

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

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

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

openjson

OPENJSON TVF の構文の図。

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

with_clause

OPENJSON TVF の WITH 句の構文の図。

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

Arguments

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);

Results:

キー	value	型
String_value	John	1
DoublePrecisionFloatingPoint_value	45	2
DoublePrecisionFloatingPoint_value	2.3456	2
BooleanTrue_value	true	3
BooleanFalse_value	false	3
Null_value	NULL	0
Array_value	["a","r","r","a","y"]	4
Object_value	{"obj":"ect"}	5

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

path

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

パスの値として変数を指定できます。 (これは、SQL Server 2016 (13.x) 以前のバージョンではサポートされていません)。

次の例では、パスを指定して入れ子になったオブジェクトを返します。

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"')

Results

Key	Value
0	en-GB
1	en-UK
2	de-AT
3	es-AR
4	sr-Cyrl

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

Results

month	temp	month_id
Jan	10	0
Feb	12	1
Mar	15	2
Apr	17	3
May	23	4
Jun	27	5

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

with_clause

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

colName(共同名)

出力列の名前。

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

type

出力列のデータ型。

Note

AS JSON オプションも使用する場合、列のデータ型は nvarchar(MAX) である必要があります。

column_path

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

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

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

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

AS JSON

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

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

Note

JSON プロパティから入れ子になった JSON フラグメントを返す場合は、 AS JSON フラグを指定する必要があります。このオプションを指定しないと、プロパティが見つからない場合、 OPENJSON は参照先の JSON オブジェクトまたは配列の代わりに NULL 値を返すか、 strict モードで実行時エラーを返します。

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

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  
 )

Results

Number	Date	Customer	Quantity	Order
SO43659	2011-05-31T00:00:00	AW29825	1	{"Number":"SO43659","Date":"2011-05-31T00:00:00"}
SO43661	2011-06-01T00:00:00	AW73565	3	{"Number":"SO43661","Date":"2011-06-01T00:00:00"}

戻り値

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

既定のスキーマで OPENJSON を呼び出すと(つまり、 WITH 句で明示的なスキーマを指定しない場合)、関数は次の列を持つテーブルを返します。
- Key。指定したプロパティの名前または指定した配列内の要素のインデックスを含む nvarchar(4000) 値。 key列には BIN2 照合順序があります。
- Value。プロパティの値を含む nvarchar(MAX) 値。 value列は、jsonExpression から照合順序を継承します。
- Type。値の型を含む int 値。 Type列は、既定のスキーマでOPENJSONを使用する場合にのみ返されます。 type列には、次のいずれかの値があります。
  
  型の列の値 JSON データ型
  
  0 null 値
  
  1 文字列
  
  2 number
  
  3 true/false
  
  4 アレイ
  
  5 オブジェクト
最初のレベルプロパティのみが返されます。 JSON のテキストが正しくフォーマットされていない場合、ステートメントが失敗します。
OPENJSONを呼び出し、WITH句で明示的なスキーマを指定すると、WITH句で定義したスキーマを持つテーブルが返されます。

型の列の値	JSON データ型
0	null 値
1	文字列
2	number
3	true/false
4	アレイ
5	オブジェクト

Note

Key、Value、およびTypeの列は、既定のスキーマでOPENJSONを使用する場合にのみ返され、明示的なスキーマでは使用できません。

Remarks

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

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

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

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

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

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

Examples

例 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 つのオブジェクトの名前プロパティが重複しています。例では、キーの値を使って、結果から重複する行を除外しています。

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

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

Tip

個々のフィールドに格納されている JSON 配列を展開し、親行と結合する必要がある場合は、通常、Transact-SQL CROSS APPLY 演算子を使用します。 CROSS APPLYの詳細については、FROM 句を参照してください。

返される行の明示的に定義されたスキーマを指定した 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

Results

title	street	postcode	lon	lat
食品市場全体	17991 レドモンド・ウェイ	ワシントン州 98052	47.666124	-122.10155
Sears	148番街 NE	ワシントン州 98052	47.63024	-122.141246,17

例 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

フィードバック

このページはお役に立ちましたか?

Last updated on 2025-11-18

次の方法で共有

OPENJSON (Transact-SQL)

Syntax

openjson

with_clause

Arguments

jsonExpression

path

配列要素の ID

with_clause

colName(共同名)

type

column_path

AS JSON

戻り値

Remarks

Examples

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

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

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

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

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

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

関連コンテンツ

フィードバック

その他のリソース

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