OceanBase Database supports the JSON data type, which allows you to store, index, and query JSON data. You can use the JSON data type to store JSON text and the TEXT type with the IS JSON constraint to store JSON text. You can 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 the 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 sample table 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 is 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
# Dynamically drop the JSON column b.
obclient> ALTER TABLE test_json_oracle1 DROP COLUMN b;
Query OK, 0 rows affected
# Dynamically add the JSON column b.
obclient> ALTER TABLE test_json_oracle1 ADD b JSON;
Query OK, 0 rows affected
# Create an index on the specified path of the 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 check whether the result of an expression is in the correct 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 JSON data format is considered correct only if the key names are unique in each object. If you specifyWITHOUT UNIQUE KEYS, the JSON data format is considered 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 check whetherexpris in the correct JSON format.
Note
-
Since OceanBase Database internally supports 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 check whether the current 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 in the database 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 an IS JSON CHECK constraint on a text column.
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 field names in a given JSON object must be unique. You can use the WITH UNIQUE KEYS keyword to specify that JSON data is valid only when all objects in the specific JSON data have unique field names (that is, no object has duplicate field names). Here is an example:
# Create a JSON text column with an IS JSON constraint using the WITH UNIQUE KEYS clause.
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 WITH UNIQUE KEYS clause is effective. The data contains two duplicate keys, so an error occurs.
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 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