JSON_TABLE
JSON_TABLE は、JSON データのリレーショナルビューを作成する SQL 拡張機能です。
ビューで JSON_TABLE を使用する方法の例については、『TDV ユーザー ガイド』の「ビュー」トピックを参照してください。JSON_TABLE の例のプログレッシブセットについては、次のセクションを参照してください。
| • | 例 1：リテラル JSON テーブル |
| • | Example 2: Another Literal JSON Table, with Ignored Objects |
| • | 例 3：オブジェクトのプロパティとその値の取得 |
| • | 例 4：外部テーブルによって提供される JSON コンテンツ |
| • | 例 5：サブクエリ |
| • | 例 6：キーと値の取得を伴う条件ロジック |
| • | 例 7：無効なキーと値 |
| • | 例 8：ネストされたアレイ |
構文
JSON_TABLE には、さまざまな引数と構文があります。JSON パスの注釈、定義、および図の後、例は、JSON_TABLE を代表的なユースケースに適用する方法を示しています。
備考
JSON_TABLE エレメントは、タブ、改行、および余分なスペース文字でフォーマットして、読みやすくすることができます。
JSON_TABLE を使用すると、次のことができます。
| • | スキーマや特定の使用パターンに関係なく、JSON データを定義して作成します。 |
| • | JSON 式の評価の結果を、新しい仮想テーブル (「インラインリレーショナルビュー」) のリレーショナル行と列に分解します。 |
定義
これらの定義は、例を使用すると最も簡単に理解できます。このドキュメントの例、および『TDV ユーザー ガイド』の Views トピックの例では、JSON_TABLE を構造化、表示、および使用する方法を示しています。
| • | JSON - JavaScript オブジェクト表記。この表記法ではコメントは許可されていません。 |
| • | JSON_TABLE - キーワード JSON_TABLE の後に、括弧で囲まれた 3 つの順序付きエレメントが続きます。最初の 2 つは、暗黙的に (カンマ区切り) または明示的に (CROSS JOIN キーワード区切り) クロス結合されます。 |
次のような JSON コンテンツプロバイダー：
リテラル - インライン仮想テーブルを定義する、一重引用符 ( ' ' ) で囲まれた構造。
識別された Web データ ソース内のカラム参照 (T1、C1 など)。
行プロバイダーを指定する一重用符 (' ') で囲まれたパス式 (次の主な箇条書きを参照)。
COLUMNS 句 - COLUMNS という単語の後に、括弧内にカンマで区切られた 1 つ以上のカラム定義が続きます。各カラム定義には、カラム エイリアス、その SQL データ タイプ、キーワード PATH、および (1) そのカラムを占有するコンテキスト項目とオブジェクトを指定するパス式 (例 1：リテラル JSON テーブル)、または (2) 値を取得する構文エレメントを指定するキーワード (例 3：オブジェクトのプロパティとその値の取得)。
| — | テーブルの省略可能なエイリアス (JT など)。 |
| — | ソース テーブルが (インライン仮想テーブルではなく) 外部テーブルである場合は、カンマの後にテーブルの名前 (およびオプションでその名前のエイリアス) が続きます。 |
| — | JSON コンテンツがカラム参照によって提供される場合、カラムを所有するテーブルを JSON_TABLE とクロス結合する必要があります。 テーブルは、明示的に (“T1 CROSS JOIN T2”) または暗黙的に (“T1, T2”) クロス結合できます。 |
| • | パス式 - 操作対象の JSON オブジェクトを識別する式。 |
コンテキスト アイテム (JSON ルート) - ドル記号 ($)。
オプションのパス ステップ (オブジェクト ステップまたはアレイ ステップ)。
注：カラムパスの場合、1 つのパスステップの深さのみが許可されます (「$。title」と同様のパターンで)
| — | オブジェクト ステップ - ドット (ピリオド) の後にオブジェクト プロパティの名前が続きます。名前にドットが含まれている場合は、二重引用符で囲む必要があります。 |
| — | アレイ ステップ - ドット (ピリオド) の後にオブジェクト プロパティの名前が続き、その後に角括弧 ( [ ] ) が続きます。名前に内部ドットが含まれる場合は、二重引用符で囲む必要があります。 アレイ ステップ内の文字はアレイ スライサーと呼ばれます： 数字、またはカンマで区切られた複数の数字は、オブジェクトの位置 (1 から開始) を示します。 キーワード「to」は範囲を示します。 開始番号を省略すると、範囲はアレイの最初のエレメントから始まります。 TO の後の番号を省略すると、アレイの最後のエレメントで範囲を終了します。 アレイ ステップの例： .[ to 3, 6, 8 to ] - エレメント 1, 2, 3, 6, 8, 9、10 (10 個のエレメントのアレイ) |
| • | プロパティ名 - パス式では、プロパティ名は英字で始まる必要があります。英数字と一部の特殊文字 (二重引用符で囲む必要があります) を含めることができます。 |
JSON パス
パス式とその意味の例を次に示します。
|
パス式 |
説明 |
|
$ |
特定の JSON オブジェクトを指定するコンテキスト アイテム (ルート)。 |
|
$.dept |
ルート、およびパスステップ。オブジェクトのプロパティ 'dept'の値 |
|
$.dept.coffee[1] |
ルート、パスステップ、およびリーフステップ。JSON オブジェクトのルートのプロパティ 'coffee' の値であるアレイの最初のエレメントであるオブジェクト。プロパティ 'coffee' の値はアレイです。 |
|
$.dept.coffee[12、3、8 から 10] |
アレイ 'coffee' (JSON オブジェクトのルートのプロパティ) の 12 番目、3 番目、8 番目、9 番目、および 10 番目のエレメント。エレメントはアレイ順に返されます：3番目、8番目、9番目、10番目、12番目。 |
|
$.dept[ ].coffee[ ] |
両方のステップはアレイ ステップにすることができます。 |
|
$。"rest.ID_output"。"rest.row" |
このパス式は、外部テーブル内の行を指定します。パスエレメント内のドット文字をエスケープするために二重引用符が使用されていることに注意してください。 |
例 1：リテラル JSON テーブル
この例では、インライン テーブルを設定し、そこからタイトル、作成者、価格を (この順序で) 選択します。
実行結果はクエリに従います。
クエリ
この例では、FROM 句がインライン仮想テーブルを提供します。JSON_TABLE リテラルは、開き括弧の直後で始まり、パス式の直前で終わります (その後にカンマが続きます)。パス式は、アレイオブジェクト (仮想テーブル) と先頭から 2 までの範囲を指定します。COLUMNS 句は、SELECT で要求されたカラムに対応するカラムを定義します。JT のエイリアスは、閉じ括弧の後にテーブルに適用されます。
SELECT
myTitle, author, price
FROM
JSON_TABLE ( '{ "store": {"book": [
{"category" : "reference",
"author" : "Nigel Rees",
"title" : "Sayings of the Century",
"price" : 8.95
},
{ "title":"The Rumi Collection"},
{"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 15.00
},
{"category": "history",
"author": "Steve Harris",
"title": "Rennaisance",
"price": 17.00
}
]
}}',
'$.store.book[ to 2]' COLUMNS (myTitle VARCHAR(100) PATH '$.title',
price DOUBLE PATH '$.price',
author VARCHAR (100) PATH '$.author' ) ) JT
ORDER BY price desc
結果
このクエリを実行した結果は次のとおりです。
myTitleauthorprice
Savings of the Century Nigel Rees 8.95
The Rumi Collection [NULL] [NULL]
例2：無視されたオブジェクトを含む、もう1つのリテラル JSON テーブル
この例では、2 つのストアオブジェクトの間にニューススタンドオブジェクトがありますが、クエリはそれとその内容を無視します。ブックレコードごとに、クエリは 3 つの属性の値を要求します。
クエリ
SELECT
myTitle, author, price
FROM
JSON_TABLE ( '{ "store": {"book": [
{"category": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
},
{"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 15.00
},
{"category": "history",
"author": "Steve Harris",
"title": "Rennaisance",
"price": 17.00
}
]
},
"newsstand" : {"magazine": [
{"brand" : "Newsweek",
"price" : 10.00
}
]
},
"store": {"book": [
{"category": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_2",
"price": 8.95
},
{"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour_2",
"price": 15.00
},
{"category": "history",
"author": "Steve Harris",
"title": "Rennaisance_2",
"price": 17.00
}
]
}
}',
'$.store[2].book' COLUMNS (myTitle VARCHAR(100) PATH '$.title',
price DOUBLE PATH '$.price',
author VARCHAR (100) PATH '$.author' ) ) JT
-- ORDER BY price asc
結果
パス式はアレイの 2 番目のオブジェクトを指していますが、そのオブジェクトの名前 test (store) が一致しないため、結果は返されません。
例 3：オブジェクトのプロパティとその値の取得
このクエリは、ブック内のすべてのキーと値を取得します。この場合、COLUMNS 句は、PATH の後に、一重引用符で囲まれたパス式ではなく、キーワードを使用します。
クエリ
SELECT
property, propValue
FROM
JSON_TABLE ( '{ "store": {"book": [
{"category": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
},
{"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 15.00
},
{"category": "history",
"author": "Steve Harris",
"title": "Rennaisance",
"price": 17.00
}
]
}}',
'$.store.book' COLUMNS (property VARCHAR(100) PATH key,propValue VARCHAR (200) PATH value) ) JT ORDER BY property
結果
結果には、キーを表す列見出しの下に値が表示されるのではなく、キーとその値が行エントリーとして表示されます。つまり、JSON_TABLE を使用して、値だけでなくテーブルから構造情報を取得できます。
propertypropValue
author Nigel Rees
author Evelyn Waugh
author Steve Harris
category reference
category fiction
category history
price 8.95
price 15.00
price 17.00
title Savings of the Century
title Sword of Honor
title Renaissance
例 4：外部テーブルによって提供される JSON コンテンツ
この例では、JSON_TABLE を使用して、REST データ ソースから取得した外部テーブルにリレーショナル構造 (カラム) を定義します。
クエリ
SELECT
customerId, customerName
FROM
JSON_TABLE (C."output", '$."rest.customersResponse"."rest.customersOutput"."rest.row"'COLUMNS (customerId INTEGER PATH '$."rest.customerid"',
customerName VARCHAR (100) PATH '$."rest.companyname"') ) JT ,
/shared/customers_wrapper C
結果
結果は、REST データ ソースからの出力 JSON テーブルから選択されます。
customerId customerName1 Able Computing
2 Anston Systems
3 Blackard Electronics
. . .
例 5：サブクエリ
この例では、JSON_TABLE はサブクエリに埋め込まれ、REST データ ソースを使用します。
クエリ
SELECT
1 C
FROM
/services/databases/system/DUAL
WHERE EXISTS
(
SELECT
customerId, price
FROM
/shared/examples/customers_wrapper C,
JSON_TABLE (C."output", '$."rest.customersOutput"."rest.row"' COLUMNS (customerId INTEGER PATH '$."rest.customerid"', price VARCHAR(100) PATH '$."rest.companyname"')) JTWHERE
customerId = 30
)
例 6：キーと値の取得を伴う条件ロジック
この例は、条件付きロジックを使用して、ソースデータの構造に基づいてさまざまなプロパティの値を取得する方法を示しています。これにより、異種データ ソースを処理する際の柔軟性が向上します。
クエリ
SELECT
firstName,
lastName,
CASE WHEN firstName IS NULL THEN fullName
ELSE firstName || ' ' || lastName END fullName,
price
FROM
JSON_TABLE ( '{ "store": {"book": [
{"category": "reference",
"author" : {"firstName": "Nigel" , "lastName" : "Rees"},"title": "Sayings of the Century",
"price": 8.95
},
{"category": "fiction",
"author": {"FN":"Evelyn Waugh"},"title": "Sword of Honour",
"price": 15.00
},
{"category": "history",
"author": "Steve Harris",
"title": "Rennaisance",
"price": 17.00
}
]
}}',
'$.store.book[1 to 2]' COLUMNS (author VARCHAR(100) PATH '$.author', price VARCHAR(100) PATH '$.price')) JT,JSON_TABLE (JT.author, '$' columns (firstName VARCHAR(20) PATH '$.firstName', lastName VARCHAR(20) PATH '$.lastName', fullName VARCHAR(20) PATH '$.FN' )) JT2
結果
結果は、2 つの異なる方法で編成されたデータと、両方に共通する価格を組み合わせたものです。
firstNamelastNamefullNameprice
Nigel Rees Nigel Rees 8.95
[NULL] [NULL] Evelyn Waugh 15.00
例 7：無効なキーと値
クエリ
SELECT
firstName,
lastName,
CASE WHEN firstName IS NULL THEN author
ELSE firstName || ' ' || lastName END fullName,
price
FROM
JSON_TABLE ( '{ "store": {"book": [
{"category": "reference",
"author" : {"firstName": "Nigel" , "lastName" : "Rees"},"title": "Sayings of the Century",
"price": 8.95
},
{"category": "fiction",
"author": {"FN":"Evelyn Waugh"},"title": "Sword of Honour",
"price": 15.00
},
{"category": "history",
"author": "Steve Harris",
"title": "Rennaisance",
"price": 17.00
}
]
}}',
'$.store.book[*]' COLUMNS (author VARCHAR(100) PATH '$.author', price VARCHAR(100) PATH '$.price')) JT,JSON_TABLE (JT.author,
'$' columns (firstName VARCHAR(20) PATH '$.firstName', lastName VARCHAR(20) PATH '$.lastName')) JT2
結果
アレイ指定 ($.store[*]) にサポートされていないワイルドカード文字が含まれているため、エラー メッセージが返されます。
com.compositesw.cdms.webapi.WebapiException: Problems encountered while resolving JSON_TABLE references: Exception 1 :
com.compositesw.cdms.services.parser.ParserException: Invalid JSON path. Cause: Compile json
path $.store.book[*] failed.. On line 32, column 6.
[parser-2931070] . . .
例 8：ネストされたアレイ
この例では、store は book というアレイを含むアレイです。パス式 $.store[1].book[2] は、これらのネストされたアレイからプロパティ値を取得します。
クエリ
SELECT
-- {option "DISABLE_PLAN_CACHE" }myTitle, author, price
FROM
JSON_TABLE ( '{ "store": [{"book":
[{"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA1-B1",
"price": 13.95
} ,
{"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA1-B1",
"price": 12.95
}
] , "book": [ {"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA2-B1",
"price": 11.95
} ,
{"category_21": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA2-B2",
"price": 10.95
}
]} ,
{ "book": [ {"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA3-B1",
"price": 9.95
} ,
{"category_21": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA3-B2",
"price": 8.95
}
]}
]}', '$.store[1].book[2]' COLUMNS (myTitle VARCHAR(100) PATH '$.title',
price DOUBLE PATH '$.price',
author VARCHAR(100) PATH '$.author' ) ) JT
ORDER BY price asc
--OFFSET 1 FETCH 2 ROWS ONLY
結果
結果 (クエリの最終行はコメントアウトされたまま) は、PATH 式に基づいてフェッチされ、価格で並べ替えられます。
myTitleauthorprice
Sayings of the Century_S1-BA2-B2 Nigel Rees 10.95
Sayings of the Century_S1-BA1-B 1 Nigel Rees 12.95
OFFSET 1 FETCH 2 ROWS ONLY のコメントを外すと、オフセットは最初の適格なアイテムをスキップし (価格でソートした後)、2 つの行がフェッチされても、1 つだけが返されます。
myTitleauthorprice
Sayings of the Century_S1-BA1-B2 1 Nigel Rees 12.95