近年Oracle DatabaseはJSONデータ型の対応が強化されてきました。Oracle Database 19cでは文字列型に対する IS JSON 構文や、JSON_OBJECT関数等が強化されました。

JSONデータ型

Oracle Database 21cではネイティブなデータ型としてのJSON型が提供されています。以下の例では json1 テーブルの val 列が JSON 型になっています。

SQL> CREATE TABLE json1(id NUMBER PRIMARY KEY, val JSON);

表が作成されました。

SQL> DESC json1
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 VAL                                                JSON

JSON型の列は {DBA|ALL|USER}_JSON_COLUMNSビューで検索できます。

SQL> SELECT * FROM USER_JSON_COLUMNS WHERE TABLE_NAME='JSON1';

TABLE_NAME           OBJEC COLUMN_NAME          FORMAT    DATA_TYPE
-------------------- ----- -------------------- --------- -------------
JSON1                TABLE VAL                  OSON      JSON

FORMAT列に出力される「OSON」は、ネイティブ・バイナリ形式のJSONデータ型を示し、更新や検索を高速化するためのOracle独自の内部フォーマットであることを示しています。

JSONデータの格納

SQL> INSERT INTO json1  VALUES (100,
               '{"PONumber"             : 1600,
                 "Reference"            : "ABULL-20140421",
                 "Requestor"            : "Alexis Bull",
                 "User"                 : "ABULL",
                 "CostCenter"           : "A50",
                 "Special Instructions" : null,
                 "AllowPartialShipment" : true}');

1行が作成されました。

SQL> COMMIT;

コミットが完了しました。

OSONフォーマット

OSONデータ型はOracle Database 21cの独自フォーマットです。入力されたデータは再フォーマットされ、不要なスペース等は削除されます。

SQL> INSERT INTO json1 VALUES (200, '{"PONumber"   :   2000     }');

1行が作成されました。

SQL> COMMIT;

コミットが完了しました。

SQL> SELECT val FROM json1 WHERE id=200;

VAL
------------------
{"PONumber":2000}

OSONフォーマットは古いバージョンのクライアントから検索すると正しく表示できません。下記はOracle Database 21cで格納したJSONデータをOracle Database 19c Clientを使って検索した例です。

SQL> SHOW sqlpluscompatibility
sqlpluscompatibility 19.0.0
SQL>
SQL> SELECT * FROM json1;

        ID　VAL
----------　--------------------------------------------------------------------------------
       100　7B22504F4E756D626572223A313630302C225265666572656E6365223A224142554C4C2D32303134
30343231222C22526571756573746F72223A22416C657869732042756C6C222C2255736572223A22

VARCHAR2型に上記と同じデータを格納したところ、VARCHAR2型の方がデータ長は短くなりました。JSON型のデータには検索や更新のための情報が付加されているのだと思われます。

SQL> CREATE TABLE text1 (id NUMBER PRIMARY KEY, val VARCHAR2(2000));

表が作成されました。

SQL> INSERT INTO text1 SELECT * FROM json1;

1行が作成されました。

SQL> COMMIT;

コミットが完了しました。

SQL> SELECT length(val) FROM json1;

LENGTH(VAL)
-----------
        186

SQL> SELECT length(val) FROM text1;

LENGTH(VAL)
-----------
        162