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ドキュメント

これらを詳細に説明する例をいくつか示します。

親トピック: 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)は、オブジェクト・ストレージ内に3行あり、1行に1つのオブジェクトがあります。 

{ "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オブジェクトの単一の配列を問い合せる方法を示します。デフォルトでは、アレイは開梱されます。

たとえば、fruitArray.jsonなどの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を使用して、ロードするデータへのパスを指定します。パスは配列につながる必要があります。行は前の例のようにマップされます。

たとえば、fruitEmbeddedArray.jsonなどの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注釈は、対応する型に自動的に変換されます。

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

{ "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 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)は、オブジェクト・ストレージ内に3行あり、1行に1つのオブジェクトがあります。 

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

たとえば、fruitArray.jsonなどの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ドキュメント・サブセット化を実行できます。

たとえば、fruitEmbeddedArray.jsonなどの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ドキュメント・ストアを参照してください