Oracle RDBMS に JSONデータを格納する

JSON within the Oracle Database

JSON形式のデータをOracle Database で扱うサンプル

• Oracle Database 12c (12.1.0.2)では JSON形式のデータを CLOB, BLOB,VARCHAR2 のデータ型に格納可能 (格納サイズやアプリケーションによって選択）
• JSON形式のチェック制約が追加

例）JSON格納表の作成
CREATE TABLE JSON (doc CLOB CONSTRAINT valid_json CHECK (doc IS JSON));

JSON形式データの挿入
- Insert文で格納可能
INSERT INTO JSON VALUES (
'{
"firstName": "Gerald",
"lastName": "Venzl",
"location": {
"country": "UK",
"city": "London",
"postCode": "EC2M"
}
}');
commit;

• チェック制約違反の例 INSERT INTO JSON VALUES ('Invalid Json'); INSERT INTO JSON VALUES ('Invalid Json') * ERROR at line 1: ORA-02290: check constraint (TEST.VALID_JSON) violated Retrieving JSON data

JSON形式で格納されたデータの検索

... の形式で指定が可能
（大文字/小文字の区別があるので注意）

• 検索した例） SELECT j.doc.firstName, j.doc.location.city FROM JSON j;

FIRSTNAME LOCATION

---

Gerald London

• 表示列名の制御は２つのパターンがある a) JSON_TABLE関数を使用 b) AS 句でエイリアスを指定 例） SELECT j.doc.firstName as "First Name", j.doc.location.city as "City" FROM JSON j;

別の例として JSONデータを格納
（前回とちがって postcode 属性は なしで、middleName属性を持っている）
INSERT INTO JSON VALUES (
'{
"firstName": "Dominic",
"middleName": "Patrick",
"lastName": "Giles",
"location": {
"country": "UK",
"city": "London"
}
}');
commit;

• middleName属性の検索例 SELECT j.doc.firstName, j.doc.middleName, j.doc.lastName FROM JSON j;

FIRSTNAME MIDDLENAME LASTNAME

---

Gerald Venzl
Dominic Patrick Giles

１行目のデータにはmiddleName属性値はないが検索はエラーとならずに
NULLとして返してくれる
属性が追加/削除されてもデータの変更なしでアプリ側で必要なデータのみ
アクセスすることが可能

SELECT j.doc.lastName,
j.doc.location.city as "CITY",
j.doc.location.postCode as "POST CODE"
FROM JSON j;

LASTNAME CITY POST CODE

---

Venzl London EC2M
Giles London

https://blogs.oracle.com/developer/entry/series_json_within_the_oracle