OceanBase Database supports the JSON data type, which allows you to store JSON data in the database, create indexes on JSON data, and query JSON data. You can also store JSON data in a TEXT column with the IS JSON constraint. You can create a table that contains a JSON column. You can also create multiple JSON columns in a table. However, the following limitations apply:
- A JSON column cannot be a PRIMARY KEY, FOREIGN KEY, or UNIQUE KEY column. However, you can specify the path of JSON data as an index.
- A JSON column cannot be a partitioning key.
The following example shows how to create a JSON column.
# 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
# Insert data into the table. Column b has a specified value, but columns c and d do not. The insertion 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)'
# Insert data into the table. Column b has no 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 from the table.
obclient> ALTER TABLE test_json_oracle1 DROP COLUMN b;
Query OK, 0 rows affected
# Add column b back to the table.
obclient> ALTER TABLE test_json_oracle1 ADD b JSON;
Query OK, 0 rows affected
# Create an index on the specified path of 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 the correct JSON data 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 JSON data format is considered correct only when the keys in each object are unique. If you specifyWITHOUT UNIQUE KEYS, the JSON data format is considered correct even if the keys in an object are not unique. The default value isWITHOUT UNIQUE KEYS.FORMAT JSON: This option is used whenexpris a BLOB.STRICT|LAX: Specifies whether to use strict syntax to verify whetherexpris in the correct JSON data 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. We do not recommend that you 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 whether the 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 (i.e., 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 duplicate keys, so an error is returned.
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234, key:123}');
OBE-02290: check constraint violated
# No duplicate keys. The data is written successfully.
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