mysql之JSON数据操作

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}"')