LoginSignup
9
4

More than 3 years have passed since last update.

@sugimount(sugi mount)

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

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

1597236561133.png

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

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

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

1597147916102.png

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

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

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

1597149267043.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

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

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

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

1597236630848.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

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

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

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

実行結果です。

1597217590619.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

配列を新規追加

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

配列に要素を追加

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

Delete

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

オブジェクトを削除

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

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

1597228628128.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

配列の一部を削除

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

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

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

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

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

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

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

1597237034339.png

要検証 : Index

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

検証で使った環境

  • 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/

9
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
4