Oracle Database 23c：JSONスキーマを使用してJSON列のCHECK制約をアプリケーション側で事前にチェックしてみた

# はじめに
Oracle Databse 23cでは、JSONスキーマを使用してJSON列のCHECK制約をアプリケーション側で事前にチェックできるようになっています。

今回はその事前チェックを行う場合に利用できるPL/SQLファンクションの動作の確認と、実際に事前チェックを行うPL/SQLファンクションを作成してみました。

# 1. JSONスキーマを使用したCHECK制約

JSON型の列jcolを持つ表jtabを作成します。
jcol列にはJSONスキーマを用いたCHECK制約json_chkが付与されています。
```
SQL> CREATE TABLE jtab (
  2  	jcol JSON
  3  	CONSTRAINT json_chk 
  4  	CHECK (jcol IS JSON VALIDATE '{"type"       : "object", 
  5                                    "properties" : {"firstName" : {"type"      : "string",
  6                                                                   "minLength" : 1}, 
  7                                                    "salary"    : {"type"      : "number",
  8                                                                   "minimum"   : 10000}
  9                                                    },
 10                                    "required"   : ["firstName"]
 11                                   }')
 12  );

Table created.

SQL>
```
IS JSON VALIDATE の後に記述されている
```
{"type"       : "object",
 "properties" : {"firstName" : {"type"      : "string"
                                "minLength" : 1}, 
                 "salary"    : {"type"      : "number",
                                "minimum"   : 10000}
                },
 "required"   : ["firstName"]
}
```
の部分がJSONスキーマになります。

このJSONスキーマに対して有効なドキュメントの条件は以下の通りです。
・必須のプロパティfirstNameとオプションのプロパティsalaryを含むJSONオブジェクトである
・プロパティfirstNameの値は1文字以上の文字列データである
・プロパティsalaryは必須ではないが、もし存在する場合、その値は10,000以上の数値データである

なお、こちらの記事にあるように、SQLドメインを使用してJSONスキーマを定義することも可能です。

https://qiita.com/bambooseven/items/bf9746f40826f3aa9e59

# 2. アプリケーション側でのJSONスキーマの取得
DBMS_JSON_SCHEMA.DESCRIBEファンクションを使用して、表やビュー、JSON Relational Dualityビューおよびその列からJSONスキーマを取得することができます。

DBMS_JSON_SCHEMA.DESCRIBEファンクションを使用して、表jtabのjcol列からJSONスキーマを取得してみます。
```
SQL> SELECT DBMS_JSON_SCHEMA.DESCRIBE(
  2             object_name => 'JTAB',      -- JSONスキーマのベースとなるオブジェクト名
  3             owner_name  => 'TESTUSER',  -- オブジェクトの所有者
  4             column_name => 'JCOL'       -- JSONスキーマのベースとなる列名
  5         ) AS json_schema;

JSON_SCHEMA
--------------------------------------------------------------------------------
{"dbColumn":"JCOL","allOf":[{"type":"object","properties":{"firstName":{"type":"
string","minLength":1},"salary":{"type":"number","minimum":10000}},"required":["
firstName"]}]}


SQL>
```
このままではわかりにくいので、JSON_SERIALIZEファンクションをPRETTY句を指定して使用して、JSONスキーマを表示します。
```
SQL> SELECT JSON_SERIALIZE(
  2             DBMS_JSON_SCHEMA.DESCRIBE(
  3                 object_name => 'JTAB',      -- JSONスキーマのベースとなるオブジェクト名
  4                 owner_name  => 'TESTUSER',  -- オブジェクトの所有者
  5                 column_name => 'JCOL'       -- JSONスキーマのベースとなる列名
  6             )
  7             PRETTY) AS json_schema;

JSON_SCHEMA
----------------------------------------------------------------------------------------------------
{
  "dbColumn" : "JCOL",
  "allOf" :
  [
    {
      "type" : "object",
      "properties" :
      {
	"firstName" :
	{
	  "type" : "string",
	  "minLength" : 1
	},
	"salary" :
	{
	  "type" : "number",
	  "minimum" : 10000
	}
      },
      "required" :
      [
	"firstName"
      ]
    }
  ]
}


SQL>
```
DBMS_JSON_SCHEMA.DESCRIBEファンクションを使用して、表jtabの列jcolに指定されているCHECK制約のJSONスキーマを取得することができました。

# 3. JSONスキーマに対するJSONデータの検証
DBMS_JSON_SCHEMA.IS_VALIDファンクションを指定して、JSONデータがJSONスキーマに合致しているかを検証することができます。

引数json_dataに指定したJSONデータが引数json_schemaに指定したJSONスキーマに合致している場合は、DBMS_JSON_SCHEMA.IS_VALIDは戻り値として1を返します。

```
SQL> SELECT DBMS_JSON_SCHEMA.IS_VALID(
  2             json_data   => '{"firstName":"Yamada"}',
  3             json_schema =>'{"dbColumn":"JCOL","allOf":[{"type":"object","properties":{"firstName":{"type":"string","minLength":1},"salary":{"type":"number","minimum":10000}},"required":["firstName"]}]}'
  4         ) AS result;

    RESULT
----------
	     1

SQL> 
```
引数json_dataに指定したJSONデータが引数json_schemaに指定したJSONスキーマに合致していない場合は、DBMS_JSON_SCHEMA.IS_VALIDは戻り値として0を返します。
```
SQL> SELECT DBMS_JSON_SCHEMA.IS_VALID(
  2             json_data   => '{"firstName":""}',
  3             json_schema => '{"dbColumn":"JCOL","allOf":[{"type":"object","properties":{"firstName":{"type":"string","minLength":1},"salary":{"type":"number","minimum":10000}},"required":["firstName"]}]}'
  4         ) AS result;

    RESULT
----------
	     0

SQL>
```

# 4. CHECK制約のJSONスキーマを取得してJSONデータを検証するPL/SQLファンクションの作成
生成したJSONデータをDBMS_JSON_SCHEMA.DESCRIBEファンクションで取得したJSONスキーマを使用して検証することで、データベースにデータを投入する前に、アプリケーション側で事前にチェックができます。

ここでは、DBMS_JSON_SCHEMA.DESCRIBEファンクションとDBMS_JSON_SCHEMA.IS_VALIDファンクションを使用して、データベースに投入するJSONデータが列のCHECK制約に違反していないかを事前に確認するPL/SQLファンクション VALIDATE_WITH_JSON_SCHEMA を作成してみます。

VALIDATE_WITH_JSON_SCHEMAファンクションは、パラメータとして検証対象となるJSONデータ（l_json_text）、JSONスキーマによるCHECK制約がある列を含む表の名前（l_table_name）、JSONスキーマによるCHECK制約がある列の名前（l_column_name）を渡すと、指定した列のCHECK制約のJSONスキーマを使用してパラメータとして渡したJSONデータを検証した結果をBOOLEANで返します。
```
SQL> CREATE OR REPLACE FUNCTION validate_with_json_schema (
  2      l_json_text   IN VARCHAR2,
  3      l_table_name  IN VARCHAR2,
  4      l_column_name IN VARCHAR2
  5  )
  6  RETURN BOOLEAN
  7  IS
  8      l_json_data   JSON := JSON(l_json_text);
  9      l_json_schema JSON;
 10      l_result      BOOLEAN;
 11  BEGIN
 12      -- DBMS_JSON_SCHEMA.DESCRIBEファンクションで、指定された列のJSONスキーマを取得
 13      SELECT DBMS_JSON_SCHEMA.DESCRIBE(
 14  	           object_name => UPPER(l_table_name),
 15                column_name => UPPER(l_column_name)
 16             ) INTO l_json_schema;
 17      -- DBMS_JSON_SCHEMA.IS_VALIDファンクションで、入力されたJSONデータに対してJSONスキーマを使用した検証を実行
 18      SELECT DBMS_JSON_SCHEMA.IS_VALID(l_json_data, l_json_schema) INTO l_result;
 19      -- JSONスキーマによる検証結果をファンクションの戻り値として返す
 20      RETURN l_result;
 21  END;
 22  /

Function created.

SQL> 
```
ファンクションvalidate_with_json_schemaが作成できました。

早速実行してみます。

まずは、検証対象のJSONドキュメントとして「{"firstName":""}」を渡して、表jtabのjcol列のCHECK制約のJSONスキーマと合致しているかを確認してみます。

表jtabのjcol列のCHECK制約のJSONスキーマに対して有効なドキュメントの条件は以下の通りです。
・必須のプロパティfirstNameとオプションのプロパティsalaryを含むJSONオブジェクトである
・プロパティfirstNameの値は1文字以上の文字列データである
・プロパティsalaryは必須ではないが、もし存在する場合、その値は10,000以上の数値データである

```
SQL> SELECT VALIDATE_WITH_JSON_SCHEMA ('{"firstName":""}','jtab','jcol') AS result;

RESULT
-----------
FALSE

SQL> 
```
「{"firstName":""}」はプロパティfirstNameの値が1文字以上ではないため、検証がNGとなりFALSEが返ってきました。

次に検証対象のJSONドキュメントとして「{"firstName":"Yamada"}」を渡して、表jtabのjcol列のCHECK制約のJSONスキーマと合致しているかを確認してみます
```
SQL> SELECT VALIDATE_WITH_JSON_SCHEMA ('{"firstName":"Yamada"}','jtab','jcol') AS result;

RESULT
-----------
TRUE

SQL> 
```
「{"firstName":"Yamada"}」は表jtabのjcol列のCHECK制約のJSONスキーマと合致してしているため、検証がOKとなりTRUEが返ってきました。

さらに検証対象のJSONドキュメントとして「{"firstName":"Yamada","salary":5000}」を渡して、表jtabのjcol列のCHECK制約のJSONスキーマと合致しているかを確認してみます

```
SQL> SELECT VALIDATE_WITH_JSON_SCHEMA ('{"firstName":"Yamada","salary":5000}','jtab','jcol') AS result;

RESULT
-----------
FALSE

SQL> 
```
「{"firstName":"Yamada","salary":5000}」は、プロパティsalaryの値が10000以上ではないため、検証がNGとなりFALSEが返ってきました。

最後に検証対象のJSONドキュメントとして「{"firstName":"Yamada","salary":10000}」を渡して、表jtabのjcol列のCHECK制約のJSONスキーマと合致しているかを確認してみます
```
SQL> SELECT VALIDATE_WITH_JSON_SCHEMA ('{"firstName":"Yamada","salary":10000}','jtab','jcol') AS result;

RESULT
-----------
TRUE

SQL>
```
「{"firstName":"Yamada","salary":10000}」は表jtabのjcol列のCHECK制約のJSONスキーマと合致してしているため、検証がOKとなりTRUEが返ってきました。

JSONスキーマを使用して、データベースにデータを投入する前にJSON列のCHECK制約をアプリケーション側で事前にチェックできることが確認できました。

# 参考情報
・[JSON Schemas Generated with DBMS_JSON_SCHEMA.DESCRIBE ](https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/json-schema.html#GUID-18F905B9-DC6A-442A-8996-B1CF407CD585)
・[Validating JSON Documents with a JSON Schema](https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/json-schema.html#GUID-7D67DF73-CB21-4878-AF93-D1A213411EC0)