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

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

# Step

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

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

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

```json
{"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を作成

```sql
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上のファイルのパスを記載

```sql
select * from EMP1_JSON_EXT;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/2fda26b1-b5bf-cee1-d5ce-194c1ea94eb1.png)



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

```sql
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;
```

```sql
select * from V_EMP_JSON_EXT;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/db1976d6-20b1-f85c-c464-570ae1bd5859.png)

## ②Tableを作成

```sql
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" ;
```

```sql
select * from DEPARTMENTS;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/90210354-c969-e62a-3661-86e80fb79df2.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/00b3aeec-d56c-274b-9610-f7b5976fa51b.png)

# 参考

- Documentations
    - [DBMS_CLOUD Package Format Options](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/format-options.html#GUID-08C44CDA-7C81-481A-BA0A-F7346473B703)
    - [ADWでの外部表の作成](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html#GUID-2AFBEFA4-992E-4F53-96DB-F560084C7DA9)
    - [JSONデータのロード](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/autonomous-json-load.html#GUID-6B9EBE09-FA5F-43D4-B3EA-DCCD19C77051)

- Blogs
 - [Object Storage上のJSONファイルをAutonomous Databaseから外部表としてアクセスしたときのメモ](https://qiita.com/TakuyaAma/items/86539cb99596c8d6b0ff)
 - [Fastest Way to Upload a bunch of Documents to your Oracle JSON Collection?](https://www.thatjeffsmith.com/archive/2020/10/fastest-way-to-upload-a-bunch-of-documents-to-your-oracle-json-collection/)