前提

以下環境を前提としたメモとなります。
- Database : Autonomous Database 19c
- Object Storageからのファイルロード

Step

① JsonファイルからViewを作成
② Tableを作成
③ 上記①と②をJoinするクエリを実行

① JsonファイルからViewを作成

以下のJsonファイルのViewを作成する

{"name" : "Blake", "department_id" : 10, "job" : "Intern", "salary" : 30000}
{"name" : "Smith",  "department_id" : 20, "job" : "Programmer", "salary" : 80000}
{"name" : "Miller",  "department_id" : 20, "job" : "Programmer", "salary" : 90000}
{"name" : "Clark",  "department_id" : 30, "job" : "Manager", "salary" : 100000}
{"name" : "King",  "department_id" : 30, "job" : "President", "salary" : 200000, "email" : "king@example.com"}

１．Object Storageに上記ファイルをアップロード

２．上記ファイルを外部表として扱うためのDDLを作成

BEGIN
    DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
                              TABLE_NAME     => 'EMP1_JSON_EXT',
                              credential_name => 'JSON_EXT_CRED'
                              FILE_URI_LIST  => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/JSON_EXT/o/emp.json',
                              COLUMN_LIST    => 'json_document varchar2(1000)',
                              FIELD_LIST     => 'json_document char(1000)',
                              FORMAT         => JSON_OBJECT(
                                                  'type' VALUE 'json',
                                                  'rejectlimit' VALUE '999999999'
                              )
    );
END;
/

FILE_URI_LISTは、アップロードしたObject Storage上のファイルのパスを記載

select * from EMP1_JSON_EXT;

３．上記外部表のView（Jsonカラムを2次元表として扱うためのView）を作成

CREATE OR REPLACE FORCE EDITIONABLE VIEW "V_EMP_JSON_EXT" (
    "name",
    "department_id",
    "job",
    "email",
    "salary"
) DEFAULT COLLATION "USING_NLS_COMP" AS
    SELECT
        JT."name",
        JT."department_id",
        JT."job",
        JT."email",
        JT."salary"
    FROM
        "EMP1_JSON_EXT" RT,
        JSON_TABLE ( "JSON_DOCUMENT", '$[*]'
                COLUMNS
                    "name" VARCHAR2 ( 8 ) PATH '$.name',
                    "department_id" NUMBER ( 16 ) PATH '$.department_id',
                    "job" VARCHAR2 ( 16 ) PATH '$.job',
                    "email" VARCHAR2 ( 16 ) PATH '$.email',
                    "salary" NUMBER PATH '$.salary'
            )
        JT;

select * from V_EMP_JSON_EXT;

②Tableを作成

CREATE TABLE "USER2"."DEPARTMENTS" 
   (    "DEPARTMENT_ID" NUMBER(4,0), 
    "DEPARTMENT_NAME" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP", 
    "MANAGER_ID" NUMBER(6,0), 
    "LOCATION_ID" NUMBER(4,0)
   )  DEFAULT COLLATION "USING_NLS_COMP" ;

select * from DEPARTMENTS;

③ 上記①と②をJoinするクエリを実行

select e."name", e."department_id", d.DEPARTMENT_NAME from V_EMP_JSON_EXT e , DEPARTMENTS d
where e."department_id" = d.DEPARTMENT_ID;

Autonomous Database : Jsonファイルから作成したViewとTableをJoinしたときのメモ

前提

Step

① JsonファイルからViewを作成

②Tableを作成

③ 上記①と②をJoinするクエリを実行

参考