はじめに
Oracle Database 23c Free - Developer ReleaseでJSON constructorとJSON_VALUEがJSON型の変換をサポートするようになったので試してみたいと思います。
Oracle Database 23c Free - Developer Releaseのインストール
こちらを参考にインストールします。
Setting Oracle Database Free Environment Variablesまで実行できたら完了です。
データベースへの接続
SQL*Plusからsysdbaとしてコンテナ・データベースに接続します。
[oracle@instance-20230605-1511-db23c ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Jun 9 02:57:37 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
ALTER SESSION SET CONTAINER文でPDB（FREEPDB1）に接続します。
SQL> ALTER SESSION SET CONTAINER=FREEPDB1;
Session altered.
SQL>
確認用にPL/SQLの標準出力を表示に変えておきます。
set serveroutput on
これで検証の準備が整いました。
文字列のJSONデータをJSON型に変換する
最初に文字列のJSONデータをJSON型に変換してみます。
以下のSQLを実行します。
WITH JTAB AS (
SELECT
JSON( '{ "name" : "Alexis Bill",
"Address": { "street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America" } }') AS JCOL
FROM
DUAL
)
SELECT
J.JCOL.Address.city
FROM
JTAB J;
以下の結果が出力され文字列のJSONデータがJSON型に変換されていることが確認できます。
ADDRESS
--------------------------------------------------------------------------------
"South San Francisco"
VARRAY型をJSON型に変換する
次にVARRAY型をJSON型に変換してみます。
以下のPL/SQLを実行します。
DECLARE
TYPE theVarray IS VARRAY(4) OF NUMBER;
myVarray theVarray := theVarray(1, 2, 3, null);
myJSON JSON;
BEGIN
myJSON := JSON(myVarray);
DBMS_OUTPUT.PUT_LINE(JSON_SERIALIZE(myJSON));
END;
/
以下の結果が出力されます。VARRAY型をJSON型に変換してjson_serializeでテキスト表記にして出力しています。正しく出力されていることで変換されていることが確認できます。
[1,2,3,null]
JSON型をVARRAY型に変換する
次は逆にJSON型をVARRAY型に変換します。
以下のPL/SQLを実行します。
DECLARE
TYPE theVarray IS VARRAY(5) OF NUMBER;
myVarray theVarray;
BEGIN
myVarray := JSON_VALUE(JSON('[1, 2, 3, 4, 5]'), '$' RETURNING theVarray);
DBMS_OUTPUT.PUT_LINE('COUNT = ' || myVarray.COUNT);
FOR i IN 1..myVarray.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(myVarray(i));
END LOOP;
END;
/
以下のように出力されJSON型をVARRAY型に変換されていることが確認できます。
COUNT = 5
1
2
3
4
5
連想配列をJSON型に変換する
次は連想配列をJSON型に変換してみます。
以下のPL/SQLを実行します。
DECLARE
TYPE AsscArray IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
myAsscArray AsscArray := AsscArray('FIRST_NAME' => 'Bob', 'LAST_NAME' => 'Jones');
myJson JSON;
BEGIN
myJson := JSON(myAsscArray);
DBMS_OUTPUT.PUT_LINE(JSON_SERIALIZE(myJson));
END;
/
以下の結果が出力され、連想配列からJSON型に変換されていることが確認できます。
{"FIRST_NAME":"Bob","LAST_NAME":"Jones"}
JSON型を連想配列に変換する
次は逆にJSON型から連想配列に変換します。
以下のPL/SQLを実行します。
DECLARE
TYPE theAsscArray IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
myAsscArray theAsscArray;
BEGIN
myAsscArray := JSON_VALUE(JSON('{"Key1":10, "Key2":20}'), '$' RETURNING theAsscArray);
DBMS_OUTPUT.PUT_LINE('COUNT = ' || myAsscArray.COUNT);
DBMS_OUTPUT.PUT_LINE(myAsscArray('Key1'));
DBMS_OUTPUT.PUT_LINE(myAsscArray('Key2'));
END;
/
以下の結果が出力されJSON型から連想配列に変換されていることが確認できます。
COUNT = 2
10
20
まとめ
試してみたところ、JSON型からVARRAY、連想配列などの型に変換できることが確認できました。変換できることでPL/SQLとJSONをサポートする言語間でのデータ交換がしやすくなるように思います。
参考