はじめに

Oracle Database 23cから、外部表でJSON型の列が使用できるようになったので試してみました。

1.JSONデータの準備

こちらの記事と同じ方法で、v$parameterビューからJSONデータを含むテキストファイル json_data.txt を作成し、/tmpに配置しました。

json_data.txt のサイズを確認します。

[oracle@basedb23c ~]$ ls -l /tmp/json_data.txt 
-rwxr-xr-x 1 oracle oinstall 70861 Oct 17 14:00 /tmp/json_data.txt

json_data.txt の内容を確認します

[oracle@basedb23c ~]$ tail /tmp/json_data.txt 
{"name":"uniform_log_timestamp_format","type":1,"value":"TRUE","isDefault":"TRUE","description":"use uniform timestamp formats vs pre-12.2 formats"}
{"name":"use_dedicated_broker","type":2,"value":"NONE","isDefault":"TRUE","description":"Use dedicated connection broker"}
{"name":"use_large_pages","type":2,"value":"only","isDefault":"FALSE","description":"Use large pages if available (TRUE/FALSE/ONLY)"}
{"name":"user_dump_dest","type":2,"value":"/u01/app/oracle/product/23.0.0.0/dbhome_1/rdbms/log","isDefault":"TRUE","description":"User process dump directory"}
{"name":"wallet_root","type":2,"value":"/opt/oracle/dcs/commonstore/wallets/db23c_476_iad","isDefault":"FALSE","description":"wallet root instance initialization parameter"}
{"name":"workarea_size_policy","type":2,"value":"AUTO","isDefault":"TRUE","description":"policy used to size SQL working areas (MANUAL/AUTO)"}
{"name":"xml_client_side_decoding","type":2,"value":"true","isDefault":"TRUE","description":"enable/disable xml client-side decoding"}
{"name":"xml_db_events","type":2,"value":"enable","isDefault":"TRUE","description":"are XML DB events enabled"}
{"name":"xml_handling_of_invalid_chars","type":2,"value":"raise_error","isDefault":"TRUE","description":"Handle invalid chars during xmlelement"}
{"name":"xml_params","type":2,"value":null,"isDefault":"TRUE","description":"Parameters to alter xml behavior"}
[oracle@basedb23c ~]$

json_data.txt の行数を確認します。

[oracle@basedb23c ~]$ wc -l /tmp/json_data.txt 
535 /tmp/json_data.txt

535行あることがわかりました。

2. ディレクトリ・オブジェクトの作成

/tmp をポイントするディレクトリ・オブジェクト json_data_dir を作成します。

SQL> CREATE OR REPLACE DIRECTORY json_data_dir AS '/tmp';

Directory JSON_DATA_DIR created.

SQL>

ディレクトリ・オブジェクト json_data_dir が作成されました。

3. JSON型の列を含む外部表の作成

/tmp/json_data.txt を元にしたJSON型の列を含む外部表 json_data_ext を作成します。

SQL> CREATE TABLE json_data_ext 
  2      (json_data      JSON)
  3  ORGANIZATION EXTERNAL(
  4      TYPE ORACLE_LOADER
  5      DEFAULT DIRECTORY json_data_dir
  6      ACCESS PARAMETERS (
  7          RECORDS DELIMITED BY 0x'0A'
  8          FIELDS (json_data CHAR(4000))
  9     )
 10      LOCATION ('json_data.txt')
 11* );

Table JSON_DATA_EXT created.

SQL>

外部表 json_data_ext が作成されました。

4. 外部表に対するクエリの実行

外部表json_data_extのレコード数を確認します。

SQL> SELECT COUNT(*) FROM json_data_ext;

   COUNT(*) 
___________ 
        535 

SQL>

json_data.txt の行数と同じ535であることがわかりました。

ドット表記法を使用して、プロパティnameがsessionsであるJSONドキュメントを外部表 json_data_ext から抽出してみます。

SQL> SELECT JSON_SERIALIZE(json_data PRETTY) json_data
  2  FROM json_data_ext j
  3* WHERE j.json_data.name = 'sessions';

JSON_DATA                                                                                                                           
___________________________________________________________________________________________________________________________________ 
{
  "name" : "sessions",
  "type" : 3,
  "value" : "624",
  "isDefault" : "TRUE",
  "description" : "user and system sessions"
}    

SQL>

プロパティnameがsessionsであるJSONドキュメントを抽出されました。

外部表でJSON型の列が使用できることが確認できました。

参考情報

・Oracle Database 23c : CREATE DIRECTORY
・Oracle Database 23c : CREATE TABLE
・Loading External JSON Data