OceanBase Database allows you to store JSON data in the database, create indexes on JSON data, and query JSON data. OceanBase Database supports a data type named JSON for storing JSON text and a text type with the IS JSON constraint for storing JSON text. OceanBase Database allows you to create a table that contains a JSON column. Each table can contain multiple JSON columns. However, the following limitations apply:
- JSON columns cannot be primary keys, foreign keys, or unique keys. However, you can specify a path in JSON data as an index.
- JSON columns cannot be partitioning keys.
The following example shows how to create a column of the JSON data type.
# Create a table named test_json_oracle1 with three JSON columns: b, c, and d. Column b has no constraints, column c has the NOT NULL constraint, and column d has a default value.
obclient> CREATE TABLE test_json_oracle1(a INT PRIMARY KEY, b JSON, c JSON NOT NULL, d JSON DEFAULT '{}');
Query OK, 0 rows affected
# Execute an INSERT statement. Column b has a specified value, but columns c and d do not. The statement fails because column c has the NOT NULL constraint.
obclient> INSERT INTO test_json_oracle1(a, b) VALUES(1, NULL);
OBE-01400: cannot insert NULL into '(C)'
# Execute an INSERT statement. Column b does not have a specified value, column c has an empty object, and column d is filled with an empty object.
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
# Drop column b.
obclient> ALTER TABLE test_json_oracle1 DROP COLUMN b;
Query OK, 0 rows affected
# Add column b.
obclient> ALTER TABLE test_json_oracle1 ADD b JSON;
Query OK, 0 rows affected
# Create an index on a specified path in JSON data.
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
The IS JSON and IS NOT JSON conditions are used in SQL statements to verify whether the result of an expression is in a valid JSON format. The syntax is as follows:
expr:
IS [NOT] JSON
[FORMAT JSON]
[STRICT|LAX]
[ALLOW|DISALLOW SCALARS]
[WITH|WITHOUT UNIQUE KEYS]
WITH | WITHOUT UNIQUE KEYS: If you specifyWITH UNIQUE KEYS, the condition considers the JSON data format correct only when the key names are unique in each object. If you specifyWITHOUT UNIQUE KEYS, the condition considers the JSON data format correct even if the key names are not unique in an object. The default value isWITHOUT UNIQUE KEYS.FORMAT JSON: This option is used whenexpris a BLOB.STRICT|LAX: Specifies whether to use strict syntax to determine whetherexpris in a valid JSON format.
Note
-
Since OceanBase Database internally implements the JSON data type, you can directly define a column as the JSON data type to store JSON data. It is not recommended to use the
IS [NOT] JSON constraint to verify whether a column is a JSON column.
Here is an example:
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 clause
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 clause
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
You can store JSON data in a column of the VARCHAR2, CLOB, or BLOB data type and use IS JSON as a check constraint to ensure that the data inserted into the column is valid JSON data.
# Create a table with a text column that has an IS JSON CHECK constraint.
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
# Insert invalid JSON data.
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234}');
OBE-02290: check constraint violated
# Insert valid JSON data.
obclient> INSERT INTO json_data_with_constraint VALUES ('[1,2,3]');
Query OK, 1 row affected
The JSON standard does not specify that field names in a given JSON object must be unique. You can use the WITH UNIQUE KEYS keyword to specify that the JSON data is valid only if all objects in the data have unique field names (that is, no object has duplicate field names). Here is an example:
# Create a table with a text column that has an IS JSON CHECK constraint.
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
# The condition clause is effective. The data contains two duplicate keys, so an error is returned.
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234, key:123}');
OBE-02290: check constraint violated
# The data does not contain duplicate keys. The data is inserted.
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