MYSQL解析json字符串

1.数据库存储数据

[{"key":"禁口食材","value":"葱，香菜"},{"key":"用户病症","value":"低血糖"}]

2.读出JSON数组中的所有KEY

SELECT
	JSON_EXTRACT(user_features,'$[*].key')
FROM
	customer_info
WHERE
	customer_id = '35523232'

3.读出JSON数组中的所有value

SELECT
	JSON_EXTRACT(user_features,'$[*].value')
FROM
	customer_info
WHERE
	customer_id = '35523232'

4.读出某个key为XX的值,并以字符串展示

SELECT
	JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(user_features,'$[*].value'), JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(user_features,'$[*].key'), 'one', 'XX')))) userFeatures
FROM
	customer_info
WHERE
	customer_id = '35523232'