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

expr:
    IS [NOT] JSON
        [FORMAT JSON]
        [STRICT|LAX]
        [ALLOW|DISALLOW SCALARS]
        [WITH|WITHOUT UNIQUE KEYS]

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

# 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

# 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