MySQL查询JSON字段checkBoxValue布尔值时报错，请求修正

The immediate issue causing the ER_PARSE_ERROR is that MySQL doesn't recognize == as an equality operator—you need to use a single = instead. Additionally, we need to make sure we're correctly checking the JSON boolean value, since JSON_EXTRACT returns a JSON-type value rather than a plain SQL boolean.

Here's the corrected query, with two robust options depending on how your checkBoxValue is stored:

Option 1: For JSON boolean values (e.g., {"checkBoxValue": false}) #

This uses MySQL's native boolean comparison and fixes the operator issue:

SELECT data FROM Job 
WHERE (? IS NULL OR JSON_EXTRACT(data, '$.address') LIKE ? OR JSON_EXTRACT(data, '$.customerJobNumber') = ? OR JSON_EXTRACT(data, '$.jobId') = ? ) 
AND (JSON_EXTRACT(data, '$.jobDate') IS NULL OR JSON_EXTRACT(data, '$.jobDate') <= ?) 
AND JSON_EXTRACT(data,'$.checkBoxValue') = false 
AND JSON_EXTRACT(data, '$.status') NOT IN (?)

You can also use IS FALSE for even clearer boolean checking:

AND JSON_EXTRACT(data,'$.checkBoxValue') IS FALSE

Option 2: For string-based "false" values (e.g., {"checkBoxValue": "false"}) #

If your JSON stores the value as a string instead of a boolean, adjust the comparison to match the string literal:

SELECT data FROM Job 
WHERE (? IS NULL OR JSON_EXTRACT(data, '$.address') LIKE ? OR JSON_EXTRACT(data, '$.customerJobNumber') = ? OR JSON_EXTRACT(data, '$.jobId') = ? ) 
AND (JSON_EXTRACT(data, '$.jobDate') IS NULL OR JSON_EXTRACT(data, '$.jobDate') <= ?) 
AND JSON_EXTRACT(data,'$.checkBoxValue') = 'false' 
AND JSON_EXTRACT(data, '$.status') NOT IN (?)

Key Notes: #

• Always use = (not ==) for equality checks in MySQL—this is the standard operator for SQL databases.
• If you're using MySQL 8.0 or later, you can simplify the JSON extraction with JSON_VALUE for readability:

  AND JSON_VALUE(data,'$.checkBoxValue') = false -- for boolean values
  

内容的提问来源于stack exchange，提问作者Sahesh