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