MySQL use JSON_CONTAINS with a subquery

Question

I'm trying to make a subquery inside JSON_CONTAINS to have a results like this

SELECT * FROM addresses JSON_CONTAINS(`groups`, '"Client"', '$') ;

This is what I have tried

SELECT * FROM addresses JSON_CONTAINS(
    `groups`, 
    (SELECT u.group FROM users u WHERE u.email = 'example@email.org' LIMIT 1),
    '$'
) ;

But since the subquery returns Client instead of "Client" JSON_CONTAINS doesn't accept the value:

ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0.

Answer 1

You could just concat the double quotes in the subquery:

SELECT * 
FROM addresses 
WHERE JSON_CONTAINS(
    `groups`, 
    (SELECT CONCAT('"', u.group, '"') FROM users u WHERE u.email = 'example@email.org' LIMIT 1),
);

Note that $ is the default value for the third argument of JSON_CONTAINS().

I am quite suspicious about the the subquery:

• don't you want to correlate it with the outer query?

• LIMIT without ORDER BY might not behave consistently