JSONデータはデータベースに格納し、インデックスを作成してクエリを実行できます。OceanBaseデータベースでは、JSON という名前のデータ型をサポートしており、ユーザーはJSONテキストを保存できます。また、IS JSON 制約を持つテキスト型もJSONテキストの保存に利用できます。 OceanBaseデータベースでは、JSON列を含むテーブルを作成でき、各テーブルに複数のJSON型列を作成できます。ただし、以下の制限があります：
- JSON型の列は主キー、外部キー、一意キーとして使用できませんが、JSONデータ内のパスを指定してインデックスを作成できます。
- JSON型の列はパーティションキーとして使用できません。
以下の例は、JSONデータ型の列を作成する方法を示しています。
# サンプルテーブルを作成し、3つのJSON列b、c、dを追加します。b列には制約を設定せず、c列にはNOT NULL制約を設定し、d列にはデフォルト値を設定します。
obclient> CREATE TABLE test_json_oracle1(a INT PRIMARY KEY, b JSON, c JSON NOT NULL, d JSON DEFAULT '{}');
Query OK, 0 rows affected
# INSERT操作を実行します。b列には確定値がありますが、c列とd列には値がないため、c列にNOT NULL制約があるため書き込みは成功しません。
obclient> INSERT INTO test_json_oracle1(a, b) VALUES(1, NULL);
OBE-01400: cannot insert NULL into '(C)'
# INSERT操作を実行します。b列に明示的な値を指定せず、c列に空のオブジェクトを代入します。結果として、d列にも空のオブジェクトが入力されます。
obclient> INSERT INTO test_json_oracle1(a, c) VALUES(1, '{}');
Query OK, 1 row affected
obclient> SELECT * FROM test_json_oracle1;
+---+------+----+------+
| A | B | C | D |
+---+------+----+------+
| 1 | NULL | {} | {} |
+---+------+----+------+
1 row in set
# JSON列bを動的に削除します。
obclient> ALTER TABLE test_json_oracle1 DROP COLUMN b;
Query OK, 0 rows affected
# JSON列bを動的に追加します。
obclient> ALTER TABLE test_json_oracle1 ADD b JSON;
Query OK, 0 rows affected
# インデックスを作成します。JSONデータ内の指定したパスを使用できます。
obclient> CREATE TABLE t (id INT PRIMARY KEY, docs JSON NOT NULL, docs1 JSON);
Query OK, 0 rows affected
obclient> CREATE UNIQUE INDEX j_idx on t (JSON_VALUE(t.docs, '$.id'));
Query OK, 0 rows affected
IS JSON と IS NOT JSON は、SQLステートメント内の条件として使用され、式の結果が有効なJSONデータ形式であるかどうかを検証します。構文は以下のとおりです：
expr:
IS [NOT] JSON
[FORMAT JSON]
[STRICT|LAX]
[ALLOW|DISALLOW SCALARS]
[WITH|WITHOUT UNIQUE KEYS]
WITH | WITHOUT UNIQUE KEYS：WITH UNIQUE KEYSを指定した場合、この条件はJSONデータ形式が正しいと見なされるのは、キー名が各オブジェクト内で一意である場合のみです。WITHOUT UNIQUE KEYSを指定した場合、オブジェクト内に重複するキー名が存在しても、この条件はJSONデータ形式が正しいと見なされます。デフォルト値はWITHOUT UNIQUE KEYSです。FORMAT JSON：exprがBLOBの場合に使用するオプションです。STRICT|LAX：exprが有効なJSONデータであるかどうかを厳密な構文に従って判断するかどうかを示します。
説明
-
OceanBaseデータベースは内部でJSON型を実装しているため、JSONデータを格納する場合は、列を直接JSON型として定義するだけで済みます。この記事で説明している方法で `
IS [NOT] JSON` 制約を追加して、現在の列がJSON列かどうかを検証することは推奨されません。
例：
CREATE TABLE js_t1 (col1 VARCHAR2(100));
INSERT INTO js_t1 VALUES ( '[ "LIT192", "CS141", "HIS160" ]' );
INSERT INTO js_t1 VALUES ( '{ "Name": "John" }' );
INSERT INTO js_t1 VALUES ( '{ "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } }');
INSERT INTO js_t1 VALUES ( '{ "isEnrolled" : true }' );
INSERT INTO js_t1 VALUES ( '{ "isMatriculated" : False }' );
INSERT INTO js_t1 VALUES (NULL);
INSERT INTO js_t1 VALUES ('This is not well-formed JSON data');
obclient> SELECT col1 FROM js_t1 WHERE col1 IS JSON;
+----------------------------------------------------+
| COL1 |
+----------------------------------------------------+
| [ "LIT192", "CS141", "HIS160" ] |
| { "Name": "John" } |
| { "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } } |
| { "isEnrolled" : true } |
| { "isMatriculated" : False } |
+----------------------------------------------------+
5 rows in set
# STRICT句
obclient> SELECT col1 FROM js_t1 WHERE col1 IS NOT JSON STRICT AND col1 IS JSON LAX;
+----------------------------------------------------+
| COL1 |
+----------------------------------------------------+
| { "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } } |
| { "isMatriculated" : False } |
+----------------------------------------------------+
2 rows in set
# WITH UNIQUE KEYS句
CREATE TABLE js_t2 (col1 VARCHAR2(100));
INSERT INTO js_t2 VALUES ('{a:100, b:200, c:300}');
INSERT INTO js_t2 VALUES ('{a:100, a:200, b:300}');
INSERT INTO js_t2 VALUES ('{a:100, b : {a:100, c:300}}');
obclient> SELECT col1 FROM js_t2 WHERE col1 IS JSON WITH UNIQUE KEYS;
+-----------------------------+
| COL1 |
+-----------------------------+
| {a:100, b:200, c:300} |
| {a:100, b : {a:100, c:300}} |
+-----------------------------+
2 rows in set
VARCHAR2、CLOB、または BLOB データ型の列を使用して、JSONデータをデータベースに格納できます。IS JSON をチェック制約と組み合わせて使用することで、列に挿入されるデータが形式の正しいJSONデータであることを保証できます。
# テーブルを作成し、テキスト列にIS JSON CHECK制約を追加します。
obclient> CREATE TABLE json_data_with_constraint
(po_doc VARCHAR2 (2048) CONSTRAINT ensure_json CHECK (po_doc IS JSON (STRICT)));
Query OK, 0 rows affected
# 無効なJSONデータを1件書き込みます。
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234}');
OBE-02290: check constraint violated
# 有効なJSONデータを1件書き込みます。
obclient> INSERT INTO json_data_with_constraint VALUES ('[1,2,3]');
Query OK, 1 row affected
指定されたJSONオブジェクトについて、JSON標準ではフィールド名が一意である必要があるかどうかは規定されていません。WITH UNIQUE KEYS キーワードを使用することで、特定のJSONデータに含まれるすべてのオブジェクトが一意のフィールド名を持つ場合（つまり、重複するフィールド名を持つオブジェクトがない場合）にのみ、そのデータを形式が正しいと見なすように指定できます。例：
# WITH UNIQUE KEY条件句を使用して、IS JSON制約を持つJSONテキスト列を作成します。
obclient> CREATE TABLE json_data_with_constraint (po_doc VARCHAR2 (2048)
CONSTRAINT ensure_json CHECK (po_doc IS JSON(WITH UNIQUE KEYS)));
Query OK, 0 rows affected
# 条件句が有効なため、データ内の2つのKeyが重複し、エラーが発生します。
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234, key:123}');
OBE-02290: check constraint violated
# Keyに重複がないため、書き込みは成功します。
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234, key2:123}');
Query OK, 1 row affected
obclient> DROP TABLE json_data_with_constraint;
Query OK, 0 rows affected