Why MySQL JSON_EXTRACT Not work in WHERE condition?

Question

I'll try this:

SELECT * FROM `gass_pedan` WHERE json_extract(`cars_info`, '$.driver_id') = 4;

which wrong in the above syntax?, also I'm using:

SELECT * FROM `gass_pedan` WHERE JSON_CONTAINS(`cars_info`, '4', '$.driver_id')

this is my JSON:

{"mxrid":2,"mxrno":"11","driver_id":"4","drname":"carl","cartype":"mixer"}

this is my server information:
[![enter image description here][1]][1]

#3141 - Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0.

Answer 1

if you have empty cars_info values, you need to avoid calling JSON_EXTRACT() on those values. It will ignore NULL values, but empty strings cause an error.

SELECT * 
FROM `gass_pedan` 
WHERE `cars_info` != '' AND json_unquote(json_extract(`cars_info`, '$.driver_id')) = '4';

You also need to use json_unquote(). Otherwise it returns a string with literal " around it, which isn't equal to the number. You can use the ->> shorthand for JSON_UNQUOTE(JSON_EXTRACT(...))

SELECT * 
FROM `gass_pedan` 
WHERE `cars_info` != '' AND `cars_info`->>'$.driver_id' = '4';