[Oracle Cloud] Oracle Database で JSON データを扱う

# はじめに

Oracle Database では、JSONデータを管理できます。トランザクション処理、Index 付けなど、Oracle Database で提供されている機能群と合わせて、JSON データを管理できます。

この記事では、JSON データの INSERT 方法や、基本的なデータの取得方法を紹介していきます。「JSON開発者ガイド」という Document がありますが、網羅的に記載されているために、かなり難解に書かれていると感じています。Quick Start 的な立ち位置で、シンプルなJSONデータの扱い方を紹介します。

JSON 開発者ガイド
https://docs.oracle.com/cd/F19136_01/adjsn/preface.html#GUID-B52F36FB-0912-46E3-8A9B-C0728E7016D1



# アクセス方法

Oracle Database で JSON データを取り扱う時に、大きく分けて 2種類の API が利用できます。

- SQL
- SODA

今回の記事では、SQL を使った JSON の扱い方を紹介していきます。SODA とは、開発者向けに作られている JSON 用 API ファミリーです。SQL のことを良くしらなくても、Oracle Database 上で JSON を扱えるものとなっているようです。興味のある方は、下のURLをどうぞ。Java, Node.js, Python, C などの Driver が提供されております。そのうち、何かしらの Driver を触ってみたいと思っています。

SODA
https://www.oracle.com/database/technologies/appdev/json.html



# 使用可能な型

まず、Oracle Database 上で JSON を扱うためには、次の3種類のデータ型が利用できます。

- VARCHAR2
- BLOB
- CLOB

使い分け指針は Document に書かれています。4000バイト以下に収まることが確定的な場合は、`VARCHAR2(4000)` で定義するのが良いそうです。なので、今回の記事では、`VARCHAR2(4000)` のデータ定義で JSON を扱っていきます。

JSONデータの格納および管理の概要
https://docs.oracle.com/cd/F19136_01/adjsn/overview-of-storage-and-management-of-JSON-data.html#GUID-26AB85D2-3277-451B-BFAA-9DD45355FCC7



# Create Table

まず、初めに JSON 列を扱うためのテーブルを作っていきます。`VARCHAR2(4000)` で、`JDATA` という名前の JSON 用列を定義しています。
重要なものは、`CONSTRAINT json_menu CHECK (JDATA IS JSON)` です。忘れないようにしましょう。

- ID : 数値データを格納する列
- JDATA : JSON データ を格納する列

```
CREATE TABLE menu (
    ID NUMBER(10) NOT NULL,
    JDATA VARCHAR2(4000),
    CONSTRAINT pk_menu PRIMARY KEY (ID),
    CONSTRAINT json_menu CHECK (JDATA IS JSON)
);
COMMIT;
```

上記の通り、1テーブル内に、JSON を扱うための列と、通常の列を混在出来ます。



# Insert

それでは実際に JSON データを含む Row を Insert していきます。次のコマンドがそのままコピペできます。`JDATA` に対して、普通の JSON 文字列をそのまま Insert 出来ます。(テストデータのネーミングは、MySQL 徹底入門という本からお借りしました。ありがとうございます。)

```
INSERT INTO menu (ID,JDATA) VALUES(
1,
'{
  "name" : "赤島海鮮定食",
  "dept_name" : "食事作る部署",
  "price" : 1700
}'
);

INSERT INTO menu (ID,JDATA) VALUES(
2,
'{
  "name" : "はっかま弁当",
  "dept_name" : "食事作る部署",
  "price" : 500
}'
);

INSERT INTO menu (ID,JDATA) VALUES(
3,
'{
  "name" : "L国生ロール",
  "dept_name" : "スイーツ作る部署",
  "price" : 1000,
  "material" : ["egg", "sugar"]
}'
);

INSERT INTO menu (ID,JDATA) VALUES(
4,
'{
  "name" : "蜂楽まんぢゅう",
  "dept_name" : "スイーツ作る部署",
  "price" : 90
}'
);

INSERT INTO menu (ID,JDATA) VALUES(
5,
'{
  "name" : "こっこダックワーズ",
  "dept_name" : "スイーツ作る部署",
  "price" : 600
}'
);

INSERT INTO menu (ID,JDATA) VALUES(
6,
'{
  "name" : "坂元メロン",
  "dept_name" : "スイーツ作る部署",
  "price" : 5000
}'
);

INSERT INTO menu (ID,JDATA) VALUES(
7,
'{
  "name" : "うだ豚まん",
  "dept_name" : "食事作る部署",
  "price" : 300
}'
);

INSERT INTO menu (ID,JDATA) VALUES(
8,
'{
  "name" : "4ライブとんかつ",
  "dept_name" : "食事作る部署",
  "price" : 600
}'
);

INSERT INTO menu (ID,JDATA) VALUES(
9,
'{
  "name" : "坂元イチゴ",
  "dept_name" : "スイーツ作る部署",
  "price" : 2000
}'
);

COMMIT;
```



memo : 全DELETE用コマンド

```
DELETE FROM menu;
COMMIT;
```





# Select

## オブジェクトを取得

JSON のオブジェクトを取得する方法を紹介します。まずは、何も考えず全てを取得してみましょう。

```
SELECT * FROM menu;
```



実行結果はこんなかんじです。`JDATA` 列には、JSON データの文字列がそのまま入っています。

![1597236561133.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/d5ac5545-be9a-4583-efd1-f08ae32bdd62.png)



JSONデータの中から、特定の key を持つデータをドットで区切って取得できます。
menu という Table に `m` と別名をつけて、`m.jdata.price` とアクセスできます。便利ですね。別名を付けないとエラーとなるので、ご注意ください。

```
SELECT m.id, m.jdata.price FROM menu m;
```



実行結果です。通常列の ID と、JSON データの中の一部の Price が、同じ形で取得できています。

![1597147916102.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/b556345d-9cef-fe4e-d68e-eff252daf05e.png)



もちろん、JSON から複数データを取得できます。Name と Price を指定してみましょう。

```
SELECT m.id, m.jdata.name, m.jdata.price FROM menu m;
```



実行結果です。ID, Name, Price が取得できています。

![1597149267043.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/5766d0b3-6002-aa62-ec41-59d86432233a.png)



なお、JSON 中から取得したデータはすべて文字列として取得されています。数値として変換したい場合は、TO_NUMBER といった SQL 関数を利用出来ます。

```
SELECT m.id, m.jdata.name, TO_NUMBER(m.jdata.price) FROM menu m;
```



## 配列を取得

JSON の中で配列として定義したデータも取得できます。Material 配列を取得します。

```
SELECT m.id, m.jdata.name, m.jdata.price, m.jdata.material FROM menu m;
```



実行結果です。一番右側の `MATERIAL` というのがそれです。egg と sugar が入っていますね。ちなみに、material が存在していない行は、NULL が返ってきます。

![1597236614208.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/32d7c01e-b1d2-becc-bc8e-fa2cb1b30d1d.png)



Material 配列の中から、1番目の要素を取得できます。`[0] ` の指定が出来ます。簡単で便利ですね。

```
SELECT m.id, m.jdata.name, m.jdata.price, m.jdata.material[0] FROM menu m;
```



実行結果です。期待した通り、egg のみ取得できています。

![1597236630848.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/987ef0c2-2fae-c2ea-8773-e08d49367716.png)



# Where

## 単純な絞り込み

JSON データに含まれる値から、WHERE で絞り込みが出来ます。坂元メロンという名前のデータのみ取得するのがこちらです。`json_exists` で色々指定しています。

```
SELECT m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.name == "坂元メロン")');
```



実行結果です。坂元メロンのみ取得できています。

![1597149826832.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/5c1614e6-961e-10c0-cb01-9e2cacc257d0.png)



`json_exists(m.jdata, '$?(@.name == "坂元メロン")')` と書かれている部分は、`m.jdata` 列に有るJSONデータを受け取って、name と 坂本メロンを比較しています。説明が難しいので、次の Document の例を見てみるとよいでしょう。わかりやすいはずです。

***例15-2 JSON_EXISTS: パス式のフィルタ内のカレント項目と有効範囲***
https://docs.oracle.com/cd/F19136_01/adjsn/condition-JSON_EXISTS.html#GUID-8A0043D5-95F8-4918-9126-F86FB0E203F0



## LIKE 検索

イコールの比較だけではなく、LIKE のようなあいまい比較もできます。`==` の代わりに、`like_regex` で比較できます。

```
SELECT m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.name like_regex "坂元.*")');
```



実行結果です。坂元 という文字が含まれている、2行分が絞り込まれています。

![1597217361569.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/4005774f-6f46-46c9-faea-bc06228766b0.png)



前方一致だけではなく、途中の一致もできます。

```
SELECT m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.name like_regex ".*まん.*")');
```



実行結果です。

![1597217590619.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/e3c8a427-1db0-c74e-f8e9-845852193a2f.png)



like_regex 以外に、比較で使えるものがこちらに書かれています。興味があればどうぞ。
https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/json-path-expressions.html#GUID-AEBAD813-99AB-418A-93AB-F96BC1658618



# Update

JSON の中の一部のデータを更新が出来ます。RDBMS によって特色がかなり出る部分です。Oracle Database では、json_mergepatch を使って更新できます。json_mergepatch はちょっと考え方が特殊だと思います。Linux の Patch コマンドと似たような概念です。元々使っていた JSON データに対して、差分のJSONデータを用意して適用する、という考え方です。一個 Sample を書きます。

元々使っていた JSON データ

```
'{"User":"ABULL", "PONumber":1600}'
```



json_mergepatch で用意する 差分のJSON データ

```
'{"PONumber":99999}'
```



適用後に出来上がる JSON 
PONumber が差分のJSONデータによって上書きされた形ですね。

```
'{"User":"ABULL", "PONumber":99999}'
```



こういった考え方で、json_mergepatch を扱っていきます。ちょっと癖があるかもしれませんが、慣れれば大丈夫です。慣れましょう。

Document はこちら
https://docs.oracle.com/cd/F19136_01/adjsn/updating-json-document-json-merge-patch.html#GUID-31F88F28-3D92-489B-9CCD-BD1931B91F1F



## オブジェクトを追加

それでは実際に、JSON の中にオブジェクトを追加してみます。ID が 6 の Row に有る JDATA に対して、`"popular" : true` の JSON オブジェクトを追加します。json_mergepatch の第一引数に、`jdata` を渡しています。これが、更新したい元々使っていたJSONデータです。これに対して、`"popular" : true` の差分データを適用して、出来上がったデータを、UPDATE 文で `jdata` に更新しています。

```
UPDATE menu SET jdata = json_mergepatch(jdata, 
'{
  "popular" : true
}') WHERE ID IN (6);

COMMIT;
```



SELECT  で確認しましょう。

```
SELECT m.id, m.jdata.name, m.jdata.popular FROM menu m;
```



実行結果です。ID が 6 の行だけ、POPULAR が true になっています。無事に追加出来ています。

![1597236786570.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/390b17fe-4d58-b143-36d1-52172c78f294.png)



## 配列を新規追加

JSON に配列データを追加も出来ます。あらたに、category 配列を、json_mergepatch で追加しましょう。

```
UPDATE menu SET jdata = json_mergepatch(jdata, 
'{
  "category" : ["スイーツ", "洋菓子"]
}') WHERE ID IN (3, 5);

UPDATE menu SET jdata = json_mergepatch(jdata, 
'{
  "category" : ["スイーツ", "和菓子"]
}') WHERE ID IN (4);

UPDATE menu SET jdata = json_mergepatch(jdata, 
'{
  "category" : ["スイーツ", "果物"]
}') WHERE ID IN (6);

COMMIT;
```



SELECTで確認です。

```
SELECT m.id, m.jdata.name, m.jdata.price, m.jdata.category FROM menu m;
```



実行結果はこちらです。正常に、CATEGORY が追加されていますね。

![1597236823232.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/2f542ed9-c727-ec70-6269-32492937dc75.png)



## 配列に要素を追加

では、配列に要素を追加するためには、どうすればいいでしょうか。結論、直接的に要素を追加(append) することは出来ません。要素を追加した配列を用意して、配列全体を上書きする必要があります。(もし、良い方法があれば教えて下さい。SODA API にいい方法があるのかも・・・？)

やってみましょう。蜂楽まんぢゅう の CATEGORY `["スイーツ", "和菓子"]` を、`["スイーツ", "和菓子", "はちみつ"]` と更新します。次のように、json_mergepatch で用意する差分データは、元々あるデータに`はちみつ`を含めた、全体の配列を渡す必要があります。

```
UPDATE menu SET jdata = json_mergepatch(jdata, 
'{
  "category" : ["スイーツ", "和菓子", "はちみつ"]
}') WHERE ID IN (4);

COMMIT;
```



SELECT で確認です。

```
SELECT m.id, m.jdata.name, m.jdata.price, m.jdata.category FROM menu m;
```



実行結果

![1597241167744.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/35f0eead-1635-41ea-a07c-21fbe63672fe.png)



# Delete

次に、JSON の一部データを削除していきます。これも、Update と同様に、json_mergepatch を使っていきます。



## オブジェクトを削除

JSON の一部オブジェクトを削除します。まず、削除前のデータを確認します。

```
SELECT m.id, m.jdata.name, m.jdata.price, m.jdata.material FROM menu m;
```

![1597228628128.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/96413822-efd6-b583-b481-0ccb3549ef4f.png)



ID 3 の行から、material を削除しましょう。null を渡して、json_mergepatch を使って UPDATE すればよいです。

```
UPDATE menu SET jdata = json_mergepatch(jdata, 
'{
  "material" : null
}') WHERE ID IN (3);

COMMIT;
```



Select で確認します。

```
SELECT m.id, m.jdata.name, m.jdata.price, m.jdata.material FROM menu m;
```



ID 3 の行から、MATERIAL が削除されて Null になっています。

![1597228677877.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/f865472c-0810-7fb1-0157-f8e31022396c.png)



## 配列の一部を削除

JSON 配列の一部データも削除は出来ますが、Update と同様に、削除後の配列データ全てを json_mergepatch で渡す必要があります。

まず、削除前のデータの確認です。ID 3 の Material には、`["egg","sugar"]` が入っています。

```
SELECT m.id, m.jdata.name, m.jdata.price, m.jdata.material FROM menu m;
```

![1597228628128.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/e3df1463-32ab-07e9-b1d3-183653d39b99.png)



sugar を削除した後の配列全体を渡しています。

```
UPDATE menu SET jdata = json_mergepatch(jdata, 
'{
  "material" : ["egg"]
}') WHERE ID IN (3);

COMMIT;
```



確認です。無事に削除されています。

```
SELECT m.id, m.jdata.name, m.jdata.price, m.jdata.material FROM menu m;
```

![1597236887863.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/c3524902-9c22-e2a6-0ff1-7d048e9f81a8.png)



# ORDER BY

JSON に限ったはなしではなく、通常の SQL 文と同様に、ORDER BY が利用できます。文字列型なので、数値型に変更したうえで、ORDER BY をしています。

```
SELECT m.id, m.jdata.name, m.jdata.price FROM menu m ORDER BY TO_NUMBER(price) DESC;
```



実行結果

![1597230735670.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/9592a18b-d535-2340-97cf-744c1efe3f5a.png)



# GROUP BY

同様に GROUP BY も利用できます。JSON データの中に有る、DEPT_NAME で GROUP BY しています。

```
SELECT count(m.id), m.jdata.dept_name, avg(m.jdata.price) FROM menu m GROUP BY m.jdata.dept_name;
```



実行結果

![1597231154190.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/1ef84c9b-e432-2567-8bbe-caec1513a4ad.png)



# OFFSET + LIMIT (のようなもの)

OFFSET, LIMIT のようなことも通常の SQL 文と同様に可能です。1 行を OFFSET で読み飛ばして、3行分 FETCH しています。

```
SELECT m.id, m.jdata.name, m.jdata.price FROM menu m ORDER BY ID OFFSET 1 ROWS FETCH FIRST 3 ROWS ONLY;
```


![1597236495849.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/57e5ae71-fa3c-68f9-5886-e2990eec5bb3.png)





# SQL Monitor で実行計画を確認

`/*+ MONITOR */` と付けることで、SQL Monitoring にモニタリングをさせることも可能です。Oracle Cloud の DBCS では、Enterprise Edition 以上が必要です。SQL Monitoring は、実行時間が5秒以上 or パラレルクエリー のSQL文がモニタリングが対象となっています。JSON データの単純な SELECT ではモニタリング対象にならないため、MONITOR というヒント句をつけています。

```
SELECT /*+ MONITOR */ * FROM menu;
```



JSON データを取得可能

![1597236943898.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/98aa165f-4717-17ce-9fd3-f48d9679d478.png)



JSON を使っていても、通常のSQLと同様に、TABLE ACCESS FULL の実行計画となっています。

![1597237034339.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/9bde64ef-c97b-4705-e12c-a705c32185eb.png)



# 要検証 : Index

今回は、Index を使った検証は実施していません。JSON データの中の値でも、Index が利用できます。そのうち検証してみたいと思います。

https://docs.oracle.com/cd/F19136_01/adjsn/indexes-for-json-data.html#GUID-8A1B098E-D4FE-436E-A715-D8B465655C0D


# 検証で使った環境

- OCI DBCS Enterprise Edition 19c


# 参考URL

JSON開発者ガイド
https://docs.oracle.com/cd/F19136_01/adjsn/index.html

SODA Document
https://docs.oracle.com/en/database/oracle/simple-oracle-document-access/index.html

SODAについて
https://orablogs-jp.blogspot.com/2018/10/node-oracledb-30-introduces-soda.html

JSON を SQL で扱う
http://atudc.blogspot.com/2018/09/rdbmsoraclesql-servermysqljson.html

JMESPATH TutorialをなぞってOracle Database 12c JSON機能を使ってみた
https://cosol.jp/techdb/2017/12/oracle_database_12c_json_with_jmespath_tutorial/