创建 JSON 数据类型的列 - OceanBase 数据库 V5.0.1

创建 JSON 数据类型的列

更新时间：2026-07-29 10:39:52

JSON 数据可以存储在数据库中、建立索引和查询，OceanBase 数据库支持名称为 JSON 的数据类型用户保存 JSON 文本，以及带有 IS JSON 约束的文本类型保存 JSON 文本。 OceanBase 数据库支持用户创建一个包含 JSON 列的表，且每个表可以创建多个类型为 JSON 的列。但是存在如下限制：

JSON 类型的列不能作为 Primary Key、Forgin Key 和 Unique Key，但可以指定 JSON 数据的路径作为索引。
JSON 类型的列不能作为分区键。

如下示例展示如何创建 JSON 数据类型的列。

# 创建示例表并带有 3 个 JSON 列 b、c 和 d，其中 b 列没有任何约束，c 列带有 NOT NULL 约束，d 列带有默认值
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 操作，b 列有确定值, c、d 列没有，无法写入成功，因为 c 列上有 NOT NULL 约束
obclient> INSERT INTO test_json_oracle1(a, b) VALUES(1, NULL);
OBE-01400: cannot insert NULL into '(C)'

# 执行 INSERT 操作，b 列没有显式指定值，c 列是一个空对象，查询结果，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

# 动态删除 JSON 列 b
obclient> ALTER TABLE test_json_oracle1 DROP COLUMN b;
Query OK, 0 rows affected

# 动态添加 JSON 列 b
obclient> ALTER TABLE test_json_oracle1 ADD b JSON;
Query OK, 0 rows affected

# 创建索引，可以使用 JSON 数据的指定路径
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 和 IS NOT JSON 在 SQL 语句中作为 SQL 条件用以校验表达式结果是否是合法的 JSON 数据格式。语法格式如下：

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

WITH | WITHOUT UNIQUE KEYS：如果指定 WITH UNIQUE KEYS，则仅当键名在每个对象中唯一时，此条件才认为 JSON 数据格式正确；如果指定WITHOUT UNIQUE KEYS，则即使对象中出现重复的键名，此条件也会认为 JSON 数据格式正确。默认值为 WITHOUT UNIQUE KEYS。
FORMAT JSON：当 expr 是 BLOB 的时候，应使用该选项。
STRICT|LAX：是否按照严格语法来判断 expr 是否是合法的 JSON 数据。

说明

IS [NOT] JSON

示例如下：

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 子句
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 子句
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

您可以使用数据类型为 VARCHAR2、CLOB 或 BLOB 的列将 JSON 数据存储在数据库中，并结合 IS JSON 作为检查约束，以确保插入到列中的数据是格式正确的 JSON 数据。

# 创建表, 文本列上带有 IS JSON CHECK 约束
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

# 写入一条非法的 JSON 数据
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234}');
OBE-02290: check constraint violated

# 写入一条合法的 JSON 数据
obclient> INSERT INTO json_data_with_constraint VALUES ('[1,2,3]');
Query OK, 1 row affected

对于给定的 JSON 对象，JSON 标准没有指定字段名称是否必须是唯一的。您可以通过使用关键字 WITH UNIQUE KEYS 指定仅当特定 JSON 数据包含的所有对象都具有唯一的字段名称（即没有对象具有重复的字段名称）时才将其视为格式正确。示例如下：

# 使用条件子句 WITH UNIQUE KEY 创建带有 IS JSON 约束的 JSON 文本列
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

# 条件子句生效，数据中 2 个 Key 重复，发生报错
obclient> INSERT INTO json_data_with_constraint VALUES ('{key:1234, key:123}');
OBE-02290: check constraint violated

# 没有重复的 Key, 写入成功
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