Oracle JSON型を試してみたのでメモ

目的

OracleでJSON型が使用できるようになったとのことなので、使うかどうかわからないが試してみたメモ

「ネイティブ・バイナリ形式OSONを使用したJSONデータ」というらしい
https://docs.oracle.com/cd/F32587_01/adjsn/json-in-oracle-database.html#GUID-F6282E67-CBDF-442E-946F-5F781BC14F33
『JSONデータ型(他のSQLデータ型との変換)』参照

環境

Oracle 21c

SQL*PlusでのDB操作

テーブル作成

SQL> create table test_tbl(json_fld json);

レコード登録

insert into test_tbl
values('{"user_id": "U000000001","fld1": "str1","fld2": "2","fld3": "2023/3/15"}');

select

クライアントが21cの場合は、select * from test_tblでJSONが表示されるが、12cの場合は、正常に表示されない。
12cでもフィールド毎にselectすることはできる。

fld1を文字列型としてselectする場合

SQL> select t.json_fld.fld1.string() from test_tbl t;

json_fldの前にt_alias(この例の場合はt)が必要。
t_aliasではなくtest_tbl.json_fldと記述するとエラーになる。
文字列として取得する場合はstring()が必要。
※t_aliasは以下の表記に基づいた
https://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_10002.htm

fld2を数値型としてselectする場合

SQL> select t.json_fld.fld2.number() from test_tbl t;

fld3を日付型としてselectする場合

fld3.date()では表示されない。

SQL> select to_date(t.json_fld.fld3.string(), 'YYYY/MM/DD') from test_tbl t

検索条件とする場合

where t.json_fld.fld1.string()='str1'のように指定する。
他のテーブルとの結合条件としても使用できる。
・・・が、検索条件、結合条件にするならば、別途フィールドを設けたほうが効率がよいのではないかと思う。

参考