AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生多模数据库 Lindorm 开发参考 SQL参考 数据类型 JSON数据类型

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_object和json_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;
上一篇：空间数据类型下一篇：BLOB数据类型（邀测中）
文档推荐