跳到主要内容

创建 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 列​

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_type 和 freq_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';

Contents

编辑此页