More than 3 years have passed since last update.

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

Last updated at 2020-09-05Posted at 2020-08-12

はじめに

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 データの文字列がそのまま入っています。

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

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

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

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

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

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

なお、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 が返ってきます。

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

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

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

Where

単純な絞り込み

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

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

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

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行分が絞り込まれています。

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

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

実行結果です。

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 になっています。無事に追加出来ています。

配列を新規追加

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 が追加されていますね。

配列に要素を追加

では、配列に要素を追加するためには、どうすればいいでしょうか。結論、直接的に要素を追加(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;

実行結果

Delete

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

オブジェクトを削除

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

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

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 になっています。

配列の一部を削除

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

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

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

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;

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;

実行結果

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;

実行結果

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;

SQL Monitor で実行計画を確認

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