JSON_SEARCH查询报Warning 3156错误求助：无法将JSON值转换为INTEGER

The warning you're seeing happens because JSON_SEARCH returns a string representing the path to the matching JSON value (e.g., "$.feeds[0].trait") when it finds a match, not a boolean or integer. When you use this string directly in the WHERE clause, MySQL tries to cast it to an integer to evaluate it as a truthy/falsy value—and since the path string isn't a valid integer, you get the 3156 warning.

Here are two straightforward fixes:

Solution 1: Check for Non-Null Result from JSON_SEARCH #

Instead of using the JSON_SEARCH result directly, explicitly check if it returns a non-null value (indicating a match was found):

SELECT DISTINCT globalusers.id 
FROM globalusers 
WHERE JSON_SEARCH(dynamic_attributes->>'$.feeds[*].trait', 'all', 'Bankrupt') IS NOT NULL;

This works because JSON_SEARCH returns NULL when no match exists. By checking for IS NOT NULL, we avoid forcing MySQL to cast the path string to an integer.

Solution 2: Use JSON_CONTAINS (More Idiomatic for Existence Checks) #

For checking if a value exists in a JSON array, JSON_CONTAINS is a better fit—it returns a boolean (1 for match, 0 otherwise) which plays nicely with the WHERE clause:

SELECT DISTINCT globalusers.id 
FROM globalusers 
WHERE JSON_CONTAINS(dynamic_attributes, '{"trait": "Bankrupt"}', '$.feeds');

This query directly checks if any element in the feeds array has a trait equal to "Bankrupt", eliminating the casting issue entirely.

Additional Notes #

• The dynamic_attributes->>'$.feeds[*].trait' syntax in your original query extracts all trait values as a JSON array, but JSON_SEARCH can also work with the full JSON column directly if you prefer (e.g., JSON_SEARCH(dynamic_attributes, 'all', 'Bankrupt', NULL, '$.feeds[*].trait')).
• Both solutions will correctly return the id of users where the Bankrupt trait exists in their feeds array.

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