Oracle Database 12cのJSON関連機能を試す

JavaScript Object Notation (JSON)は比較的簡単な表記と柔軟性を持つデータ表現であり、近年はJSONデータをデータベースに格納する要求が増えました。
単一の文字列内に複数データを含めることや、データ型に対する厳密性を欠くJSONをRDBMSに格納することには議論の余地はありますが、多くのRDBMSがJSONに対するサポートを増やしています。ここではOracle Database 12cのJSON対応機能について検証します。

マニュアル

Oracle Database 12c Release 1のJSONに関するマニュアルは、「Oracle XML DB開発者ガイド 」に記載されています。
Oracle Database 12c Release 2では「Oracle® Database JSON開発者ガイド 」として独立しました。

データ型

Oracle DatbaseにはJSON専用のデータ型はありません。VARCHAR2型やCLOB型等、テキスト型を使用してJSONデータを格納します。
ただしJSONデータは常にUnicodeが使用されるため、JSONデータを扱う場合はデータベースの文字コードをUnicode系に指定した方が良いと思います。

制約

CHECK制約にIS JSONを指定すると、JSONとして認められないデータの格納を禁止することができます。

SQL> CREATE TABLE json1(id NUMBER, val VARCHAR2(2000) CONSTRAINT valchk CHECK(val IS JSON));

表が作成されました。

SQL> INSERT INTO json1 VALUES (100, '{"PONumber" : 1600, "Reference" : "ABULL-20140421"}');

1行が作成されました。

SQL> INSERT INTO json1 VALUES (100, '"PONumber" : 1600, "Reference" : "ABULL-20140421"');
INSERT INTO json1 VALUES (100, '"PONumber" : 1600, "Reference" : "ABULL-20140421"')
*
行1でエラーが発生しました。:
ORA-02290: チェック制約(SCOTT.VALCHK)に違反しました

WHERE句

IS JSON句はWHERE句でも指定することができます。

SQL> SELECT id FROM json1 WHERE val IS JSON;

        ID
----------
       100

検索

JSONデータ内を検索する場合、専用の関数を用いる方法と、ドット表記法によるアクセスがあります。ドット表記法は、「テーブル別名.列名.JSONフィールド名.JSONフィールド名...」の形式でJSONデータ内のデータを取得します。
FROM句に指定するテーブル名には別名が必須で、列名の前に指定します。この指定はJSONデータの一部に対するファンクション・インデックスを作成する場合にも利用できます。

SQL> SELECT j.val.PONumber FROM json1 j;

PONUMBER
--------------------------------------------------------------------------------
1600

配列要素の場合は「要素名[番号]」で指定できます。番号は0からはじまります。また「要素名[*]」を指定することで、
すべての要素を指定することができます。

実行計画を確認すると、特殊な関数を使用せずにデータの取得が行われていることがわかります。

SQL> SELECT j.val.PONumber FROM json1 j;

PONUMBER
--------------------------------------------------------------------------------
1600


実行計画
----------------------------------------------------------
Plan hash value: 2239912331

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |  1002 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| JSON1 |     1 |  1002 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        567  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

関数

Oracle Database 12cにはJSONデータを扱う以下の関数が提供されています。

json_value

　json_value関数はJSONデータ内のスカラー値を検索して返します。

SQL> SELECT json_value(val, '$.PONumber' RETURNING NUMBER NULL ON ERROR) PONumber FROM json1;

  PONUMBER
----------
      1600

上記の例はval列からPoNumber要素を検索しています。戻り値のデータ型はNUMBERであり、変換エラーが発生した場合はNULLを返します。

json_query

json_query関数はJSONデータの1つ以上の指定されたJSON値を確認し、文字列の値を戻します。

SQL> SELECT json_query(val, '$.PONumber' WITH WRAPPER) PONumber FROM json1;

PONUMBER
--------------------------------------------------------------------------------
[1600]

上記例ではPONumber要素を返しています。WITH WRAPPERを指定すると配列ラッパーで囲まれます。

json_table

json_table関数はJSONデータのリレーショナル・ビューを作成します。下記以外に様々な書式で記述できます。

SQL> SELECT j.PONumber FROM json1, json_table(val, '$' 
	COLUMNS (
		PONumber NUMBER PATH '$.PONumber'
	)) j;

  PONUMBER
----------
      1600

json_exists

json_exists関数は、指定された要素を持つ条件を指定できます。下記の例ではmiddle要素を持つレコードのみ検索しています。

SQL> CREATE TABLE json2 (val VARCHAR2(100));

表が作成されました。

SQL> INSERT INTO json2 VALUES ('[{first:"John"}, {middle:"Mark"}, {last:"Smith"}]');

1行が作成されました。

SQL> INSERT INTO json2 VALUES ('[{first:"Mary"}, {last:"Jones"}]');

1行が作成されました。

SQL> INSERT INTO json2 VALUES ('[{first:"Jeff"}, {last:"Williams"}]');

1行が作成されました。

SQL> SELECT * FROM json2 WHERE json_exists(val, '$.middle');

VAL
--------------------------------------------------------------------------------
[{first:"John"}, {middle:"Mark"}, {last:"Smith"}]

json_textcontains

json_textcontains関数は、指定されたテキストを含むかを検査することができます。この関数を使用するためには
Oracle TextのJSON検索索引を作成する必要があります。

SQL> CREATE INDEX idx1_json1 ON json1(val)
    INDEXTYPE IS CTXSYS.CONTEXT
    PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)') ;

索引が作成されました。

SQL> SELECT * FROM json1 WHERE json_textcontains(val, '$', 'AB');