Oracle Cloud Infrastructureドキュメント

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

自律型AIデータベースを使用すると、外部表を使用して、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ドキュメントをオブジェクトとして問い合せることができます:

  • 行区切りの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 }

  1. 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: 問い合せるソース・ファイルのカンマ区切りリストです。

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

    SELECT * FROM fruit_1;

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

この例では、JSONオブジェクトの単一の配列を問い合せる方法を示します。デフォルトでは、アレイは開梱されます。

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

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

  1. 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;
/

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

    SELECT * FROM fruit_2a;

  3. 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;
/

  4. 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 }
  ]
}

  1. 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;
/

  2. 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 }

  1. 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;
/

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

    SELECT * FROM fruit_4;

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

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

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

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

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

fruitMultiLineFile1.json

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

fruitMultiLineFile2.json

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

fruitMultiLineFile3.json

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

  1. これらの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;
/

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

    SELECT * FROM fruit_5;

例: 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 AI 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 }

  1. 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: 問い合せるソース・ファイルのカンマ区切りリストです。

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

    SELECT * FROM fruit_6;

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

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

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

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

  1. 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;
/

  2. 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 }
  ]
}

  1. 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;
/

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

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

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

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

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

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

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

fruitMultiLineFile1.json

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

fruitMultiLineFile2.json

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

fruitMultiLineFile3.json

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

  1. これらの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;
/

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

    SELECT * FROM fruit_9;

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