You can store JSON data in a database, create indexes for it, and query it. OceanBase Database supports the JSON data type, which allows you to store JSON text. It also supports the TEXT data type with the IS JSON constraint for storing JSON text. You can create a table that contains a JSON column. Each table can have 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 operation. Column b has a specified value, but columns c and d do not. The operation fails because column c has the NOT NULL constraint.
obclient> INSERT INTO test_json_oracle1(a, b) VALUES(1, NULL);
ORA-01400: cannot insert NULL into '(C)'
# Execute an INSERT operation. Column b does not have an explicit 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 a 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
IS JSON and IS NOT JSON are used in SQL statements to check 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 keys are unique in each object. If you specifyWITHOUT UNIQUE KEYS, the condition considers the JSON data format correct even if the keys 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 check whetherexpris valid JSON data.
Note
-
Since OceanBase Database internally implements the JSON data type, you can directly define a column as JSON 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 and use IS JSON as a check constraint to ensure that the data inserted into the column is well-formed 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}');
ORA-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 a JSON object is well-formed only if all objects in the JSON 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 two duplicate keys, so an error occurs.
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234, key:123}');
ORA-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