はじめに

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をサポートする言語間でのデータ交換がしやすくなるように思います。

Oracle Database 23c Free - Developer ReleaseでPL/SQLでのJSON型のデータ変換を試す