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

JSON函数

更新时间：
产品详情
我的收藏

本文档详细介绍 Lindorm SQL 中所有 JSON 相关函数的使用方法、参数说明和实际示例。

JSON函数概览

函数分类

函数名

函数说明

JSON 构造函数

json_object

创建 JSON 对象。

json_array

创建 JSON 数组。

JSON 提取函数

json_extract

从 JSON 文档中提取指定路径的值。

json_extract_string

提取指定路径值并转换为字符串类型。

json_extract_long

提取指定路径值并转换为长整型。

json_extract_double

提取指定路径值并转换为浮点数。

JSON 包含检查函数

json_contains

检查 JSON 文档是否包含所有指定的值或对象。

json_contains_any

检查 JSON 文档是否包含指定的值或对象中的任意一个。

JSON 更新函数

json_set

在 JSON 文档中插入或更新数据并返回新的 JSON 文档，相当于json_insert+json_replace。

json_insert

向 JSON 文档中插入数据并返回新的 JSON 文档，仅在字段不存在时插入新字段。

json_replace

在 JSON 文档中替换已存在的数据并返回新的 JSON 文档，仅在字段存在时更新其值。

json_remove

从 JSON 文档中删除由路径指定的数据并返回修改后的 JSON 文档。

json_upsert

语义上同json_set，但能够处理 NULL 值。

函数说明

JSON 构造函数

json_object

创建 JSON 对象。

语法：

json_object(key1, value1, key2, value2, ...)

参数：

  • key: 字符串类型的键名。

  • value: 任意类型的值。

示例：

-- 创建简单对象
SELECT json_object('name', 'Alice', 'age', 25);
-- 结果: {"name": "Alice", "age": 25}

-- 在 INSERT 中使用
UPSERT INTO users (id, data) VALUES (1, json_object('name', 'Charlie', 'city', 'Beijing'));

json_array

创建 JSON 数组。

语法：

json_array(value1, value2, value3, ...)

参数：

  • value: 任意类型的值，可以是标量、对象或数组。

示例：

-- 创建简单数组
SELECT json_array('apple', 'banana', 'orange');
-- 结果: ["apple", "banana", "orange"]

-- 在 INSERT 中使用
UPSERT INTO products (id, tags) VALUES (1, json_array('electronics', 'mobile', 'smartphone'));

JSON 提取函数

json_extract

从 JSON 文档中提取指定路径的值。

语法：

json_extract(json_column, 'path')

参数：

  • json_column: JSON 类型的列或表达式。

  • path: JSON 路径表达式，如 $.field、$.array[0]、$.nested.field。

示例：

-- 提取简单字段
SELECT json_extract('{"name": "Alice", "age": 25}', '$.name');
-- 结果: "Alice"

-- 提取嵌套字段
SELECT json_extract('{"user": {"name": "Bob", "age": 30}}', '$.user.name');
-- 结果: "Bob"

-- 提取数组元素
SELECT json_extract('{"skills": ["Java", "Python"]}', '$.skills[0]');
-- 结果: "Java"

-- 提取整个数组
SELECT json_extract('{"skills": ["Java", "Python"]}', '$.skills');
-- 结果: ["Java", "Python"]

-- where 条件 数字
select * from tb where json_extract(c2, '$.k3.k4') > 5;

-- where 条件 字符串
select * from tb where json_extract_string(c2, '$.k2') = '1';

json_extract_type 类型安全的提取函数

json_extract_string

提取指定路径值并转换为字符串类型。如果对应路径不是字符串。默认会返回 NULL。

SELECT json_extract_string('{"name": "Alice"}', '$.name');
-- 结果: Alice (VARCHAR 类型)

SELECT json_extract_string('{"number": "30"}', '$.name');
-- 结果: NULL。(Number类型不符合)

json_extract_long

提取指定路径值并转换为长整型。如果对应路径不是数字，JDBC会抛错。

SELECT json_extract_long('{"id": 123456789}', '$.id');
-- 结果: 123456789 (LONG 类型)

SELECT json_extract_long('{"id": 123456.789}', '$.id');
-- 抛错

SELECT json_extract_long('{"id": "123456.789"}', '$.id');
-- 抛错

json_extract_double

提取指定路径值并转换为浮点数。如果对应路径不是浮点数，JDBC会抛错。

SELECT json_extract_double('{"id": 12345.56}', '$.id');
-- 结果: 12345.56 (Double 类型)

SELECT json_extract_double('{"id": 12345}', '$.id');
-- 结果: 12345.0 (Double 类型)

SELECT json_extract_double('{"id": "123456.789"}', '$.id');
-- 抛错

以上三个函数是强类型的函数，要求对应PATH对应的元素必须严格匹配指定类型，否则会返回 NULL 或报错。支持集群级别调整为强制转换（不建议），建议在 JSON 数据插入前确定好类型，混合类型可能导致不可预见的逻辑问题。

JSON 包含检查函数

json_contains

检查 JSON 文档是否包含所有指定的值或对象。

语法：

json_contains(target, candidate[, path])

参数：

  • target_json: 必填，JSON文档。

  • candidate_json: 必填，被包含的 JSON 文档。

  • path：可选，路径表达式。

更多参数详情，可参考参数补充说明。

返回值：

  • 1 (true): 包含指定值

  • 0 (false): 不包含指定值

若在 JSON 文档 target_json 中包含了 JSON 文档 candidate_json，则json_contains函数将返回 1，否则返回 0。若提供了 path 参数，则检查由 path 匹配的部分是否包含 candidate_json JSON 文档。

若存在以下的情况， json_contains 函数将返回 NULL。

  • JSON 文档中不存在指定的路径。

  • 任意一个参数为 NULL。

示例：

-- 建表插入数据
CREATE TABLE test_table (id INT, c1 VARCHAR, data JSON, PRIMARY KEY(id));
UPSERT INTO test_table (id, data) VALUES (1, NULL);
UPSERT INTO test_table (id, data) VALUES (2, '{"skills": ["Java", "Python"]}');
UPSERT INTO test_table (id, data) VALUES (3, '{"skills": ["Go", "C"]}');
UPSERT INTO test_table (id, data) VALUES (4, '{"technical_skills": ["Java", "Go", "Rust"]}');
UPSERT INTO test_table (id, data) VALUES (5, '["Java","C++", "JavaScript"]');
UPSERT INTO test_table (id, data) VALUES (6, '{"skills": "Java"}');
UPSERT INTO test_table (id, data) VALUES (7, '["Java", "C#"]');
UPSERT INTO test_table (id, data) VALUES (8, '{"skills": ["Go", "Rust"]}');
-- 两参数默认形式。即默认path为'$'
mysql>  SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]') and id>0 and id<10;
+------+------------------------------+
| id   | data                         |
+------+------------------------------+
|    5 | ["Java","C++", "JavaScript"] |
|    7 | ["Java", "C#"]               |
+------+------------------------------+
2 rows in set (0.01 sec)

-- 三参数形式：检查指定路径
mysql>  SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]', '$.skills') and id>0 and id<10;
+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
+------+--------------------------------+
1 row in set (0.01 sec)

-- 同时包含“Java”和“Go”
mysql>  SELECT id, data FROM test_table WHERE json_contains(data, '["Java","Go"]', '$.skills') and id>0 and id<10;
Empty set (0.02 sec)

-- 数组或者元素包含标量 "Java" 
mysql>  SELECT id, data FROM test_table WHERE json_contains(data, '"Java"', '$.skills') and id>0 and id<10;
+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
|    6 | {"skills": "Java"}             |
+------+--------------------------------+
2 rows in set (0.01 sec)

-- 数组中元素包含数组，且数组中只有一个标量"Java" （id=6对应的path的json文档是标量，标量的子集只能是标量,不能是数组）
mysql>  SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]', '$.skills') and id>0 and id<10;
+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
+------+--------------------------------+
1 row in set (0.01 sec)

-- 检查整个文档是否包含对象
SELECT json_contains('{"a": 1, "b": 2}', '{"a": 1}');
-- 结果: 1

-- 检查指定路径是否包含值
SELECT json_contains('{"a": 1, "b": 2}', '1', '$.a');
-- 结果: 1

SELECT json_contains('{"a": 1, "b": 2}', '1', '$.b');
-- 结果: 0

-- 检查数组包含
SELECT json_contains('{"skills": ["Java", "Python"]}', '"Java"', '$.skills');
-- 结果: 1

参数补充说明

  1. candidate_json是一个JSON文档，需把对应的元素表示为对应的JSON字符串。

    • 包含数字10对应JSON文档： '10'

    • 包含字符串10对应JSON文档： '"10"'

    • 包含数值列表对应JSON文档： '[1,2,3]'

    • 包含字符串列表对应JSON文档 '["10","abc","key"]'

  2. 标量的子集只能是标量。array类型的子集既可以是元素也可以是array。

    JSON类型区分：

    • 标量：String、Number（Integer/Double）、Boolean、Null；

    • 复杂类型：Array 、Object。

    当检查条件是json_contains_any(skills, '["Java"]', '$.technical_skills')，对于$.technical_skills路径的JSON：

    • 若为 {"technical_skills":"Java"}，则返回false （String的子集不能是一个List）。

    • 若为 {"technical_skills":["Java"]} 或 {"technical_skills":["Java","Go"]}，则返回true。

    当检查条件是 json_contains_any(skills, '"Java"', '$.technical_skills') ，对于$.technical_skills路径的JSON：

    • 若为 {"technical_skills":"Java"}，则返回true。

    • 若为 {"technical_skills":["Java"]} 或{"technical_skills":["Java","Go"]} ，则返回true。

json_contains_any

检查 JSON 文档是否包含指定的值或对象中的任意一个。。

语法：

语法同json_contains，区别可参考JSON 包含检查函数对比。

示例：

mysql> select json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]');
+--------+
| EXPR$0 |
+--------+
|      0 |
+--------+
1 row in set (0.02 sec)

mysql> select json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]','$.skills');
+--------+
| EXPR$0 |
+--------+
|      1 |
+--------+
1 row in set (0.03 sec)

-- 默认两个参数形式,即默认path为'$'
mysql> SELECT id, data FROM test_table WHERE json_contains_any(data, '["Java", "Go"]') limit 10;
+------+------------------------------+
| id   | data                         |
+------+------------------------------+
|    5 | ["Java","C++", "JavaScript"] |
|    7 | ["Java", "C#"]               |
+------+------------------------------+
2 rows in set (0.01 sec)

-- 三参数形式
mysql>  SELECT id, data FROM test_table WHERE json_contains_any(data, '["Java", "Go", "Rust"]', '$.skills') limit 10;
+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
|    3 | {"skills": ["Go", "C"]}        |
|    6 | {"skills": "Java"}             |
|    8 | {"skills": ["Go", "Rust"]}     |
+------+--------------------------------+
4 rows in set (0.01 sec)

-- 检查是否包含数组中的任意值
SELECT json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]');
-- 结果: 0
SELECT json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]','$.skills');
-- 结果: 1

-- 在指定路径下检查
SELECT json_contains_any('{"departments": ["Engineering", "Sales"]}', '["Marketing", "Engineering"]', '$.departments');
-- 结果: 1

JSON 更新函数

json_set

在 JSON 文档中插入或更新数据并返回新的 JSON 文档，相当于json_insert+json_replace。

语法：

json_set(json_column, 'path', new_value)

行为特点：

  • 如果指定路径存在，则更新该字段。

  • 如果指定路径不存在，则插入新字段。

  • 如果 JSON 列为 NULL，结果仍为 NULL，不做任何处理。

示例：

-- 更新现有字段
UPSERT INTO test_table (id, data) VALUES (3, '{"name": "Charlie", "age": 30}');
UPDATE test_table SET data = json_set(data, '$.age', 31) WHERE id = 3;
-- 结果: {"name": "Charlie", "age": 31}

-- 插入新字段
UPDATE test_table SET data = json_set(data, '$.department', 'Engineering') WHERE id = 3;
-- 结果: {"name": "Charlie", "age": 31, "department": "Engineering"}

-- JSON 列为 NULL 时（与 json_upsert 的关键区别）
UPSERT INTO test_table (id,c1) VALUES (4,'test');
UPDATE test_table SET data = json_set(data, '$.name', 'David') WHERE id = 4;
-- 结果: NULL

json_insert

向 JSON 文档中插入数据并返回新的 JSON 文档，仅在字段不存在时插入新字段。

语法：

json_insert(json_column, 'path', new_value)

行为特点：

  • 如果指定路径不存在，插入新字段。

  • 如果指定路径已存在，不进行任何操作。

示例：

-- 插入新字段
UPSERT INTO test_table (id, data) VALUES (5, '{"name": "Eve"}');
UPDATE test_table SET data = json_insert(data, '$.age', 28) WHERE id = 5;
-- 结果: {"name": "Eve", "age": 28}

-- 尝试插入已存在的字段（无效果）
UPDATE test_table SET data = json_insert(data, '$.name', 'New Name') WHERE id = 5;
-- 结果: {"name": "Eve", "age": 28} (name 字段保持不变)

json_replace

在 JSON 文档中替换已存在的数据并返回新的 JSON 文档，仅在字段存在时更新其值。

语法：

json_replace(json_column, 'path', new_value)

行为特点：

  • 如果指定路径存在，则更新该字段。

  • 如果指定路径不存在，则不进行任何操作。

示例：

-- 更新现有字段
UPSERT INTO test_table (id, data) VALUES (6, '{"name": "Frank", "age": 35}');
UPDATE test_table SET data = json_replace(data, '$.age', 36) WHERE id = 6;
-- 结果: {"name": "Frank", "age": 36}

-- 尝试更新不存在的字段（无效果）
UPDATE test_table SET data = json_replace(data, '$.city', 'Shanghai') WHERE id = 6;
-- 结果: {"name": "Frank", "age": 36} (没有 city 字段)

json_remove

从 JSON 文档中删除由路径指定的数据并返回修改后的 JSON 文档。

语法：

json_remove(json_column, 'path')

行为特点：

  • 如果指定路径存在，则删除该字段。

  • 如果指定路径不存在，安全操作，无错误。

示例：

-- 删除字段
UPSERT INTO test_table (id, data) VALUES (7, '{"name": "Grace", "temp_field": "to_remove"}');
UPDATE test_table SET data = json_remove(data, '$.temp_field') WHERE id = 7;
-- 结果: {"name": "Grace"}

-- 删除不存在的字段（安全）
UPDATE test_table SET data = json_remove(data, '$.nonexistent') WHERE id = 7;
-- 结果: {"name": "Grace"} (无错误)

json_upsert

语义上同json_set，但能够处理 NULL 值。

语法：

json_upsert(json_column, 'path', new_value)

行为特点：

  • 如果 JSON 列为 NULL，创建新对象 {path: new_value}

  • 如果指定路径存在，则更新该字段。

  • 如果指定路径不存在，则插入新字段。

示例：

-- JSON 列为 NULL 时
CREATE TABLE test_table (id INT, c1 VARCHAR, data JSON, PRIMARY KEY(id));
UPSERT INTO test_table (id,c1) VALUES (1,'test');
UPDATE test_table SET data = json_upsert(data, '$.name', 'Alice') WHERE id = 1;
-- 结果: {"name": "Alice"}

-- 更新现有字段
UPSERT INTO test_table (id, data) VALUES (2, '{"name": "Bob", "age": 25}');
UPDATE test_table SET data = json_upsert(data, '$.age', 26) WHERE id = 2;
-- 结果: {"name": "Bob", "age": 26}

-- 插入新字段
UPDATE test_table SET data = json_upsert(data, '$.city', 'Beijing') WHERE id = 2;
-- 结果: {"name": "Bob", "age": 26, "city": "Beijing"}

更多关于以上JSON 更新函数的区别，可参考JSON 更新函数对比。

函数对比总结

JSON 包含检查函数对比

函数

区别

json_contains

json_contains 的candidate_json的每个元素都必须是target_json的元素，

即candidate_json是target_json的子集。

json_contains_any

json_contains_any 的candidate_json的任意一个元素都必须是target_json的元素，

即candidate_json与target_json有共同元素。

JSON 更新函数对比

函数

NULL 列处理

字段存在

字段不存在

主要用途

json_set

保持 NULL

更新

插入

确定列不为 NULL 时的更新 （对齐MySQL语义）。

json_upsert

创建新对象

更新

插入

通用更新，列为NULL时也会插入。

json_insert

保持 NULL

无操作

插入

仅插入新字段。

json_replace

保持 NULL

更新

无操作

仅更新现有字段。

json_remove

保持 NULL

删除

无操作

删除字段。

函数索引支持

语法

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

  • json_extract_double

column

JSON列的列名。

json_path

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

ASYNC

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

搜索索引JSON函数支持可参考Lindorm JSON类型搜索索引使用手册。

支持的函数

以下 JSON 函数支持创建函数索引：

加速json_extract和json_extract_type相关查询

  • json_extract_string(json_column, 'path')

  • json_extract_long(json_column, 'path')

  • json_extract_double(json_column, 'path')

Lindorm宽表SQL支持为JSON数据类型列中特定路径的数据创建二级索引，但在构建二级索引时，必须明确指定JSON列的json_extract函数类型。

加速json_contains相关查询

json_contains(json_column, 'value', 'path')

目前需要固定value和path（查询条件固定）。

索引创建示例

-- 为 json_contains 创建索引
CREATE INDEX idx_user_role ON test_table (json_contains(data, '"admin"', '$.roles')) INCLUDE(data) SYNC;

-- 为 json_extract_string 创建索引
CREATE INDEX idx_user_city ON test_table (json_extract_string(data, '$.address.city')) INCLUDE(data) SYNC;

-- 为 json_extract_string 创建索引 不冗余
CREATE INDEX idx_user_city ON test_table (json_extract_string(data, '$.address.city'));

-- 为 json_extract_long 创建索引
CREATE INDEX idx_user_age ON test_table (json_extract_long(data, '$.age')) INCLUDE(data) SYNC;

-- 查看索引创建结果
SHOW INDEX FROM test_table;

使用限制

  • 使用JsonPath时务必遵循标准语法。

  • 复杂JSON操作需警惕性能损耗。

  • 对于高频查询路径，建议创建索引以提升响应速度。

常见错误处理

  • 无效 JSON 路径

    illegal json path。业务需要保证JsonPath是合法的JsonPath。参考MySQL关于JsonPath的定义。

  • 无效 JSON 值

    json_contain candidate is not a valid value。需要检查 candidate 是一个合法的Json字符串。

  • 全表扫描警告

    This query may be a full table scan and thus may have unpredictable performance。触发低效全表扫描，需要考虑建立索引加速查询或者加Limit 只过滤部分数据。

建议说明

  1. 选择合适的更新函数

    根据业务需求选择 json_upsert 或 json_set。

  2. 索引规划

    为高频查询的 JSON 路径创建函数索引。

  3. 类型安全

    使用类型特定的提取函数确保数据类型正确，建议不要混用数字和字符型数字，易产生歧义。例如：数字10和字符串"10"。

上一篇：字符串函数下一篇：聚合函数