JSON函數 - Lindorm

本文檔詳細介紹 Lindorm SQL 中所有 JSON 相關函數的使用方法、參數說明和實際樣本。

JSON函數概覽

函數分類	函數名	函數說明
JSON 建構函式	json_object	建立 JSON 對象。
JSON 建構函式	json_array	建立 JSON 數組。
JSON 提取函數	json_extract	從 JSON 文檔中提取指定路徑的值。
	json_extract_string	提取指定路徑值並轉換為字串類型。
	json_extract_long	提取指定路徑值並轉換為長整型。
	json_extract_double	提取指定路徑值並轉換為浮點數。
JSON 包含檢查函數	json_contains	檢查 JSON 文檔是否包含所有指定的值或對象。
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

參數補充說明

candidate_json是一個JSON文檔，需把對應的元素表示為對應的JSON字串。
- 包含數字10對應JSON文檔： '10'
- 包含字串10對應JSON文檔： '"10"'
- 包含數值列表對應JSON文檔： '[1,2,3]'
- 包含字串列表對應JSON文檔 '["10","abc","key"]'
標量的子集只能是標量。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 只過濾部分資料。

建議說明

選擇合適的更新函數
根據業務需求選擇 json_upsert 或 json_set。
索引規劃
為高頻查詢的 JSON 路徑建立函數索引。
型別安全
使用類型特定的提取函數確保資料類型正確，建議不要混用數字和字元型數字，易產生歧義。例如：數字10和字串"10"。

JSON函數概覽

函數說明

JSON 建構函式

json_object

json_array

JSON 提取函數

json_extract

json_extract_type 型別安全的提取函數

json_extract_string

json_extract_long

json_extract_double

JSON 包含檢查函數

json_contains

json_contains_any

JSON 更新函數

json_set

json_insert

json_replace

json_remove

json_upsert

函數對比總結

JSON 包含檢查函數對比

JSON 更新函數對比

函數索引支援

文法

參數說明

支援的函數

加速json_extract和json_extract_type相關查詢

加速json_contains相關查詢

索引建立樣本

使用限制

常見錯誤處理

建議說明

加速`json_extract`和`json_extract_type`相關查詢

加速`json_contains`相關查詢