mybatis sql错误?

Question

<select id="test" resultType="java.util.Map">
    select JSON_EXTRACT(all_info, '\$[0].motor_controller_temperature')
    from data_20230217
</select>

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: The 'JSON_EXTRACT(all_info, '$' property of null is not a List or Array.

关键是这个sql

select JSON_EXTRACT(all_info, '\$[0].motor_controller_temperature')
       from data_20230217

用navicat运行没有错误

Answer 1

你反斜杠是什么意思,你应该直接就行了

select JSON_EXTRACT(all_info, '$[0].motor_controller_temperature')

Answer 2

你是要拿all_info这个json里面的哪个key?如果key就是motor_controller_temperature那就这么写:

select JSON_EXTRACT(all_info, '$.motor_controller_temperature')
       from data_20230217

如果是motor是一个数组,你要拿的是motor这个数组里面第一个对象的key为controller_temperature里面的值,那就要套两层JSON_EXTRACT,注意索引是从1开始而不是0:

json_extract( json_extract(all_info,'$.motor[1]'),'$.controller_temperature')

Answer 3

增加一个别名就好了，mybatis的原因