mysql json字符串查询

注意一：字段类型为varchar、char、text等字符类型

注意二：内容为json

注意三：mysql服务器版本为5.7及以上

语法：select * from 表名 where 字段->'$.属性'='查询内容'

select * from table1 where col1->'$.name'='xiaoming'

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'

可以使用JSON_EXTRACT函数查询json字段或者json字符串类型的text字段中json对象的某个属性值，注意层级需要用.号连接，例如： SELECT id, JSON_EXTRACT(content, '$.all_result.quality_result.total_score') AS total_score FROM tableName 其中，content是表的字段，里面存放json格式字符串， $.all_result.quality_result.total_score表示：content字段的json字符串中all_result的属性下quality_result属性的总分数total_score。 content存储的内容： { "return_code": "200", "return_info": "处理成功", "all_result": { "quality_result": { "quality_items": { "数学": 5.0, "语文": 5.0, "英语": 0.0 }, "total_score": 10.0 } } }

---