JSON data can be stored in databases, indexed, and queried. OceanBase Database supports JSON text and text with the IS JSON constraint. You can create tables with JSON columns in OceanBase Database. Each table can contain multiple JSON columns. Be aware of the following limitations on JSON columns:
- A JSON column cannot be used as the primary key, foreign key, or unique key. However, you can define a particular JSON path expression as a function-based index.
- JSON columns cannot be used as partitioning keys.
The following examples show you how to create JSON columns.
# Create a table with JSON columns b, c, and d. Column b has no constraint, column c has a 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 specified values, but columns c and d do not. The data cannot be written because column c has a 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 explicitly specified values, and column c is an empty object. The query result shows that an empty object is inserted into column 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
# Dynamically drop JSON column b.
obclient> ALTER TABLE test_json_oracle1 DROP COLUMN b;
Query OK, 0 rows affected
# Dynamically add JSON column b.
obclient> ALTER TABLE test_json_oracle1 ADD b JSON;
Query OK, 0 rows affected
# Create an index, which can be a specified JSON path expression.
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 as SQL conditions in the SQL statement to check whether the result of the expression is valid JSON data. The syntax is as follows:
expr:
IS [NOT] JSON
[FORMAT JSON]
[STRICT|LAX]
[ALLOW|DISALLOW SCALARS]
[WITH|WITHOUT UNIQUE KEYS]
WITH | WITHOUT UNIQUE KEYS: IfWITH UNIQUE KEYSis specified, the result is considered valid JSON data only if the key name is unique in each object. IfWITHOUT UNIQUE KEYSis specified, the result is considered valid JSON data even if duplicate key names exist in an object. The default value isWITHOUT UNIQUE KEYS.FORMAT JSON: This option is required when the data type ofexpris BLOB.STRICT|LAX: specifies whether to use strict syntax to check whether the result ofexpris valid JSON data.
Note
-
OceanBase Database supports JSON data types. Therefore, you can directly define JSON columns to store JSON data. We recommend that you do not 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 VARCHAR2, CLOB, or BLOB columns of a database and use the IS JSON constraint to ensure that valid JSON data is inserted into the columns.
# Create a table with the IS JSON CHECK constraint on text columns.
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
# Write an invalid JSON data record.
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234}');
ORA-02290: check constraint violated
# Write a valid JSON data record.
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 of the JSON objects must be unique. You can use the WITH UNIQUE KEYS keyword to specify that JSON data is valid only when all objects it contains have unique field names. Here is an example:
# Use the WITH UNIQUE KEY condition clause to create a JSON text column with the IS JSON 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 takes effect. Two keys in the data are duplicated, and an error occurs.
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234, key:123}');
ORA-02290: check constraint violated
# No duplicate keys exist, and the write operation is successful.
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