mysql json查询 简书_Mysql查询json数据特定key的值

mysql某张表中有一个字段为json格式，假设字段名为properties

{

"ocsp.event.append-timestamp.enable": "true",

"ocsp.streaming.data.filter.expression": "id=e4_json",

"ocsp.event.enable": "true",

"ocsp.schema.field.names": "id",

"ocsp.event.output.numPartitions": "0",

"ocsp.event.json-format.enable": "true",

"ocsp.streaming.field.translation.enable": "false",

"ocsp.event.append-id.enable": "false",

"ocsp.streaming.data.keys": "id",

"ocsp.stream.sql": "SELECT id FROM uuu_5dea34758400_oi5xs0rdaf WHERE id=e4_json",

"ocsp.kafka.topic": "hn_20210216b",

"ocsp.event.period": "{\"period\":\"day\",\"time\":[{\"begin\":{\"d\":\"0\",\"h\":\"03:30:23\"},\"end\":{\"d\":\"0\",\"h\":\"23:20:23\"}}],\"startDate\":\"2021-02-16\",\"endDate\":\"2021-03-28\"}",

"ocsp.event.periodSwitch": "true",

"ocsp.event.output": "5d74080d1ac0"

}

现在需要查询ocsp.stream.sql的值 可以使用json_extract函数。注意如果该key是由点分隔符组成的，则需要用双引号将整个key包起来

select json_extract(properties,'$."ocsp.schema.field.names"') from COMPONENT where type='EVENT' and id='5dea403ed7c0';

json_keys函数可以用来获取json中所有的key字段

同时 select json_keys(properties) from COMPONENT where type='EVENT' and id='5dea403ed7c0';