JSON_TABLE
JSON_TABLEは、JSONデータのリレーショナルビューを作成するSQL拡張機能です。
ビューでJSON_TABLEを使用する方法の例については、TDVユーザーガイドの「ビュー」トピックを参照してください。 JSON_TABLEの例のプログレッシブセットについては、次のセクションを参照してください。
構文
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で区切られている）のいずれかで相互結合されます。
a。 JSONコンテンツプロバイダー。次のいずれかになります。
リテラル-インライン仮想テーブルを定義する、一重引用符（ ''）で囲まれた構造。
識別されたWebデータソース（たとえば、T1、C1）の列参照。
b。 一重引用符（ ''）で囲まれた、行プロバイダーを指定するパス式（以下の次の主要な箇条書きを参照）。
c。 COLUMNS句-COLUMNSという単語の後に、括弧内に1つ以上のコンマ区切りの列定義が続きます。各列定義には、列エイリアス、そのSQLデータタイプ、キーワードPATH、および(1)その列を占めるコンテキストアイテムとオブジェクトを指定するパス式のいずれかが含まれます（
例1：リテラルJSON表）、または(2)値を取得する構文エレメントを指定するキーワード（
例3：オブジェクトのプロパティとその値の取得）。
— テーブルのオプションのエイリアス（JTなど）。
— ソーステーブルが（インライン仮想テーブルではなく）外部の場合、コンマの後にテーブルの名前（およびその名前のオプションのエイリアス）が続く。
— JSONコンテンツが列参照を介して提供される場合、列を所有するテーブルはJSON_TABLEと相互結合する必要があります。
テーブルは、明示的に（ "T1 CROSS JOIN T2"）または暗黙的に（ "T1、T2"）相互結合できます。
•パス式-操作する1つまたは複数のJSONオブジェクトを識別する式。
d。 コンテキストアイテム（JSONルート）-ドル記号（$）。
e。 オプションのパスステップ（オブジェクトステップまたは配列ステップ）。
注：列パスの場合、1つのパスステップの深さのみが許可されます（「$。title」と同様のパターンで）
— オブジェクトステップ—ドット（ピリオド）の後にオブジェクトプロパティの名前が続きます。名前に内部ドットが含まれている場合は、二重引用符で囲む必要があります。
— 配列ステップ—ドット（ピリオド）、オブジェクトプロパティの名前、角かっこ（[]）が続きます。名前に内部ドットが含まれている場合は、二重引用符で囲む必要があります。
配列ステップ内の文字は、配列スライサーと呼ばれます。
数値、またはコンマで区切られた複数の数値は、オブジェクトの位置（1から数えて）を示します。
キーワード「to」は範囲を示します。
開始番号を省略すると、配列の最初のエレメントから範囲が始まります。
TOの後の数字を省略すると、配列の最後のエレメントで範囲が終了します。
配列ステップの例：
。[から3、6、8から] —エレメント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テーブル
この例では、インラインテーブルを設定し、そこからタイトル、作成者、価格を（この順序で）選択します。
実行結果はクエリに従います。
[Query(クエリー)]
この例では、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
結果
このクエリを実行した結果は次のとおりです。
myTitle author price
Savings of the Century Nigel Rees 8.95
The Rumi Collection [NULL] [NULL]
例2：無視されたオブジェクトを含む別のリテラル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を使用して、値だけでなくテーブルから構造情報を取得できます。
property propValue
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 customerName
1 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"')) JT
WHERE
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つの異なる方法で編成されたデータと、両方に共通する価格を組み合わせたものです。
firstName lastName fullName price
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式に基づいてフェッチされ、価格で並べ替えられます。
myTitle author price
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つだけが返されます。
myTitle author price
Sayings of the Century_S1-BA1-B2 1 Nigel Rees 12.95