跳到主要内容
版本：V1.0.0

创建 JSON 值

JSON 值必须是由对象（JSON 对象）、数组、字符串、数字、布尔值（false/true）或 null 组成。其中 false、true 和 null 只允许小写形式。

JSON 文本结构​

JSON 文本结构包括字符、字符串、数字和三个字面量名称。在任何一个结构字符的之前或之后都允许使用各种间隔符，包括空格、水平制表符、换行和回车等。

       开始数组 =  [ 左方括号

       开始对象 =  { 左大括号

       结束数组 =  ] 右方括号

       最终对象 =  } 右大括号

       名称分隔符 = ： 冒号

       值分隔符 = , 逗号

对象​

对象的结构表示为一对大括号包含 0 个或多个名称/值对（或成员）。对象内的名称应该是唯一的。一个名称是一个字符串，每个名称后面都有一个冒号，用于分隔名称和值。单个逗号用于多个名称/值的分割。 示例如下：

{ "NAME": "SAM",  "Height": 175, "Weight":  100，"Registered" : false}

数组​

数组的结构表示为一个方括号包含 0 个或多个值（也称为元素）。数组元素用逗号分隔，不要求数组中的值相同。

示例如下：

["abc", 10, null, true, false]

数字​

数字使用十进制格式，包含一个整数分量，也可能是以一个减号（-）为前缀（可选），后面可以跟一个分数部分和/或指数部分。不允许前导为零。小数部分是一个小数点后跟一位或多位数字。指数部分以大写或小写字母 E 开头，后面可以跟一个加号（+）或减号（-）。E 和可选符号后可以跟随一位或多位数字。

示例如下：

[100, 0, -100, 100.11, -12.11, 10.22e2, -10.22e2]

字符串​

一个字符串开始和结束都使用引号（"）。所有 Unicode 字符都可以放在引号内，除了必须转义的字符（包括引号、反斜线和控制字符）。

JSON 文本应以 UTF-8、UTF-16 或 UTF-32 编码。默认的编码为 UTF-8。

示例如下：

{"Url":    "http://www.example.com/image/481989943"}

创建 JSON 值​

seekdb 支持对 JSON 类型执行以下 DDL 操作：

  • 创建带有 JSON 列的表。

  • 增加/删除 JSON 列。

  • 基于生成列为 JSON 类型的列创建索引。

  • 支持建表时开启半结构化编码。

  • 支持修改已有表启用半结构化编码。

使用限制​

用户可以在每个表上创建多个 JSON 类型的列，但是具有以下的限制：

  • JSON 类型的列不能作为 PRIMARY KEY、FOREIGN KEY 和 UNIQUE KEY，但是可以添加 NOT NULL 或 CHECK 约束。

  • JSON 类型的列上不可以包含默认值。

  • JSON 类型的列不能作为分区键。

  • JSON 数据长度不能超过 LONGTEXT 的长度，每个 JSON 对象或数组的最大深度为 99。

示例​

创建/修改 JSON 列​

CREATE TABLE tbl1 (id INT PRIMARY KEY, docs JSON NOT NULL, docs1 JSON);
Query OK, 0 rows affected

ALTER TABLE tbl1 MODIFY docs JSON CHECK(docs <'{"a" : 100}');
Query OK, 0 rows affected

CREATE TABLE json_tab(
      id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
      json_info  JSON COMMENT 'JSON 数据',
      json_id    INT GENERATED ALWAYS AS (json_info -> '$.id') COMMENT 'JSON 数据的虚拟字段',
      json_name  VARCHAR(5) GENERATED ALWAYS AS (json_info -> '$.NAME'),
      index      json_info_id_idx (json_id)
    )COMMENT 'json 示例表';
Query OK, 0 rows affected

ALTER TABLE json_tab ADD COLUMN json_info1 JSON;
Query OK, 0 rows affected

ALTER TABLE json_tab ADD INDEX (json_name);
Query OK, 0 rows affected

ALTER TABLE json_tab drop COLUMN json_info1;
Query OK, 0 rows affected

使用生成列为指定 key 创建索引​

CREATE TABLE jn ( c JSON, g INT GENERATED ALWAYS AS (c->"$.id"));
Query OK, 0 rows affected

CREATE INDEX idx1 ON jn(g);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

INSERT INTO jn (c) VALUES
      ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
      ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

SELECT c->>"$.name" AS name FROM jn WHERE g <= 2;

+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

EXPLAIN SELECT c->>"$.name" AS name FROM jn WHERE g <= 2\G

*************************** 1. row ***************************
Query Plan: ====================================================
*************************** 2. row ***************************
Query Plan: |ID|OPERATOR        |NAME    |EST.ROWS|EST.TIME(us)|
*************************** 3. row ***************************
Query Plan: ----------------------------------------------------
*************************** 4. row ***************************
Query Plan: |0 |TABLE RANGE SCAN|jn(idx1)|2       |83          |
*************************** 5. row ***************************
Query Plan: ====================================================
*************************** 6. row ***************************
Query Plan: Outputs & filters:
*************************** 7. row ***************************
Query Plan: -------------------------------------
*************************** 8. row ***************************
Query Plan:   0 - output([JSON_UNQUOTE(JSON_EXTRACT(jn.c, '$.name'))]), filter(nil), rowset=16
*************************** 9. row ***************************
Query Plan:       access([jn.__pk_increment], [jn.c]), partitions(p0)
*************************** 10. row ***************************
Query Plan:       is_index_back=true, is_global_index=false,
*************************** 11. row ***************************
Query Plan:       range_key([jn.g], [jn.__pk_increment]), range(NULL,MAX ; 2,MAX),
*************************** 12. row ***************************
Query Plan:       range_cond([jn.g <= 2])
12 rows in set (0.002 sec)

使用半结构化编码​

seekdb 下建表时开启半结构化编码，主要由表级别参数 semistruct_encoding_type 控制，同时也要设置表的 row_format=COMPRESSED，否则会报错。当 semistruct_encoding_type='encoding' 时，表被认为是半结构化表，即整张表中的 JSON 列都会启用半结构化编码；当 semistruct_encoding_type='' 时，表被认为是结构化表。

  1. 启用半结构化编码

    提示

    如果启用半结构化编码功能，请务必保证配置项 micro_block_merge_verify_level 为默认配置 2，切勿关闭微块合并校验。

    tab 建表时启用的示例

    CREATE TABLE t1(
      j json
    ) row_format=COMPRESSED semistruct_encoding_type = 'encoding';

    更多语法说明请见 CREATE TABLE。

    tab 修改已有表来启用半结构化编码的示例

    CREATE TABLE t1(j json);
    ALTER TABLE t1 SET row_format=COMPRESSED semistruct_encoding_type = 'encoding';

    更多语法说明请见 ALTER TABLE。

  2. 关闭半结构化编码

    关闭半结构化编码的示例如下：

    ALTER TABLE t1 SET row_format=COMPRESSED semistruct_encoding_type = '';

  3. 查询半结构编码配置

    通过 SHOW CREATE TABLE 语句查询半结构化编码配置。示例语句如下：

    SHOW CREATE TABLE t1;

    返回结果如下：

    +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                                                                                                                          |
    +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | t1    | CREATE TABLE `t1` (
    `j` json DEFAULT NULL
    ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPRESSED COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
    +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.003 sec)

    当 semistruct_encoding_type=encoding' 时，查询才会显示该配置项信息，表示开启了半结构化编码功能。

使用半结构化编码可以提高 JSON_VALUE() 函数 条件过滤查询的性能。基于 JSON 半结构化编码技术，seekdb 对 JSON_VALUE 表达式条件过滤查询场景进行了性能优化。由于 JSON 数据已被拆分为子列，系统可以直接基于编码后的子列数据进行过滤，无需还原完整的 JSON 结构，从而显著提升查询效率。

查询示例如下：

-- 查询 name 字段值为 'Devin' 的行
SELECT * FROM t WHERE JSON_VALUE(j_doc, '$.name' RETURNING CHAR) = 'Devin';

字符集注意事项如下：

  • seekdb 的 JSON 使用 utf8_bin 编码。

  • 为确保字符串白盒过滤正常工作，建议设置：

    SET @@collation_server = 'utf8mb4_bin';
    SET @@collation_connection='utf8mb4_bin';

Contents

编辑此页