跳到主要内容

创建 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 KEYFOREIGN KEYUNIQUE KEY,但是可以添加 NOT NULLCHECK 约束。

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

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

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

示例

创建/修改 JSON 列

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

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

obclient> 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

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

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

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

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

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

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

obclient> 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

obclient> SELECT c->>"$.name" AS name FROM jn WHERE g <= 2;
+-------+
| name |
+-------+
| Fred |
| Wilma |
+-------+
2 rows in set

obclient> EXPLAIN SELECT c->>"$.name" AS name FROM jn WHERE g <= 2\G
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------
|0 |TABLE SCAN|jemp(idx1)|2 |92 |
=========================================

Outputs & filters:
-------------------------------------
0 - output([JSON_UNQUOTE(JSON_EXTRACT(jemp.c, '$.name'))]), filter(nil),
access([jemp.c]), partitions(p0)

1 row in set

使用半结构化编码

seekdb 支持建表时开启半结构化编码,主要由表级别参数 SEMISTRUCT_PROPERTIES 控制,同时也要设置表的 ROW_FORMAT=COMPRESSED,否则会报错:

  • SEMISTRUCT_PROPERTIES=(encoding_type=encoding) 时,表被认为是半结构化表,即整张表中的 JSON 列都会启用半结构化编码;
  • SEMISTRUCT_PROPERTIES=(encoding_type=none) 时,表被认为是结构化表。
  • 同时,还支持通过 freq_threshold 参数设置频率阈值。
  • 当前 encoding_typefreq_threshold 是 Online DDL 语法,不支持 Offline DDL 语法。
  1. 启用半结构化编码

    提示

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

    tab 建表时启用的示例

    CREATE TABLE t1(  j json) 
    ROW_FORMAT=COMPRESSED
    SEMISTRUCT_PROPERTIES=(encoding_type=encoding, freq_threshold=50);

    更多语法说明请见 CREATE TABLE

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

    CREATE TABLE t1(j json);
    ALTER TABLE t1 SET ROW_FORMAT=COMPRESSED SEMISTRUCT_PROPERTIES = (encoding_type=encoding, freq_threshold=50);

    更多语法说明请见 ALTER TABLE

    一些修改限制:

    • 修改频繁列阈值,如果没开启半结构化不报错,但是不生效。
    • 在旁路导入或表被锁定时,freq_threshold 参数无法修改。
    • 修改其中一个子配置项,不影响其余的子配置项。
  2. 关闭半结构化编码

    SEMISTRUCT_PROPERTIES 设置为 (encoding_type=none) 时,将关闭半结构化编码。此操作不会影响已有的数据,仅对后续写入的数据生效。以下是关闭半结构化编码的示例:

    ALTER TABLE t1 SET ROW_FORMAT=COMPRESSED SEMISTRUCT_PROPERTIES = (encoding_type=none);
  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 SEMISTRUCT_PROPERTIES=(ENCODING_TYPE=ENCODING, FREQ_THRESHOLD=50) |
    +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set

    SEMISTRUCT_PROPERTIES=(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';