Oracle RDBMSに格納したJSONデータをSQLから簡単にアクセスする方法
Oracle RDBMSのVARCHAR2/BLOB/CLOB型にJSONデータを格納する方法を以下で紹介しました。
Oracle RDBMS に JSONデータを格納する
Oracle12c R2(12.2)では新機能（データ・ガイド）により、JSONデータを解析し、Viewや仮想列としてSQLからアクセスすることが可能です。
準備
- JSONデータを格納する表の作成
- JSONデータが格納された列にJSON検索索引を作成
- (オプション)JSON検索索引の統計取得
- EXEC DBMS_STATS.GATHER_INDEX_STATS での取得
例
-
JSONデータを格納する表の作成
CREATE TABLE JSON (id number(4),doc CLOB CONSTRAINT valid_json CHECK (doc IS JSON)); -
JSONデータ格納列にJSON検索索引を作成
CREATE SEARCH INDEX JSON_IDX ON JSON (doc) for json; -
サンプルデータの挿入
index.sql
INSERT INTO JSON VALUES (1,
'{
"firstName": "Gerald",
"lastName": "Venzl",
"location": {
"country": "UK",
"city": "London",
"postCode": "EC2M"
}
}');
INSERT INTO JSON VALUES (2,
'{
"firstName": "Dominic",
"middleName": "Patrick",
"lastName": "Giles",
"location": {
"country": "UK",
"city": "London"
}
}');
commit;
View or 仮想列の作成
- VIEWの作成
- 仮想列として作成
- （オプション）仮想列の削除
例
- JSON 表のDOC列にJSONデータが格納されている場合で JSON_VIEW1というVIEWを作成
EXEC DBMS_JSON.create_view_on_path('JSON_VIEW1','JSON','DOC','$');
- 作成されたVIEWの確認
DESC JSON_VIEW1
| Name | Type |
|---|---|
| ID | NUMBER(4) |
| DOC$lastName | VARCHAR2(8) |
| DOC$city | VARCHAR2(8) |
| DOC$country | VARCHAR2(2) |
| DOC$postCode | VARCHAR2(4) |
| DOC$firstName | VARCHAR2(8) |
| DOC$middleName | VARCHAR2(8) |
- JSON 表のDOC列にJSONデータが格納されている場合で 仮想列を追加
EXEC DBMS_JSON.add_virtual_columns('JSON','DOC');
- 作成された仮想列の確認
DESC JSON
| Name | Type |
|---|---|
| ID | NUMBER(4) |
| DOC | CLOB |
| DOC$lastName | VARCHAR2(8) |
| DOC$city | VARCHAR2(8) |
| DOC$country | VARCHAR2(2) |
| DOC$postCode | VARCHAR2(4) |
| DOC$firstName | VARCHAR2(8) |
| DOC$middleName | VARCHAR2(8) |
- 仮想列の削除
EXEC DBMS_JSON.drop_virtual_columns('JSON','DOC');
作成された VIEW や 仮想列には SELECT文でアクセスが可能
SQL> select * from json_view1;
ID DOC$last DOC$city DO DOC$ DOC$firs DOC$midd
---------- -------- -------- -- ---- -------- --------
1 Venzl London UK EC2M Gerald
2 Giles London UK Dominic Patrick
データ・ガイドとは
JSONドキュメントの集合から生成するスキーマ情報
対象となるJSONデータ格納列にJSON検索索引があるとJSONデータの更新に追従して、データ・ガイドが更新されます。
注）JSON検索索引はJSON形式のチェック制約(is json)がある列に対して作成が可能です。
制約がない場合 DRG-10720: syntax only supported with constraint: IS JSON が発生
####### JSON検索索引の作成構文が12c R2(12.2)から新しくなっており、12.1にて作成した検索索引はドロップして、CREATE SEARCH INDEXにて再作成が推奨
####### JSON検索索引について統計を取得すると、最大値、最小値、出現率、スカラー値がJSON nullの数なども取得します。