mysql json_extract function

Question

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 ?

Barbaros Özhan · Accepted Answer · 2020-07-08 14:36:21Z

4

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

edited Jul 8, 2020 at 14:36

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Jul 8, 2020 at 14:29

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql json_extract function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related