文档

JSON数据类型

更新时间:

Lindorm宽表引擎支持在建表、插入数据和更新数据时使用JSON数据类型。JSON(JavaScriptObject Notation)是一种可以在多种语言之间进行数据格式交换的数据类型。JSON数据的格式为键值对,结构清晰,语法易读,同时也方便前后端的数据传输。

适用引擎

JSON数据类型仅适用于宽表引擎。

前提条件

宽表引擎为2.6.2及以上版本。如何查看或升级当前版本,请参见宽表引擎版本说明升级小版本

使用限制

Lindorm宽表的主键列不支持JSON数据类型。

DDL

您可以在创建表或者修改表(新增列时)语句中指定相关列为JSON数据类型。

  • 创建表语句示例如下,相关语法请参见CREATE TABLE

    CREATE TABLE tb (p1 INT, c1 VARCHAR, c2 JSON, PRIMARY KEY(p1));
  • 修改表(新增c3列)语句示例如下,相关语法请参见ALTER TABLE

    ALTER TABLE tb ADD c3 JSON;
    说明

    增加列时,数据表不会被锁定,DML请求可以正常进行。

上述示例中,表tb的主键列为p1且数据类型为INT。c1为非主键列且数据类型为VARCHAR。c2为非主键列且数据类型为JSON。c3为非主键列且数据类型为JSON。通过以下语句查看tb的表结构。

DESCRIBE tb;

返回结果如下:

+--------------+---------------------+------------------------+---------+----------------+------------+
| TABLE_SCHEMA |     TABLE_NAME      |      COLUMN_NAME       |  TYPE   | IS_PRIMARY_KEY | SORT_ORDER |
+--------------+---------------------+------------------------+---------+----------------+------------+
| default      | tb                  | p1                     | INT     | true           | ASC        |
| default      | tb                  | c1                     | VARCHAR | false          | none       |
| default      | tb                  | c2                     | JSON    | false          | none       |
| default      | tb                  | c3                     | JSON    | false          | none       |
+--------------+---------------------+------------------------+---------+----------------+------------+

DML

以下内容介绍对JSON数据类型的列进行数据写入、读取和删除。

UPSERT

通过以下三种方式写入JSON数据。如果在JSON列中写入的数据不是JSON对象或者JSON字符串,写入过程中会报错。Lindorm宽表SQL提供的json_objectjson_array函数将写入的数据转换为JSON对象。

  • 直接写入JSON字符串。列举以下两种写入方式。

    • 使用Statement()方式写入SQL,把JSON格式的字符串写入JSON列。

      Connection conn = DriverManager.getConnection("Lindorm URL", properties);
      Statement stmt = conn.createStatement();
      String jsonStr1 = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}"; 
      String upsertSQL = "UPSERT INTO tb(p1, c1, c2) VALUES(1, '1', '"+ jsonStr1 + "')"; 
      //返回写入数据条数
      int ret = stmt.executeUpdate(upsertSQL);
    • 使用PrepareStatement()方式写入SQL,先进行SQL预处理再为该SQL模板的参数指定参数值。

      String jsonStr1 = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}";
      //写入的SQL模板
      String upsertSQL = "UPSERT INTO tb(p1, c1, c2) VALUES(1, '1', ?)";
      PreparedStatement preStmt = conn.prepareStatement(upsertSQL);
      //将JSON字符串写入JSON列
      preStmt.setString(1, jsonStr1);
      int ret = stmt.executeUpdate();
  • 使用json_object函数将函数中的数据按照写入顺序转换为key-value形式的JSON对象,再把JSON对象写入JSON列。

    String upsert = "UPSERT INTO tb(p1,c1,c2) VALUES(2,'2',json_object('k1', 2, 'k2', '2'))";

    如果使用Lindorm宽表SQL写入上述数据时,请执行以下语句。

    UPSERT INTO tb(p1,c1,c2) VALUES(2,'2','{"k1":2,"k2":"2"}');
  • 使用json_array函数将函数中的数据按照写入顺序转换为数组形式JSON对象,再把JSON对象写入JSON列。

    String  upsert = "UPSERT INTO " + tableName + "(p1,c1,c2) VALUES(3,'3', json_array(1, 2, json_object('k1', 3, 'k2', '3')))";

    如果使用Lindorm宽表SQL写入上述数据时,请执行以下语句。

    UPSERT INTO tb(p1,c1,c2) VALUES(3,'3','[1,2,{"k1":3,"k2":"3"}]');

结果验证

执行以下语句,可以验证数据写入结果。

SELECT * FROM tb;

SELECT

在查询JSON列的数据时,需要使用json_extract函数返回JSON列的值或者对JSON列的值进行条件过滤。

在Lindorm宽表SQL和MySQL中json_extract函数的用法类似。查询操作中json_extract函数可以在SELECT子句中或者WHERE子句中使用。

  • json_extract函数在SELECT子句中使用时,表示获取JSON列上具体的值并返回给用户。示例如下:

    • 写入的JSON列数据为"{\"k1\":1}"

      String json = "{\"k1\":1}";
      //SELECT子句中使用json_extract函数,表示返回c2列中k1键的值,并将返回结果的列名设置为j
      String select = "select p1, c1, c2, json_extract(c2, '$.k1') j from tb where p1 = 1";
      ResultSet resultSet = stmt.executeQuery(select);
      resultSet.next();
      String resultC2 = resultSet.getString(c2);
      //resultC2等于c2列
      String resultC2k1 = resultSet.getString("j");
      //resultC2k1等于c2列中k1键的值,结果为1
    • 写入的JSON列数据为"{\"k1\":2,\"k2\":\"2\"}"

      String json ="{\"k1\":2,\"k2\":\"2\"}";
      //SELECT子句中使用json_extract函数,表示返回c2列中k2键的值,并将返回结果的列名设置为j
      String select =  "select p1, c1, c2, json_extract(c2, '$.k2') j from tb where p1 = 2";
      ResultSet resultSet = stmt.executeQuery(select);
      resultSet.next();
      String resultC2 = resultSet.getString(c2);
      //resultC2等于c2列
      String resultC2k1 = resultSet.getString("j");
      //resultC2k1等于c2列中k2键的值,结果为2
    • 写入的JSON列数据为"[1,2,{\"k1\":3,\"k2\":\"3\"}]"

      String json ="[1,2,{\"k1\":3,\"k2\":\"3\"}]";
      //SELECT子句中使用json_extract函数,表示返回c2列中JSON数组第2个index上的k2键的值,并将返回结果的列名设置j
      String "select json_extract(c2, '$[2].k2') j from  tb where p1 = 3";
      ResultSet resultSet = stmt.executeQuery(select);
      resultSet.next();
      String resultC2 = resultSet.getString(c2);
      //resultC2等于c2列
      String resultC2k1 = resultSet.getString("j");
      //resultC2k1等于JSON数组上index为2的JSON对象以k2为键的值,结果为3
  • json_extract函数在WHERE子句中使用时,表示获取JSON列上具体的值并进行条件过滤。如果json_extract函数在WHERE子句中涉及数据比较和筛选时,不同数据类型之间比较数据类型的等级,比较方式与MySQL的比较方式相同,具体请参见The JSON Data Type

    • 写入的JSON列数据为"{\"k1\":2,\"k2\":\"2\"}"

      String json =  "{\"k1\":2,\"k2\":\"2\"}";
      //WHERE子句中使用json_extract函数,表示返回c2列中k2键的值大于0的数据
      String select = "select p1, c1, c2 from tb where where p1 >= 1 and p1 < 4 and json_extract(c2, '$.k2') > '0'";
      ResultSet resultSet = stmt.executeQuery(select);
      resultSet.next();
      String resultC2 = resultSet.getString(c2);
      //resultC2等于c2列
    • 写入的JSON列数据为"{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}"

      String json = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}";
      //WHERE子句中使用json_extract函数,表示返回c2列的指定路径'$.k2.k3.k4'的值大于4的数据
      String select = "select * from tb where p1 >= 4 and p1 < 6 and json_extract(c2, '$.k2.k3.k4') > 4";
      ResultSet resultSet = stmt.executeQuery(select);
      resultSet.next();
      String resultC2 = resultSet.getString(c2);
      //resultC2等于c2列
    • 写入的JSON列数据为"[1,2,{\"k1\":3,\"k2\":\"3\"}]"

      String json = "[1,2,{\"k1\":3,\"k2\":\"3\"}]";
      //WHERE子句中使用json_extract函数,表示返回c2列中JSON数组第2个index上的k2键的值大于0的数据。
      String select = "select * from  tb where p1 >= 1 and p1 < 4 and json_extract(c2, '$[2].k2') > '0'";
      ResultSet resultSet = stmt.executeQuery(select);
      resultSet.next();
      String resultC2 = resultSet.getString(c2);
      //resultC2等于c2列

UPDATE

重要

执行UPDATE的路径必须是map格式。例如:c2列中k1路径的值为2,则不允许更新k1路径的值;c2列中k1路径的值为{"k2":"value"},允许更新k1路径的值。您可以通过UPSERT INTO语句,以正确的格式覆盖原来的值,再进行UPDATE操作。

在更新JSON列中指定路径的数据时,需要使用UPDATE语法,目前JSON列支持以下操作:

  • JSON_SET:更新JSON列中指定路径的值或者新增不存在JSON列的路径以及路径上的值。

    例如:如果c2列中存在k1.k2路径,那么将k1.k2路径的值更新为value。如果c2列中不存在k1.k2路径,则新增k1.k2路径并将值设置为value。新增k1.k2路径时需要确保存在k1路径。

    UPDATE tb SET c2 = JSON_SET(c2, '$.k1.k2', 'value') WHERE p1 = 2;
  • JSON_INERT:插入不存在的JSON列路径上的值。

    例如:如果c2列中不存在k1.k2路径,则在k1.k2路径上插入值nvalue,您需要确保存在k1路径。

    UPDATE tb SET c2 = JSON_INSERT(c2 ,'$.k1.k2' ,'nvalue') WHERE p1 = 2;
  • JSON_REPLACE:更新已存在的JSON列路径上的值。

    例如:如果c2列中存在k1路径,那么将k1路径的值更新为nvalue

    UPDATE tb SET c2 = JSON_REPLACE(c2 ,'$.k1' ,'nvalue') WHERE p1 = 2;
  • JSON_REMOVE:删除已存在的JSON列路径和路径上的值。

    例如:如果c2列中存在k1路径,那么删除k1路径和路径的值。

    UPDATE tb SET c2 = JSON_REMOVE(c2 , '$.k1') WHERE p1 = 2;

结果验证

执行以下语句,可以验证数据修改结果。

SELECT * FROM tb;

构建二级索引

Lindorm宽表SQL支持为JSON数据类型列中指定路径的数据构建二级索引,但是在构建二级索引时需要指定JSON列的json_extract函数类型。

语法

create_index_statement ::=  CREATE INDEX [ index_name ]
                                ON table_name '(' index_identifier ')'
                              [INCLUDE include_identifier]
                              [ASYNC]
                                 [ index_options ]
index_identifier       ::=  '('json_extract_type(column, json_path)')'
include_identifier   ::= '('column_name1,...,column_namen ')'

参数

参数

描述

index_name

索引表名。

table_name

宽表名。

json_extract_type

通过json_extract_type从JSON列中提取对应的数据类型的字段作为二级索引的,如果数据类型不匹配则不构建二级索引。支持以下函数类型:

  • json_extract_string

  • json_extract_long

column

JSON列的列名。

json_path

JSON列的路径,用于提取JSON列指定路径的值。

ASYNC

异步构建索引,不添加ASYNC表示同步构建索引。

示例

为c3列中k1.k2路径的数据(数据类型为LONG)构建二级索引,同时指定索引表的压缩算法为ZSTD。如果数据类型不是LONG,则不进行构建操作。

CREATE INDEX idx1 ON tb(json_extract_long(c2, '$.k1.k2')) INCLUDE(c1,c3) ASYNC 'COMPRESSION'='ZSTD';

结果验证

执行以下语句,查看索引创建结果。

SHOW INDEX FROM tb;
  • 本页导读 (1)
文档反馈