SELECTメソッドとJSONメソッドを使用したJSONデータの抽出

Teradata® Database JSONデータ型

prodname
Teradata Database
vrm_release
16.20
category
プログラミング リファレンス
featnum
B035-1150-162K-JPN

SELECT文またはJSONメソッド(JSONExtract、JSONExtractValue、JSONExtractLargeValue、JSON_TABLE、JSON_SHRED_BATCH、および JSON_SHRED_BATCH_U)を使用して、Teradata Databaseに格納されているJSON文書からJSONデータを抽出できます。

提案されたANSI SQL/JSON規格に準拠するために、これらの抽出メソッドは目的のパスに一致する1つの値を返します。JSONPath問合わせ文字列に次の要素のいずれかがある場合を除いて、最初に見つかった結果が返されます。

  • 再帰下降演算子
  • ワイルドカード
  • 名前またはインデックス リスト
  • インデックス スライス
  • フィルタ
JSONExtractValueとJSONExtractLargeValueのメソッドは、単一のスカラー値またはJSON nullしか抽出しません。複数の値がJSONPath問合わせ式と一致すると、複数の結果が見つかったことを示す警告やエラー メッセージ文字列が返されます。

検索は、必ずしも文書全体を検索する必要がないように最適化されます。ただし、これは次のシナリオが可能であることを意味します。

  1. JSON妥当性検査を無効にする。
  2. 不正な形式のJSONデータを挿入する。例えば、次のように挿入します。{"name":"Cameron" 123456}
  3. JSON妥当性検査を有効にする。
  4. キー"name"の値を抽出する。

この場合、文書全体を検索し、不正な形式のデータに到達する前に結果が見つかったため、"Cameron"の正しい結果が返されます。検索中、結果を見つける前に不正な形式のデータが見つかった場合は、構文エラーが報告されます。

例: JSONエンティティ参照、JSONExtractValue、およびJSONExtract

JSONエンティティ参照構文またはJSONExtractValue(または、JSONExtractLargeValue)メソッドでSELECT文を使用すると、JSONインスタンスから単一のスカラー値を抽出できます。ただし、JSONエンティティ参照またはJSONExtractValue(または、JSONExtractLargeValue)メソッドを使用して、複数の値を抽出しようとすると、デフォルトで警告とエラー メッセージ文字列が表示されます。

JSONエンティティ参照を使用して複数のオブジェクト メンバーを抽出しているので、次のクエリーは警告とエラー メッセージ文字列を返します。 代わりに返される値のリストが必要な場合は、SET SESSION DOT NOTATION...ON ERROR文またはDBSコントロール フィールドDotNotationOnErrorConditionを使用してこの動作を指定する必要があります。 JSONエンティティ参照で次の構文要素を使用すると、複数の結果が返されることが考えられるので注意してください。
  • 再帰下降演算子
  • ワイルドカード
  • 名前またはインデックス リスト
  • スライス
詳細は、JSONエンティティ参照(ドット表記構文)を参照してください。
SELECT NEW JSON('{"name":"Al","name":"Betty"}').name;

結果:

*** Query completed. One row found. One column returned.
*** Warning: 7548 More than one result per JSON instance found.
*** Total elapsed time was 1 second.

NEW JSON('{"name":"Al","name":"Betty"}', LATIN).name
-----------------------------------------------------------------
*** ERROR MULTI RESULT ***

JSONエンティティ参照の代わりにJSONExtractValueを使用する同じ問合わせも、警告とエラー メッセージ文字列を返します。

SELECT NEW JSON('{"name":"Al","name":"Betty"}').JSONExtractValue('$.name');

結果:

*** Query completed. One row found. One column returned.
*** Warning: 7548 More than one result per JSON instance found.
*** Total elapsed time was 1 second.

NEW JSON('{"name":"Al","name":"Betty"}', LATIN).JSONEXTRACTVALUE('$.name')
---------------------------------------------------------------------------
*** ERROR MULTI RESULT ***

JSONExtractメソッドを使用して、複数の値を抽出できます。 JSONExtractメソッドを使用した同じ問合わせは成功し、JSON配列の複数の値を返します。

SELECT NEW JSON('{"name":"Al","name":"Betty"}').JSONExtract('$.name');

結果:

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

NEW JSON('{"name":"Al","name":"Betty"}', LATIN).JSONEXTRACT('$.name')
---------------------------------------------------------------------------
["Al","Betty"]