Autonomous Database でのJSONデータ操作

# この記事で記載すること

LiveLabsの「[Choose your Own JSON Adventure: Relational or Document Store Workshop](https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=813&clear=RR,180&session=115428618540790)」を参考にAutonomous Database でJSONデータを扱うときのメモを記載
主に以下の内容を記載

- JSONファイルのロード
 - Database Actions（SQL Developer Web）のData Loading機能を利用してPC上のJSONファイルを表にロード
 - Database Actions（Data Tools / Load) を用いてObject Storage上のJSONファイルを表にロード＋JSONを2次元化したViewの自動生成
- JSON in tableへのSQL操作
 - Simple Dot-Notation
 - SQL/JSON Path Expressions  
- JSON Collectionへのアクセス
 - Collectionの作成
 - Collectionへのデータロード
 - QBEを用いたデータアクセス
 - JSONデータ更新
 - Serach Indexを用いた検索
 - Sort


# 前提/環境

## Cloudリソース

以下のCloudリソースが利用できる状態となっていることを前提とします。

- Autonomous Database: ATPが以下コンディションで作成されている
・Database Version: 19c
・Access Type: Allow secure access from everywhere

- Object Storage: JSONファイルの格納用バケットが準備されている
・Bucket Name: common

## JSONファイル

以下からダウンロードしたファイルを用いてJSONデータを処理

[Airport Delays 2003-2016](https://objectstorage.us-ashburn-1.oraclecloud.com/p/rQEg_sXqFmCO8Swe5vVesi3-hmiCfLsY0Yc8mr_k0zdX2NWhhm34zrwhs0uvg8K-/n/c4u03/b/developer-library/o/airportDelays.json)

```json
[
    {
        "id": 1,
        "AirportCode": "ATL",
        "Name": "Atlanta, GA: Hartsfield-Jackson Atlanta International",
        "Time": {
            "Label": "2003/06",
            "Month": 6,
            "Month Name": "June",
            "Year": 2003
        },
        "Statistics": {
            "# of Delays": {
                "Carrier": 1009,
                "Late Aircraft": 1275,
                "National Aviation System": 3217,
                "Security": 17,
                "Weather Codes":["SNW","RAIN","SUN","CLDY"],
                "Weather": 328
            },
            "Carriers": {
                "Aircraft Types": [{"make" : "Boeing", "models" : ["717","737","757","767","777","787"]},
                                   {"make" : "Airbus", "models" : ["A320","A321","A330","A340","A350","A380"]}],
                "Names": "American Airlines Inc.,JetBlue Airways,Continental Air Lines Inc.,Delta Air Lines Inc.,Atlantic Southeast Airlines,AirTran Airways Corporation,America West Airlines Inc.,Northwest Airlines Inc.,ExpressJet Airlines Inc.,United Air Lines Inc.,US Airways Inc.",
                "Total": 11
            },
            "Flights": {
                "Cancelled": 216,
                "Delayed": 5843,
                "Diverted": 27,
                "On Time": 23974,
                "Total": 30060
            },
            "Minutes Delayed": {
                "Carrier": 61606,
                "Late Aircraft": 68335,
                "National Aviation System": 118831,
                "Security": 518,
                "Total": 268764,
                "Weather Codes":["SNW","RAIN","SUN","CLDY"],
                "Weather": 19474
            }
        }
    },
    {
        "id": 2,
        "AirportCode": "BOS",
        "Name": "Boston, MA: Logan International",
        "Time": {
            "Label": "2003/06",
            "Month": 6,
            "Month Name": "June",
            "Year": 2003
        },
        "Statistics": {
            "# of Delays": {
                "Carrier": 374,
                "Late Aircraft": 495,
                "National Aviation System": 685,
                "Security": 3,
                "Weather Codes":["SNW","RAIN","SUN","CLDY"],
                "Weather": 66
            },
            "Carriers": {
                "Aircraft Types": [{"make" : "Boeing", "models" : ["717","737","757","767","777","787"]},
                                   {"make" : "Airbus", "models" : ["A320","A321","A330","A340","A350","A380"]}],
                "Names": "American Airlines Inc.,Alaska Airlines Inc.,Continental Air Lines Inc.,Atlantic Coast Airlines,Delta Air Lines Inc.,Atlantic Southeast Airlines,AirTran Airways Corporation,America West Airlines Inc.,American Eagle Airlines Inc.,Northwest Airlines Inc.,ExpressJet Airlines Inc.,ATA Airlines d/b/a ATA,United Air Lines Inc.,US Airways Inc.",
                "Total": 14
            },
            "Flights": {
                "Cancelled": 138,
                "Delayed": 1623,
                "Diverted": 3,
                "On Time": 7875,
                "Total": 9639
            },
            "Minutes Delayed": {
                "Carrier": 20319,
                "Late Aircraft": 28189,
                "National Aviation System": 24400,
                "Security": 99,
                "Total": 77167,
                "Weather Codes":["SNW","RAIN","SUN","CLDY"],
                "Weather": 4160
            }
        }
    },
----
```

# 作業ユーザの作成

Database Actionsからユーザを作成

１．Database Actionsを起動してAdminユーザでログイン

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/46a7fb7b-c3fa-02b6-4692-1622e51f89b5.png)


２．Administration > Database Usersを選択 > Create Userをクリック

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/48dfa690-0d0d-20d4-d982-7981e7457bf6.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/2fb11b6b-17bf-2d1b-1744-c13209eb0f93.png)

３．以下のとおりユーザを作成

ユーザ情報を登録
・User Name: GARY
・Web Access: 有効化
・Quota: Unlimited
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/b01c7153-bcc7-02cd-fe1b-db106849838c.png)
権限を付与
・`PDB_BDA`
・`SODA_APP`
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/d49d6bed-419c-3cdf-abe2-0ed0d9660115.png)

# JSONファイルのロード
## - Database Actions（SQL Developer Web）のData Loading機能を利用してPC上のJSONファイルを表にロード

１．Database ActionsのSQLを選択

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/9ce8619f-65ac-fd76-5c71-07145032110b.png)

２．`Data Loading`機能を用いて[Airport Delays 2003-2016](https://objectstorage.us-ashburn-1.oraclecloud.com/p/rQEg_sXqFmCO8Swe5vVesi3-hmiCfLsY0Yc8mr_k0zdX2NWhhm34zrwhs0uvg8K-/n/c4u03/b/developer-library/o/airportDelays.json)をロード

→ 「ID列をPKに設定する」を除きデフォルト設定でデータロードを実行

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/df2f1487-f5fa-ea9a-f51c-0a17653106a3.png)

３．テーブルが作成され、データがロードされていることを確認

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/44174976-0fd6-b218-10b5-2f13949bd91c.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/874d8d7d-1642-75fb-3d3f-014bc47b1c1c.png)

## - （参考）Database Actions（Data Tools / Load) を用いてObject Storage上のJSONファイルを表にロード＋JSONを2次元化したViewの自動生成

０．Object Storage/BucketにJSONファイル（[Airport Delays 2003-2016](https://objectstorage.us-ashburn-1.oraclecloud.com/p/rQEg_sXqFmCO8Swe5vVesi3-hmiCfLsY0Yc8mr_k0zdX2NWhhm34zrwhs0uvg8K-/n/c4u03/b/developer-library/o/airportDelays.json)）をアップロード

１．Database ActionsのData Tools/Data Loadを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/eafb8918-44fe-c8a1-a162-09122382d6ac.png)

２．CLOUD STORAGEを選択してNextをクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/7e9fbcb3-bdc1-03ba-e6d2-6288120b5e19.png)

３．ロードするファイルをアップするObject Storage/Bucketを登録
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/c4987041-6f40-2038-72ea-0ccdcd21d52f.png)

４．対象ファイルを選択して右にドラッグ＆ドロップ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/aeffca71-b649-cd2e-8521-329aa78d737f.png)

５．Startボタンをクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/f384c2d0-1a75-05b9-df9e-d20b761d5e09.png)

６．SQL Developer WebからテーブルとViewができていることを確認

自動生成されたViewのDDL

```SQL
CREATE OR REPLACE FORCE EDITIONABLE VIEW "ADMIN"."AIRPORTDELAYS" ("id", "Name", "Year", "Label", "Month", "Month Name", "Total", "Delayed", "On Time", "Diverted", "Cancelled", "Names", "Total_16", "Carrier", "Weather", "Security", "Late Aircraft", "National Aviation System", "Total_25", "Carrier_26", "Weather_27", "Security_28", "Late Aircraft_29", "National Aviation System_31", "AirportCode", "make", "scalar_string", "scalar_string_23", "scalar_string_30") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT JT."id",JT."Name",JT."Year",JT."Label",JT."Month",JT."Month Name",JT."Total",JT."Delayed",JT."On Time",JT."Diverted",JT."Cancelled",JT."Names",JT."Total_16",JT."Carrier",JT."Weather",JT."Security",JT."Late Aircraft",JT."National Aviation System",JT."Total_25",JT."Carrier_26",JT."Weather_27",JT."Security_28",JT."Late Aircraft_29",JT."National Aviation System_31",JT."AirportCode",JT."make",JT."scalar_string",JT."scalar_string_23",JT."scalar_string_30"
FROM "ADMIN"."AIRPORTDELAYS_COLLECTION" RT,
JSON_TABLE("JSON_DOCUMENT", '$[*]' COLUMNS 
"id" number path '$.id',
"Name" varchar2(128) path '$.Name',
"Year" number path '$.Time.Year',
"Label" varchar2(8) path '$.Time.Label',
"Month" number path '$.Time.Month',
"Month Name" varchar2(16) path '$.Time."Month Name"',
"Total" number path '$.Statistics.Flights.Total',
"Delayed" number path '$.Statistics.Flights.Delayed',
"On Time" number path '$.Statistics.Flights."On Time"',
"Diverted" number path '$.Statistics.Flights.Diverted',
"Cancelled" number path '$.Statistics.Flights.Cancelled',
"Names" varchar2(512) path '$.Statistics.Carriers.Names',
"Total_16" number path '$.Statistics.Carriers.Total',
 NESTED PATH '$.Statistics.Carriers."Aircraft Types"[*]' COLUMNS (
"make" varchar2(8) path '$.make',
 NESTED PATH '$.models[*]' COLUMNS (
"scalar_string" varchar2(4) path '$[*]')),
"Carrier" number path '$.Statistics."# of Delays".Carrier',
"Weather" number path '$.Statistics."# of Delays".Weather',
"Security" number path '$.Statistics."# of Delays".Security',
"Late Aircraft" number path '$.Statistics."# of Delays"."Late Aircraft"',
 NESTED PATH '$.Statistics."# of Delays"."Weather Codes"[*]' COLUMNS (
"scalar_string_23" varchar2(4) path '$[*]'),
"National Aviation System" number path '$.Statistics."# of Delays"."National Aviation System"',
"Total_25" number path '$.Statistics."Minutes Delayed".Total',
"Carrier_26" number path '$.Statistics."Minutes Delayed".Carrier',
"Weather_27" number path '$.Statistics."Minutes Delayed".Weather',
"Security_28" number path '$.Statistics."Minutes Delayed".Security',
"Late Aircraft_29" number path '$.Statistics."Minutes Delayed"."Late Aircraft"',
 NESTED PATH '$.Statistics."Minutes Delayed"."Weather Codes"[*]' COLUMNS (
"scalar_string_30" varchar2(4) path '$[*]'),
"National Aviation System_31" number path '$.Statistics."Minutes Delayed"."National Aviation System"',
"AirportCode" varchar2(4) path '$.AirportCode')JT
;
```

# JSONデータのSQLアクセス
## - [Simple Dot-Notation](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/simple-dot-notation-access-to-json-data.html#GUID-7249417B-A337-4854-8040-192D5CEFD576)

`Dot notation is designed for easy, general use and common use cases. Queries of JSON data that use dot-notation syntax return JSON values whenever possible.`

### 特定のフィールドのValue値を条件として、JSON DocumentationのFieldを返すSQL

```sql
select a.statistics 
from airportdelays a
where a.airportcode = 'SFO'
fetch first 1 row only;
```

```json
{
    "# of Delays": {
        "Carrier": 416,
        "Late Aircraft": 312,
        "National Aviation System": 1080,
        "Security": 14,
        "Weather Codes": [
            "SNW",
            "RAIN",
            "SUN",
            "CLDY"
        ],
        "Weather": 59
    },
    "Carriers": {
        "Aircraft Types": [
            {
                "make": "Boeing",
                "models": [
                    "717",
                    "737",
                    "757",
                    "767",
                    "777",
                    "787"
                ]
            },
            {
                "make": "Airbus",
                "models": [
                    "A320",
                    "A321",
                    "A330",
                    "A340",
                    "A350",
                    "A380"
                ]
            }
        ],
        "Names": "American Airlines Inc.,Alaska Airlines Inc.,Continental Air Lines Inc.,Delta Air Lines Inc.,America West Airlines Inc.,American Eagle Airlines Inc.,Northwest Airlines Inc.,SkyWest Airlines Inc.,ATA Airlines d/b/a ATA,United Air Lines Inc.,US Airways Inc.",
        "Total": 11
    },
    "Flights": {
        "Cancelled": 83,
        "Delayed": 1880,
        "Diverted": 9,
        "On Time": 8211,
        "Total": 10183
    },
    "Minutes Delayed": {
        "Carrier": 22673,
        "Late Aircraft": 17879,
        "National Aviation System": 45407,
        "Security": 1138,
        "Total": 90487,
        "Weather Codes": [
            "SNW",
            "RAIN",
            "SUN",
            "CLDY"
        ],
        "Weather": 3390
    }
}
```

### Nestされたフィールドの値を条件として設定

```sql
select a.statistics 
  from airportdelays a
 where a.airportcode = 'SFO'
   and a.time."Month Name" = 'June'
   and a.time.Year = '2010';
```

time fieldのValue

```json
{"Label":"2010/06","Month":6,"Month Name":"June","Year":2010}
```

### analytics function

```sql
select a.name, 
    sum(a.statistics."Minutes Delayed".Carrier) "Minutes Delayed" from airportdelays a
group by a.name
order by a.name;

                                                                  NAME    Minutes Delayed 
______________________________________________________________________ __________________ 
Atlanta, GA: Hartsfield-Jackson Atlanta International                            16425906 
Baltimore, MD: Baltimore/Washington International Thurgood Marshall               3067381 
Boston, MA: Logan International                                                   4629447 
Charlotte, NC: Charlotte Douglas International                                    4043589 
Chicago, IL: Chicago Midway International                                         2251158 
Chicago, IL: Chicago O'Hare International                                        12458548 
Dallas/Fort Worth, TX: Dallas/Fort Worth International                           11162806 
....
```

## - [SQL/JSON Path Expressions](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/json-path-expressions.html#GUID-2DC05D71-3D62-4A14-855F-76E054032494)

Oracle also has many built in JSON functions for working with document data which elevates the functionality found with Dot-Notation. 

SQL/JSON condition json_exists returns true if at least one value matches, and false if no value matches. If a single value matches, then SQL/JSON function json_value returns that value if it is scalar and raises an error if it is non-scalar. If no value matches the path expression then json_value returns SQL NULL.

SQL/JSON function json_query returns all of the matching values, that is, it can return multiple values. You can think of this behavior as returning a sequence of values, as in XQuery, or you can think of it as returning multiple values. (No user-visible sequence is manifested.)

### json_value

The SQL/JSON function JSON_VALUE finds a specified scalar JSON value in JSON data and returns it as a defined SQL value (date, number, timestamp, sdo_geometry, etc). 

特定のfieldの特定のValueを指定してitemを取得
例：id field = 5を指定してtime JSON Block内のLabel itemを取得

```sql
SQL> select json_value (
  2          time, 
  3          '$.Label'
  4      ) date_label
  5  from airportdelays a
  6  where a.id = 5;

   DATE_LABEL 
_____________ 
2003/06       
```

戻り値のデータ型を指定

```sql
SQL> select json_value (
  2          Statistics, 
  3          '$.Flights.Cancelled' returning number
  4      ) cancled_flights
  5  from airportdelays a
  6  where a.id = 5;

   CANCLED_FLIGHTS 
__________________ 
                74 
```

注意：json_valueはscalarのみreturn可能 → multiple valueの場合はエラーとなる（ORA-40470: JSON_VALUE evaluated to multiple values ）

```sql
SQL> select json_value(statistics, '$."Carriers"."Aircraft Types"[*].models' error on error)
  2  from airportdelays
  3  where id = 1032;

Error starting at line : 1 in command -
select json_value(statistics, '$."Carriers"."Aircraft Types"[*].models' error on error)
from airportdelays
where id = 1032
Error report -
ORA-40470: JSON_VALUE evaluated to multiple values
```

### json_mergepatch

特定のValueを更新

更新前のデータ

```sql
SQL> select json_value (
  2          Statistics, 
  3          '$.Carriers.Names' 
  4      ) "Airline Names" 
  5  from airportdelays a 
  6  where  a.id = 10;
                                                                                                                                                                                                                                               Airline Names 
____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ 
American Airlines Inc.,JetBlue Airways,Continental Air Lines Inc.,Delta Air Lines Inc.,AirTran Airways Corporation,America West Airlines Inc.,Northwest Airlines Inc.,ATA Airlines d/b/a ATA,United Air Lines Inc.,US Airways Inc.,Southwest Airlines Co.
```

`United Air Lines Inc.`を`Oracle Air Lines Inc.`に更新

```sql
update airportdelays
set statistics = json_mergepatch ( 
                statistics,
                    '{"Carriers" : {"Names" : "'||
                        (select replace(json_value (
                            Statistics, '$.Carriers.Names')
                            ,'United Air Lines Inc.,','Oracle Air Lines Inc.,')
                        from airportdelays a
                        where id = 10)||'"}
                    }'
                )
where id = 10;
```

更新後のデータ

```sql
SQL> select json_value (
  2          Statistics, 
  3          '$.Carriers.Names' 
  4      ) "Airline Names" 
  5  from airportdelays a 
  6* where  a.id = 10;
                                                                                                                                                                                                                                               Airline Names 
____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ 
American Airlines Inc.,JetBlue Airways,Continental Air Lines Inc.,Delta Air Lines Inc.,AirTran Airways Corporation,America West Airlines Inc.,Northwest Airlines Inc.,ATA Airlines d/b/a ATA,Oracle Air Lines Inc.,US Airways Inc.,Southwest Airlines Co.   
```

### JSON Query

The SQL/JSON function json_query selects and returns one or more values from JSON data and returns those values. You can thus use json_query to retrieve fragments of a JSON document in JSON.

```sql
SQL> select JSON_QUERY(statistics, '$."# of Delays"')
  2  from airportdelays
  3  where id = 1032;
                                                                                                     JSON_QUERY(STATISTICS,'$."#OFDELAYS"') 
___________________________________________________________________________________________________________________________________________ 
{"Carrier":485,"Late Aircraft":686,"National Aviation System":545,"Security":2,"Weather Codes":["SNW","RAIN","SUN","CLDY"],"Weather":50}    
```

json_valueでエラーとなったQueryをjson_queryで実行

```sql
SQL> select json_query(statistics, '$."Carriers"."Aircraft Types"[*].models' error on error)
  2  from airportdelays
  3  where id = 1032;

Error starting at line : 1 in command -
select json_query(statistics, '$."Carriers"."Aircraft Types"[*].models' error on error)
from airportdelays
where id = 1032
Error report -
ORA-40480: result cannot be returned without array wrapper
```

arrayデータのreturn値は、arrayでwrapする必要がある

```sql
SQL> select JSON_QUERY(statistics, '$."Carriers"."Aircraft Types"[*].models' with array wrapper error on error)
  2  from airportdelays
  3  where id = 1032;

   JSON_QUERY(STATISTICS,'$."CARRIERS"."AIRCRAFTTYPES"[*].MODELS'WITHARRAYWRAPPERERRORONERROR) 
______________________________________________________________________________________________ 
[["717","737","757","767","777","787"],["A320","A321","A330","A340","A350","A380"]]      
```

### JSON Table

The SQL/JSON function JSON_TABLE creates a relational view of JSON data. It maps the result of a JSON data evaluation into relational rows and columns.

time JSONをテーブルに変換

```sql
select a.name, v.*
from airportdelays a, json_table (
        a.time, '$' 
            columns (
                Label,
                Year,
                Month,
                "Month Name"
    ) )v
where a.id = 100;
```

10行データを取得

```sql
SQL> select a.name, v.*
  2  from airportdelays a, json_table (
  3          a.time, '$' 
  4              columns (
  5                  Label,
  6                  Year,
  7                  Month,
  8                  "Month Name"
  9      ) )v
 10  fetch first 10 rows only;
                                                                  NAME      LABEL    YEAR    MONTH    Month Name 
______________________________________________________________________ __________ _______ ________ _____________ 
New York, NY: John F. Kennedy International                            2003/06    2003    6        June          
Las Vegas, NV: McCarran International                                  2003/06    2003    6        June          
Los Angeles, CA: Los Angeles International                             2003/06    2003    6        June          
Atlanta, GA: Hartsfield-Jackson Atlanta International                  2003/06    2003    6        June          
Boston, MA: Logan International                                        2003/06    2003    6        June          
Baltimore, MD: Baltimore/Washington International Thurgood Marshall    2003/06    2003    6        June          
Charlotte, NC: Charlotte Douglas International                         2003/06    2003    6        June          
Washington, DC: Ronald Reagan Washington National                      2003/06    2003    6        June          
Denver, CO: Denver International                                       2003/06    2003    6        June          
Dallas/Fort Worth, TX: Dallas/Fort Worth International                 2003/06    2003    6        June          

10 rows selected. 
```

nestされたarrayからdataを取得

```sql
SQL> select t.*
  2  from airportdelays,
  3      json_table(Statistics, '$.Carriers."Aircraft Types"[*]'
  4              columns(
  5                  make varchar2(400) path '$.make',
  6                      nested path '$.models[*]'
  7                          columns(
  8                              models varchar2(400) path '$'
  9                              )
 10                  )    
 11              ) as t
 12  where id = 100;

     MAKE    MODELS 
_________ _________ 
Boeing    717       
Boeing    737       
Boeing    757       
Boeing    767       
Boeing    777       
Boeing    787       
Airbus    A320      
Airbus    A321      
Airbus    A330      
Airbus    A340      
Airbus    A350      
Airbus    A380      

12 rows selected. 
```

複数のjson_tableを用いてViewを作成

```sql
SQL> select a.airportcode, t.*, md.*
  2  from airportdelays a, 
  3      json_table (
  4              a.time, '$' 
  5                  columns (
  6                      Label,
  7                      "Month Name",
  8                      Year
  9                  ) 
 10          ) t,
 11          json_table (
 12              a.Statistics, '$."Minutes Delayed"' 
 13                  columns (
 14                      Carrier,
 15                      "National Aviation System",
 16                      "Late Aircraft",
 17                      Security,
 18                      Weather,
 19                      "Weather Codes" varchar2(200) format json path '$."Weather Codes"',
 20                      nested path '$."Weather Codes"'
 21                      columns (
 22                          code1 varchar2(100) path '$[0]',
 23                          code2 varchar2(100) path '$[1]',
 24                          code3 varchar2(100) path '$[2]',
 25                          code4 varchar2(100) path '$[3]'
 26                      ),
 27                      Total               
 28                  )
 29          ) md
 30  where a.id = 100;

   AIRPORTCODE      LABEL    Month Name    YEAR    CARRIER    National Aviation System    Late Aircraft    SECURITY    WEATHER                  Weather Codes    TOTAL    CODE1    CODE2    CODE3    CODE4 
______________ __________ _____________ _______ __________ ___________________________ ________________ ___________ __________ ______________________________ ________ ________ ________ ________ ________ 
JFK            2003/09    September     2003    13176      14391                       7732             93          996        ["SNW","RAIN","SUN","CLDY"]    36388    SNW      RAIN     SUN      CLDY     
```

### JSON Exists

The SQL/JSON condition json_exists lets you use a SQL/JSON path expression as a row filter, to select rows based on the content of JSON documents.

simpleな実行例

```sql
SQL> select a.id 
  2  from airportdelays a
  3  where json_exists(a.time, '$?(@.Year  == "2004" && @.Month == "6")')
  4* fetch first 5 rows only;

    ID 
______ 
   349 
   350 
   351 
   352 
   353 
```

複数のjson_existsを用いた実行例

```sql
SQL> select a.id, a.airportcode, a.Statistics."Minutes Delayed".Total 
  2  from airportdelays a
  3  where json_exists(a.time, '$?(@.Year  == "2004" && @.Month == "6")')
  4  and json_exists(a.Statistics, '$?(@."Minutes Delayed".Total > 500000)');  

    ID    AIRPORTCODE    Minutes Delayed 
______ ______________ __________________ 
   349 ATL            714316             
   355 DFW            520185             
   369 ORD            554653   
```

SQL/JSON explain plan: 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/7afb8029-d0b4-795d-c456-ea3a110c192d.png)

```sql
SQL> select a.id, a.airportcode, a.Statistics."Minutes Delayed".Total 
  2  from airportdelays a
  3  where a.time.Year  = 2004
  4  and a.time.Month = 6
  5  and a.Statistics."Minutes Delayed".Total > 500000; 

    ID    AIRPORTCODE    Minutes Delayed 
______ ______________ __________________ 
   349 ATL            714316             
   355 DFW            520185             
   369 ORD            554653 
```

Dot-Notation explain plan 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/5a5aab6f-d0d4-e9a3-fc06-84b5ae1ddc7a.png)

↑ 上記実行計画から確認できるとおり、SQL/JSON path expressionsのほうがはるかに効率がいいことがわかる

### json_object

テーブルのデータをJSONでoutput

```sql
select json_object ( * ) jdoc
from airportdelays;
```

### JSON transform(21c機能)

You can use the json_transform function to change input JSON data (or pieces of JSON data), by specifying one or more modifying operations that perform changes to the JSON data. Unlike json_mergepatch, json_transform can target the specific attributes you want to change.

Continuing our underhanded data alterations, lets work with the Minutes Delayed JSON section and see if we can make our numbers look a bit more favorable. Here is a sample of the JSON we will be working with:

```json
"Minutes Delayed": {
    "Carrier": 61606,
    "Late Aircraft": 68335,
    "National Aviation System": 118831,
    "Security": 518,
    "Total": 268764,
    "Weather": 19474
}
```

```sql
update airportdelays  
set statistics = json_transform (
                statistics, 
                replace '$."Minutes Delayed".Total' = '0'
            )
where id = 10;
```

# JSON Collection
## - Collectionの作成
### Database Actions/Development/JSONを用いたCollectionの作成

１．Database ActionsからDevelopment > JSONを選択

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/99b9cbfe-2e6a-fe52-eda4-9afbf3cd37fd.png)

２．JSON Workshieetの「Create Collection」をクリック

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/7936b7ea-4b41-3bb8-5575-079da21ddf18.png)

３．New Collection画面にCollection Nameを登録 > 「Create」をクリック

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/fda682fc-f410-eea5-a9df-c45724b0dae5.png)

### SODA for REST APIを用いたCollectionの作成

０．事前準備

・curlを実行できるターミナルを準備（OCI Cloud Shellも可能）
・ADBのRESTエンドポイントを確認: Database Actions > REST > Module > Create Module > Preview URL

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/eabe06e5-604c-a79d-4b4c-ec49f3bf2e8c.png)

１．curlを用いてCollectionを作成

```bash
$ curl -u "gary:PASSWORD" -i -X PUT https://(RESTエンドポイント)/ords/gary/soda/latest/airportdelayscollection

HTTP/1.1 201 Created
Date: Mon, 26 Apr 2021 15:53:46 GMT
Content-Length: 0
Connection: keep-alive
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Location: https://(RESTエンドポイント)/ords/gary/soda/latest/airportdelayscollection/
```

## Collectionへのデータロード

０．ロード用JSONファイルをロード

```bash
curl -o airportDelays.json https://objectstorage.us-ashburn-1.oraclecloud.com/p/rQEg_sXqFmCO8Swe5vVesi3-hmiCfLsY0Yc8mr_k0zdX2NWhhm34zrwhs0uvg8K-/n/c4u03/b/developer-library/o/airportDelays.json
```

１．データロード

```bash
$ curl -u "gary:PASSWORD" -i -X POST -d @airportDelays.json -H "Content-Type: application/json" "https://(RESTエンドポイント)/ords/gary/soda/latest/airportdelayscollection?action=insert"

HTTP/1.1 100 Continue

HTTP/1.1 200 OK
Date: Mon, 26 Apr 2021 16:46:50 GMT
Content-Type: application/json
Content-Length: 736197
Connection: keep-alive
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0

{"items":[{"id":"6E7AD2EE1E6B452D954AB4F87C1B1953","etag":"3C144144B1374205A6635923E92E2546","lastModified":"2021-04-26T16:46:30.428087","created":"2021-04-26T16:46:30.428087"},{"id":"5C8FD6CE99774C6D940B82F94D960E11","etag":"53C68394591B49E88A21D64BDE5D102F","lastModified":"2021-04-26T16:46:30.428087","created":"2021-04-26T16:46:30.428087"}........
```

## QBEを用いたデータアクセス

QBEを使ってフィルタしたデータを表示

### Database Actions/JSON

１．Database Actions/Development/JSONを起動

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/854d16a5-cc08-40c4-3724-52ba515e6606.png)

２．該当するCollectionを選択

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/a993d297-0e63-4dfd-8b56-7796d02e3dc2.png)

３．特定のFieldでフィルタ

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/c143fb14-b3b6-d906-5acc-9f77c724a80f.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/cedaccdf-128b-2d04-a2dd-39d170c4ca6e.png)

４．comparison operator でフィルタ

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/a4e3cd2d-0287-7939-d933-4351eac62100.png)

### SODA for REST API

IDを指定したQuery

```bash
$ curl -X GET -u "gary:WelCome123#123#"  "https://(RESTエンドポイント)/ords/gary/soda/latest/airportdelayscollection/962B7D9D663F4731AD6B9A139DC38157"
{"type":"http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.5","status":404,"title":"Key 962B7D9D663F4731AD6B9A139DC38157 not found in collection airportdelayscollection.","o:errorCode":"REST-02001"}
```

QBEを指定したQuery

```bash
$ curl -u "gary:WelCome123#123#"  "https://(RESTエンドポイント)/ords/gary/soda/latest/airportdelayscollection?action=query" -H "Content-Type: application/json" --data-binary \
'{"AirportCode": "DCA","Statistics.Flights.Cancelled": {"$gt": 400},"Time.Year": 2011}'

{"items":[{"id":"445F2E22649A475DA22A115CF1652837","etag":"D59DF9AF2F454E58BEFA9B8A2403CA37","lastModified":"2021-05-21T01:37:09.803870000Z","created":"2021-05-21T01:37:09.803870000Z","links":[{"rel":"self","href":"https://(RESTエンドポイント):443/ords/gary/soda/latest/airportdelayscollection/445F2E22649A475DA22A115CF1652837"}],"value":{"id":2644,"AirportCode":"DCA","Name":"Washington, DC: Ronald Reagan Washington National","Time":{"Label":"2011/01","Month":1,"Month Name":"January","Year":2011},"Statistics":{"# of Delays":{"Carrier":331,"Late Aircraft":314,"National Aviation System":348,"Security":4,"Weather Codes":["SNW","RAIN","SUN","CLDY"],"Weather":38},"Carriers":{"Aircraft Types":[{"make":"Boeing","models":["717","737","757","767","777","787"]},{"make":"Airbus","models":["A320","A321","A330","A340","A350","A380"]}],"Names":"American Airlines Inc.,Alaska Airlines Inc.,JetBlue Airways,Continental Air Lines Inc.,Delta Air Lines Inc.,Atlantic Southeast Airlines,Frontier Airlines Inc.,AirTran Airways Corporation,American Eagle Airlines Inc.,United Air Lines Inc.,US Airways Inc.,ExpressJet Airlines Inc.","Total":12},"Flights":{"Cancelled":550,"Delayed":1038,"Diverted":22,"On Time":5173,"Total":6783},"Minutes Delayed":{"Carrier":16829,"Late Aircraft":18648,"National Aviation System":12615,"Security":165,"Total":50961,"Weather Codes":["SNW","RAIN","SUN","CLDY"],"Weather":2704}}}}],"hasMore":false,"count":1}
```

QBE Operator $not

```json
{"Statistics.Flights.Cancelled": {"$gt": 400},
"AirportCode":{"$not" : {"$eq" : "DCA"}}
}
```

QBE Operator $and / $or

```json
{ "$and" : [
    {"Statistics.Flights.Cancelled": {"$gt": 400}},
    {"Time.Year": 2010 },
    {"AirportCode": "ORD"}
]}
```

```json
{"AirportCode": "DCA",
"Time.Year": 2012,
"$or" : [{"Time.Month": {"$eq": 6}}, {"Time.Month": {"$eq": 7}}]
}
```

## JSONデータ更新

### Database Actions/JSON - 直接編集

１．Database Actions/Development/JSONを起動

QBEで変更対象のDocumentを検索するためのフィルターを設定のうえ、Run Queryボタンを実行

編集ボタンをクリック

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/1e45e857-c3cd-2436-079a-4b17753ee6c3.png)

任意のアイテムを編集のうえ、右下にあるSaveボタンをクリック
（例） NameをPhonexからTokyoに変更

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/111b52c1-7d70-03ae-35da-9176c80bec77.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/10142391-980a-7322-27dd-450350f25ab5.png)

### SODA for REST

SODAのpatchを用いてデータを更新

```bash
curl -u "gary:WelCome123#123#"  "https://g5730e937dc2295-atp0611.adb.ap-tokyo-1.oraclecloudapps.com/ords/gary/soda/latest/airportdelayscollection?action=query" -H "Content-Type: application/json" --data-binary \
'{
    "$query": {"id": 1},
    "$patch": [
        {
            "op": "test",
            "path": "/Statistics/# of Delays/Carrier",
            "value": 1009
        },
        {
            "op": "replace",
            "path": "/Statistics/# of Delays/Carrier",
            "value": 1019
        }
    ]
}'
```

## Search Indexを用いた検索

### Search Indexの設定

以下のとおりSeach Indexを設定

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/d53e1f94-be78-fcb6-d3b9-f49f7df266f7.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/9e5cf072-f00e-5cb8-9883-ad450b321784.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/3b525c0d-5b24-c228-7bda-23f3c6c3d56e.png)

### $contains operatorを用いたQBE検索

```json
{"Name": {"$contains": "Tokyo"}}
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/457b20b6-f093-336c-9944-aa4f6bd04e36.png)

## Sort

```json
"$orderby": {
        "$fields": [
            {
                "path": "AirportCode",
                "datatype": "varchar2",
                "order": "asc"
            }
        ],
        "$scalarRequired": false,
        "$lax": false
    }
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/75de68df-ec7a-5554-1679-38348c95cf76.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/3bd73f4b-95d1-2ad9-edb0-ae8090adae20.png)


# 補足

## JSONファイルのロード

JSONファイルの表へのロードは、以下の方法で実施

- DBMS_CLOUD.COPY_COLLECTIONを用いたロード
- Database Actionsを用いたロード
- REST

JSONファイルロードに対応するJSONフォーマット

- line-delimited JSON
- array of JSON documents

```json:"line-delimited_JSON"
{ "name" : "apple", "count": 20 }
{ "name" : "orange", "count": 42 }
{ "name" : "pear", "count": 10 }
```

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

（補足）
以下のようにpretty-printed outputなフォーマットとなっているJSONファイルはそのままでは取り込めない

```json
{
        "id": "0001",
        "type": "donut",
        "name": "Cake",
        "ppu": 0.55,
        "batters":
                {
                        "batter":
                                [
                                        { "id": "1001", "type": "Regular" },
                                        { "id": "1002", "type": "Chocolate" },
                                        { "id": "1003", "type": "Blueberry" },
                                        { "id": "1004", "type": "Devil's Food" }
                                ]
                },
        "topping":
                [
                        { "id": "5001", "type": "None" },
                        { "id": "5002", "type": "Glazed" },
                        { "id": "5005", "type": "Sugar" },
                        { "id": "5007", "type": "Powdered Sugar" },
                        { "id": "5006", "type": "Chocolate with Sprinkles" },
                        { "id": "5003", "type": "Chocolate" },
                        { "id": "5004", "type": "Maple" }
                ]
}
```

上のようなファイルをロードする場合は、以下のようにフォーマットの変換が必要

```bash
cat test.json | jq -c
{"id":"0001","type":"donut","name":"Cake","ppu":0.55,"batters":{"batter":[{"id":"1001","type":"Regular"},{"id":"1002","type":"Chocolate"},{"id":"1003","type":"Blueberry"},{"id":"1004","type":"Devil's Food"}]},"topping":[{"id":"5001","type":"None"},{"id":"5002","type":"Glazed"},{"id":"5005","type":"Sugar"},{"id":"5007","type":"Powdered Sugar"},{"id":"5006","type":"Chocolate with Sprinkles"},{"id":"5003","type":"Chocolate"},{"id":"5004","type":"Maple"}]}
```

## QBE Comparison Operators

```
$all — whether an array field value contains all of a set of values
$between — whether a field value is between two string or number values (inclusive)
$eq — whether a field value is equal to a given scalar
$exists — whether a given field exists
$gt — whether a field value is greater than a given scalar value
$gte — whether a field value is greater than or equal to a given scalar
$hasSubstring — whether a string field value has a given substring (same as $instr)
$in — whether a field value is a member of a given set of scalar values
$instr — whether a string field value has a given substring (same as $hasSubstring)
$like — whether a field value matches a given SQL LIKE pattern
$lt — whether a field value is less than a given scalar value
$lte — whether a field value is less than or equal to a given scalar value
$ne — whether a field valueis different from a given scalar value
$nin — whether a field value is not a member of a given set of scalar values
$regex — whether a string field value matches a given regular expression
$startsWith — whether a string field value starts with a given substring
```

# 参考

## Documentation

- [Load JSON Documents with Autonomous Database](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/autonomous-json-load.html#GUID-6B9EBE09-FA5F-43D4-B3EA-DCCD19C77051)
- [Simple Oracle Document Access (SODA)](https://docs.oracle.com/en/database/oracle/simple-oracle-document-access/index.html)
- [19c JSON Developer's Guide](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/index.html)
 - [Query JSON Data](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/query-json-data.html#GUID-119E5069-77F2-45DC-B6F0-A1B312945590)
 - [Performance Tuning for JSON](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/performance-tuning-for-json-data.html#GUID-D3A3F17B-8372-43A7-83A3-E37C22E9011E)
 - [Partitioning JSON Data](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/partitioning-json-data.html#GUID-E1AA281B-483C-48EC-8E5C-5B460EB8EA57)
 - [Indexes for JSON Data](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/indexes-for-json-data.html#GUID-8A1B098E-D4FE-436E-A715-D8B465655C0D)
 - [JSON Query Rewrite To Use a Materialized View Over JSON_TABLE](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/json-query-rewrite-use-materialized-view-json_table.html#GUID-8B0922ED-C0D1-45BD-9588-B7719BE4ECF0)
 - [In-Memory JSON Data](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/in-memory-json-data.html#GUID-857A2E95-28AB-4314-B654-4CB05C50D9CF)
 - [Oracle Database JSON Restrictions](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/oracle-json-restrictions.html#GUID-1DB81125-54A7-4CB6-864B-78E0E7E407C9)
- [21c JSON Developer's Guide](https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/index.html)

## Blogs

- [SQL Developer PMのBlog](https://www.thatjeffsmith.com/)
- [Oracle Database 12cのJSON関連機能を試す](https://qiita.com/plusultra/items/377d810a6191ffe20045)
- [SQLをつかってJSONデータ生成（Oracle12c R2 新機能 SQL/JSON関数）](https://qiita.com/kenwatan/items/1fcd545001da4c2d9368)
- [Introducing Oracle Autonomous JSON Database for application developers](https://blogs.oracle.com/jsondb/autonomous-json-database)
- [SODA with partitioning](https://blogs.oracle.com/jsondb/soda-with-partitioning)