Skip to content
Advertisement

MySQL use JSON_CONTAINS with a subquery

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 subery query 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.

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement