mysql5.5查询json字段_mysql 5.7 json 字段类型查找、修改

修改 json 里的数组字段

mysql> set @json = '{"test": [{"name": "laravel"}, {"name": "symfony"}]}';

Query OK, 0 rows affected (0.00 sec)

mysql> select json_set(@json, '$.test[0].name', "lumen");

+----------------------------------------------------+

| json_set(@json, '$.test[0].name', "lumen") |

+----------------------------------------------------+

| {"test": [{"name": "lumen"}, {"name": "symfony"}]} |

+----------------------------------------------------+

1 row in set (0.00 sec)

select、匹配 json 字段

SELECT JSON_EXTRACT(name, "$.id") AS name

FROM table

WHERE JSON_EXTRACT(name, "$.id") > 3

json 字段名有横杠的处理：给字段名加双引号

select json_set(@json, '$."just-test".name', "lumen");

设置值为数组：使用 json_array 函数

set @json = json_set(@json, '$."dash-test"', json_array(1));

设置值为对象：使用 json_object 函数

mysql> set @json = json_set(@json, '$.set_test[0]', json_object('name', 'awks'));

Query OK, 0 rows affected (0.00 sec)

mysql> select @json;

+-------------------------------------------------------------------------------------------------------+

| @json |

+-------------------------------------------------------------------------------------------------------+

| {"test": [{"name": "laravel"}, {"name": "symfony"}], "set_test": [{"name": "awks"}], "dash-test": []} |

+-------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

更多参考资料：