CODEWITHSUNDEEP
mysqljson
Cabrail Kerimov
Cabrail Kerimov

Reputation: 129

mysql json_extract function

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 ?

Upvotes: 3

Views: 1561

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

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')

Upvotes: 4

Related Questions