json字段操作
一、创建json索引
ALTER TABLE 表名 ADD INDEX 字段 ((CAST( dataMap->'$.${field}' AS char(225) ARRAY )));
二、JSON数据类型 合并 JSON 值
1.合并数组:
SELECT
JSON_MERGE_PRESERVE( '[1, 2]', '["a", "b", "c"]', '[true, false]' ) AS PRESERVE,
JSON_MERGE_PATCH( '[1, 2]', '["a", "b", "c"]', '[true, false]' ) AS Patch
结果:
Preserve: [1, 2, "a", "b", "c", true, false]
Patch: [true, false]
2.合并对象
SELECT
JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Preserve,
JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch
结果:
Preserve: {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3}
Patch: {"a": 4, "b": 2, "c": 5, "d": 3}
3.需要数组值的上下文中使用的非数组值会自动打包:该值由字符括起来,以将其转换为数组
SELECT
JSON_MERGE_PRESERVE('1', '2') AS Preserve,
JSON_MERGE_PATCH('1', '2') AS Patch
结果:
Preserve: [1, 2]
Patch: 2
4.数组和对象通过合并值来合并数组
SELECT
JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,
JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS Patch\G
结果:
Preserve: [10, 20, {"a": "x", "b": "y"}]
Patch: {"a": "x", "b": "y"}
5.新的JSON对象追加到了名为json_column的JSON数组中
UPDATE table_name SET json_column = JSON_ARRAY_APPEND(json_column, “$”, {“key”: “value”});
三、JSON_CONTAINS函数实现数组查询
SELECT * FROM products WHERE JSON_CONTAINS(tags, '"tag1"');
JSON_CONTAINS(associatedMap->>'$.${item.searchCriteria}', '"${tag}"')