我有一个这样的表,我想在字段类型json(array(中获取值,可以吗? 结果 attrs->>'$.target' == 1000 !
id | name | attrs |
1 | me | {"target":["1000", "2000"]} |
您可以使用数组索引为方法设置路径JSON_EXTRACT
:
SELECT JSON_EXTRACT(attrs, "$.target[0]") as test FROM tableName
where JSON_EXTRACT(attrs, "$.target[0]") = '1000'
或更灵活的解决方案:
SELECT * FROM tableName
where JSON_EXTRACT(attrs, JSON_UNQUOTE(JSON_SEARCH(attrs,'all','1000'))) = '1000'