my mysql column named json:
{"myarray":[ { "users":"82,191", type":"3" } ]}
this is above table column json i want to find user with id 191. 82 working fine. but 191 not found.
my sql query is:
SELECT id
FROM tablename
WHERE JSON_EXTRACT(json, CONCAT('$.myarray[0].users') ) IN (82) --this is working
SELECT id
FROM tablename
WHERE JSON_EXTRACT(json, CONCAT('$.myarray[0].users') ) IN (191) --but this is not working. nothing fetched. empty results.
how can i fetch results if json has also 191 ?
JSON_EXTRACT in your case returns quoted string "82,191" so for search you can use combination JSON_UNQUOTE with FIND_IN_SET like:
SELECT id
FROM tablename
WHERE FIND_IN_SET(82, JSON_UNQUOTE(
JSON_EXTRACT(json, '$.myarray[0].users')
)) > 0;
Or Directly ( without Unquoting )
SELECT id
FROM tablename
WHERE FIND_IN_SET (82, json ->> '$.myarray[0].users')