Mysql Where Condition on Json Type field

Question

I am very much struggling in MYSQL when it comes to query JSON type column, this time I need to put where condition on a JSON type field.

JSON type value in my user table is like this: {"id": ["2", "4"]}

When I query a SELECT on my table like:

SELECT json_extract(gids, '$.id') from users

Its outputs like this:

["2", "4"]

I am required to write a query where I need to get all users where id=2

I have tried somthing like this:

SELECT *
FROM users
WHERE json_extract(gids, '$.id')="2"

But its not working, Please help on this as I am stuck here.

Thanks

Hey, I am getting [42000][1305] FUNCTION <DBName>.JSON_OVERLAPS does not exist, any suggestions I am having mysql 5.7 in both local and server — CodeEdge
– CodeEdge, Commented May 22, 2020 at 19:22

Shailesh Dudhat · Accepted Answer · 2020-05-22 19:28:38Z

2

you can try using JSON_SEARCH with JSON_EXTRACT

JSON_SEARCH will search data inside array.

SELECT *
FROM users
WHERE JSON_SEARCH(JSON_EXTRACT(test, '$.id'), 'one', "1") IS NOT NULL

you can check https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-search this link for reference.

answered May 22, 2020 at 19:28

Shailesh Dudhat

1244 bronze badges

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

2 Comments

CodeEdge Over a year ago

have tested this and its working fine with one Id is it possible to match multiple ids like JSON_SEARCH(JSON_EXTRACT(test, '$.id'), 'one', "1,2,3") IS NOT NULL

Shailesh Dudhat Over a year ago

haven't tried that and not sure if that is possible will check about that if i can found something then will let you know about that

Collectives™ on Stack Overflow

Mysql Where Condition on Json Type field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related