外部表を使用したオブジェクト・ストレージのJSONデータの問合せ

機械翻訳について

外部表を使用したオブジェクト・ストレージのJSONデータの問合せ

Autonomous AI Databaseでは、外部表を使用して、Oracle Cloud Infrastructure Object Storageに格納されているJSONドキュメントにアクセスできます。

データベース・バージョンに応じて、様々な形式でJSONドキュメントに対して問合せを実行できます。

Oracle AI Database 26aiによるJSONオブジェクトとしてのJSONドキュメントのクエリ
Oracle Database 19c以前を使用したテキスト・データとしてのJSONドキュメントの問合せ

Autonomous AI Databaseは、外部ファイルで次の一般的なJSONドキュメント表現を処理します:

ファイル内の複数行のJSONドキュメント
ファイルごとに1つの複数行JSONドキュメント

次に、これらを詳細に説明する例を示します。

例: JSONオブジェクトとしてのJSONドキュメントの問合せ
Autonomous AI Databaseでは、JSONドキュメントに対する問合せをJSONオブジェクトとして実行できます。
例: テキスト・データとしてのJSONドキュメントの問合せ
Autonomous AI Databaseでは、JSONドキュメントに対する問合せをテキスト・データとして実行できます。

親トピック: Autonomous AI Databaseを使用した外部データの問合せ

例: JSONオブジェクトとしてのJSONドキュメントの問合せ

Autonomous AI Databaseでは、JSONドキュメントに対する問合せをJSONオブジェクトとして実行できます。

次のような様々なタイプのJSONドキュメントをオブジェクトとして問い合せることができます。

行区切りのJSONドキュメント
JSON配列
オブジェクト・ラップ済JSON配列
拡張JSON (EJSON)のサポート
複数行ファイルを含む単一のJSONドキュメント

次の例は、フォーマット・オプションjsondocを使用してOracle AI Database 26aiでオブジェクトとしてJSONドキュメントにアクセスする方法を示しています。

例1: JSONオブジェクトとしての行区切りのJSONドキュメントの問合せ

この例では、オブジェクト・ストレージ内の複数の行区切りのJSONドキュメントを含むJSONファイルを問い合せる方法を示します。

JSONファイル(たとえば、fruitLineDelimited.json)は、1行に1つのオブジェクトを含む3つの行を持つオブジェクト・ストレージに存在します。

{ "name": "apple", "count": 20 }
{ "name": "orange", "count": 42 }
{ "name": "pear", "count": 10 }

JSONファイルを使用して、データベースに外部表を作成します。
```
BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_1',
    credential_name =>'DEF_CRED_NAME',
    format => json_object('type' value 'jsondoc'),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitLineDelimited.json';
END;
/
```
パラメータは次のとおりです:
- table_name: 外部表名です。
- credential_name: 前のステップで作成した資格証明の名前です。 credential_nameパラメータは、Oracleオブジェクトのネーミング規則に準拠している必要があります。詳細については、「データベース・オブジェクトのネーミング・ルール」を参照してください。
- format: ソース・ファイルの形式を記述するために指定できるオプションを定義します。
- file_uri_list: 問い合せるソース・ファイルのカンマ区切りリストです。
SELECT文を使用して外部表を問い合せます。
```
SELECT * FROM fruit_1;
```

例2: JSONオブジェクトとしてのJSON配列の問合せ

この例では、1つの配列のJSONオブジェクトを問い合せる方法を示します。配列はデフォルトで展開されます。

JSONファイル(たとえば、fruitArray.json)はオブジェクト・ストレージに存在し、次のデータがあります。

[
   {
      "name" : "apple",
      "count": 20
   },
   {
      "name" : "orange",
      "count": 42
   },
   {
      "name" : "pear",
      "count": 10
   }
]

JSONファイルを使用して、データベースに外部表を作成します。

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_2a',
    credential_name =>'DEF_CRED_NAME',
    format => json_object('type' value 'jsondoc'),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitArray.json';
END;
/

SELECT文を使用して外部表を問い合せます。
```
SELECT * FROM fruit_2a;
```

jsonpathオプションを使用して、配列構造を保持し、JSONオブジェクトの配列を返すことができます。

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_2b',
    credential_name =>'DEF_CRED_NAME',
    format => json_object('type' value 'jsondoc', 'jsonpath' value '$'),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitArray.json';
END;
/

SELECT文を使用して外部表を問い合せます。
```
SELECT * FROM fruit_2b;
```

例3: JSONオブジェクトとしての問合せオブジェクト・ラップ済JSON配列

この例では、外部JSONドキュメントにラップされたJSONファイルを問い合せる方法を示します。この例では、ロードするデータへのフォーマット・オプションjsonpathを使用してパスを指定します。パスは配列につながる必要があります。行は前の例のようにマップされます。

JSONファイル(たとえば、fruitEmbeddedArray.json)はオブジェクト・ストレージに存在し、次のデータがあります。

{
  "last_updated": 1434054678,
  "ttl": 0,
  "version": "1.0",
  "fruit": [
    { "name" : "apple", "count": 20 },
    { "name" : "orange", "count": 42 },
    { "name" : "pear", "count": 10 }
  ]
}

JSONファイルを使用して、データベースに外部表を作成します。

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_3',
    credential_name =>'DEF_CRED_NAME',
    format => json_object('type' value 'jsondoc', 'jsonpath' value '$.fruit[*]'),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitEmbeddedArray.json';
END;
/

SELECT文を使用して外部表を問い合せます。
```
SELECT * FROM fruit_3;
```

例4: JSONオブジェクトとしての拡張JSON (EJSON)ドキュメントの問合せ

この例では、EJSONファイルを問い合せる方法を示します。 SQL型JSONは、TIMESTAMP、DOUBLE、FLOAT、RAWなどの拡張JSON型を表すことができます。 JSONテキストは、拡張JSON形式を使用して拡張JSON型を表すことができます。これらのEJSON注釈は、対応する型に自動的に変換されます。

EJSONファイル(たとえば、fruitEjson.json)はオブジェクト・ストレージに存在し、次のデータがあります。

{ "name" : "apple", "count": 20, "modified":{"$date":"2020-06-29T11:53:05.439Z"} }
{ "name" : "orange", "count": 42 }
{ "name" : "pear", "count": 10 }

JSONファイルを使用して、データベースに外部表を作成します。

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_4',
    credential_name =>'DEF_CRED_NAME',
    format => json_object('type' value 'jsondoc'),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitEjson.json';
END;
/

SELECT文を使用して外部表を問い合せます。
```
SELECT * FROM fruit_4;
```

例5: 複数行ファイルをJSONオブジェクトとして使用した単一のJSONドキュメントの問合せ

この例では、複数行ファイルを使用して単一のJSONドキュメントを問い合せる方法を示します。

複数行ファイルを含む単一のJSONドキュメントを表にマップできます。この表では、ディレクトリ内の各JSONファイルが1行にマップされます。
複数行ファイルを含む単一のJSONドキュメントには、各JSONファイルが表内の単一行にマップされるJSONファイルを含むディレクトリを指定できます。

この例では、次のデータを含むJSONファイルを使用します。

fruitMultiLineFile1.json

{
   "name" : "apple",
   "count": 42
}

fruitMultiLineFile2.json

{
   "name" : "orange",
   "count": 5
}

fruitMultiLineFile3.json

{
   "name" : "pear",
   "count": 10
}

これらのJSONファイルを使用して、データベースに外部表を作成します。

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_5',
    credential_name =>'DEF_CRED_NAME',
    format => json_object('type' value 'jsondoc'),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitMultiLine*.json';
END;
/

SELECT文を使用して外部表を問い合せます。
```
SELECT * FROM fruit_5;
```

親トピック: 外部表を使用したオブジェクト・ストレージのJSONデータの問合せ

例: テキスト・データとしてのJSONドキュメントの問合せ

Autonomous AI Databaseでは、JSONドキュメントに対する問合せをテキスト・データとして実行できます。

Oracle Database 19cでは、ネイティブJSONデータ型はサポートされていません。そのため、JSONデータ表現の外部データはテキスト・データとして処理されます。したがって、外部オブジェクトのJSONデータは、VARCHAR2、CLOBまたはBLOBデータ型を使用して処理されます。

JSONドキュメントへのアクセス時に考慮する点

フィールドdelimiterオプションを、JSONデータにない値または文字に定義します。つまり、フィールド・デリミタ値がJSONドキュメントに存在しません。 DBMS_CLOUDパッケージを使用して表を作成する場合、デフォルトのフィールド・デリミタは'|' (パイプ)です。

JSONデータに'|'が含まれているシナリオについて考えてみましょう。たとえば:
```
{"attr1": "oK4IJ|V", "attr2": "igNâ}
```
フィールド・デリミタが指定されていない場合、'|'はフィールド・デリミタとみなされ、問合せは次を返します。
```
{"attr1": "oK4IJ
```
JSONドキュメントが意図せずに分割されないように、データを無効なJSONドキュメントにレンダリングするには、フィールド・デリミタ・フォーマット・オプションをデータにない値に設定します。たとえば:
```
json_object('delimiter' value 'X''0''')
```
JSONドキュメントが単一のドキュメント行区切りデータとして格納されている場合、それらは問題なくテキスト・データとして処理されます。外部ファイルの複数の行にまたがるJSONドキュメントの場合、フォーマット・オプションrecorddelimiterを、データ・ファイルに存在しない値または文字に設定する必要があります。たとえば:
```
json_object('recorddelimiter' value '0x''1B''')
```
recorddelimiterオプションが設定されていない場合、すべての行は個々のレコードとみなされます。デフォルトのレコード・デリミタnewlineは、データを無効なJSONドキュメントにレンダリングします。
外部データ・ファイルでは、フィールドが明示的に指定されていない場合、デフォルトのデータ型はCHAR(255)に設定されます。したがって、255文字を超えるJSONドキュメントの読取りはエラーを返します。したがって、パラメータfield_listを、ファイル内の最大JSONドキュメントより大きい値に設定する必要があります。たとえば:
```
field_list =>'"MYDATA" char(10000)'
```
Autonomous Databaseのデフォルトの読取りバッファ・サイズは10MBです。 10MBを超えるJSONドキュメントの場合、フォーマット・オプションreadsizeを20MBのように大きい値に設定する必要があります。
```
json_object('readsize' value '20000000')
```

次に、これらすべてのパラメータを含む外部表定義の例を示します。

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_5',
    credential_name =>'DEF_CRED_NAME',
    format => json_object('type' value 'jsondoc'),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitMultiLine*.json';
END;
/

次の例は、CLOBデータ型を使用してOracle Database 19c以前でJSONドキュメントにテキスト・データとしてアクセスする方法を示しています。または、JSONドキュメントのサイズに応じて、VARCHAR2データ型を使用することもできます。

行区切りのJSONドキュメント
JSON配列
オブジェクト・ラップ済JSON配列
複数行ファイルを含む単一のJSONドキュメント

例1: テキスト・データとしての行区切りのJSONドキュメントの問合せ

この例では、オブジェクト・ストレージ内の複数の行区切りのJSONドキュメントを含むJSONファイルを問い合せる方法を示します。

JSONファイル(たとえば、fruitLineDelimited.json)は、1行に1つのオブジェクトを含む3つの行を持つオブジェクト・ストレージに存在します。

{ "name": "apple", "count": 20 }
{ "name": "orange", "count": 42 }
{ "name": "pear", "count": 10 }

JSONファイルを使用して、データベースに外部表を作成します。
```
BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_6',
    column_list => 'mydata clob',
    credential_name =>'DEF_CRED_NAME',
    field_list =>'"MYDATA" char(10000)',
    format => json_object('delimiter' value 'X''0'''),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitLineDelimited.json';
END;
/
```
パラメータは次のとおりです:
- table_name: 外部表名です。
- column_list: 外部表の列名とデータ型のカンマ区切りリストです。リストには、データファイル内のカラムと、オブジェクト名から派生したカラム(file_uri_listで指定されたファイル・パスの名前から)が含まれます。
- credential_name: 前のステップで作成した資格証明の名前です。 credential_nameパラメータは、Oracleオブジェクトのネーミング規則に準拠している必要があります。詳細については、「データベース・オブジェクトのネーミング・ルール」を参照してください。
- field_list: ソース・ファイル内のフィールドとそのデータ型を識別します。
- format: ソース・ファイルの形式を記述するために指定できるオプションを定義します。
- file_uri_list: 問い合せるソース・ファイルのカンマ区切りリストです。
SELECT文を使用して外部表を問い合せます。
```
SELECT * FROM fruit_6;
```

例2: テキスト・データとしてのJSON配列の問合せ

この例では、1つの配列のJSONオブジェクトを問い合せる方法を示します。外部表のデータ・アクセスの一部として配列を自動的に解凍することはできませんが、データへのアクセス中に解凍することはできます。

JSONファイル(たとえば、fruitArray.json)はオブジェクト・ストレージに存在し、次のデータがあります。

[
   {
      "name" : "apple",
      "count": 20
   },
   {
      "name" : "orange",
      "count": 42
   },
   {
      "name" : "pear",
      "count": 10
   }
]

JSONファイルを使用して、データベースに外部表を作成します。

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_7',
    column_list => 'mydata clob',
    credential_name =>'DEF_CRED_NAME',
    field_list =>'"MYDATA" char(10000)',
    format => json_object('delimiter' value 'X''0''','recorddelimiter' value '0x''1B'''),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitArray.json';
END;
/

SELECT文を使用して外部表を問い合せます。

SELECT * 
FROM fruit_7
NESTED mydata 
COLUMNS (nested path '$[*]' 
COLUMNS (data format json path '$'));

例3: テキスト・データとしての問合せオブジェクト・ラップ済JSON配列

この例では、外部JSONドキュメントにラップされたJSONファイルを問い合せる方法を示します。外部表データ・アクセスの一部として配列を自動的に解凍することはできませんが、データへのアクセス中に解凍およびJSONドキュメント・サブセット化を実行できます。

JSONファイル(たとえば、fruitEmbeddedArray.json)はオブジェクト・ストレージに存在し、次のデータがあります。

{
  "last_updated": 1434054678,
  "ttl": 0,
  "version": "1.0",
  "fruit": [
    { "name" : "apple", "count": 20 },
    { "name" : "orange", "count": 42 },
    { "name" : "pear", "count": 10 }
  ]
}

JSONファイルを使用して、データベースに外部表を作成します。

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_8',
    column_list => 'mydata clob',
    credential_name =>'DEF_CRED_NAME',
    field_list =>'"MYDATA" char(10000)',
    format => json_object('delimiter' value 'X''0''','recorddelimiter' value '0x''1B'''),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitEmbeddedArray.json';
END;
/

SELECT文を使用して外部表を問い合せます。

SELECT * 
FROM fruit_8 
NESTED mydata 
COLUMNS (nested path '$.fruit[*]' 
COLUMNS (data format json path '$'));

例4: 複数行ファイルをテキスト・データとして使用した単一のJSONドキュメントの問合せ

この例では、複数行ファイルを使用して単一のJSONドキュメントを問い合せる方法を示します。

複数行ファイルを含む単一のJSONドキュメントを表にマップできます。この表では、ディレクトリ内の各JSONファイルが1行にマップされます。
複数行ファイルを含む単一のJSONドキュメントには、各JSONファイルが表内の単一行にマップされるJSONファイルを含むディレクトリを指定できます。

この例では、次のデータを含むJSONファイルを使用します。

fruitMultiLineFile1.json

{
   "name" : "apple",
   "count": 42
}

fruitMultiLineFile2.json

{
   "name" : "orange",
   "count": 5
}

fruitMultiLineFile3.json

{
   "name" : "pear",
   "count": 10
}

これらのJSONファイルを使用して、データベースに外部表を作成します。

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'fruit_9',
    column_list => 'mydata clob',
    credential_name =>'DEF_CRED_NAME',
    field_list =>'"MYDATA" char(10000)',
    format => json_object('delimiter' value 'X''0''','recorddelimiter' value '0x''1B'''),
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/fruitMultiLine*.json';
END;
/

SELECT文を使用して外部表を問い合せます。
```
SELECT * FROM fruit_9;
```

JSONドキュメントの詳細は、JSONドキュメント・ストアを参照してください

親トピック: 外部表を使用したオブジェクト・ストレージのJSONデータの問合せ