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
withoutORDER BY
might not behave consistently