JSONデータはデータベースに格納し、インデックスを作成してクエリを実行できます。OceanBaseデータベースでは、JSONという名前のデータ型をサポートしており、ユーザーがJSONテキストを保存したり、IS JSON制約を持つテキスト型を使用してJSONテキストを保存したりできます。 OceanBaseデータベースでは、JSON列を含むテーブルを作成でき、各テーブルに複数のJSON型の列を作成できます。ただし、以下の制限があります：
- JSON型の列はPrimary Key、Foreign Key、Unique Keyとして使用できませんが、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