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

# はじめに
Oracle Database 23c Free - Developer ReleaseでJSON constructorとJSON_VALUEがJSON型の変換をサポートするようになったので試してみたいと思います。

# Oracle Database 23c Free - Developer Releaseのインストール
[こちら](https://docs.oracle.com/en/database/oracle/oracle-database/23/xeinl/installing-oracle-database-free.html#GUID-728E4F0A-DBD1-43B1-9837-C6A460432733)を参考にインストールします。

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

# 参考
https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/json-in-oracle-database.html#GUID-F04556DD-B435-425E-8EA6-FBE636FEEA2C

https://www.youtube.com/watch?v=FkVtYI1S5vY&ab_channel=OracleDevelopers