Thanks @wchiquito for pointing me right direction. I solved the problem. Here is how I did it. mysql> select * from t1; +—————————————-+——+ | data | id | +—————————————-+——+ | {“key1”: “value1”, “key2”: “VALUE2”} | 1 | | {“key2”: “VALUE2”} | 2 | | {“key2”: “VALUE2”} | 1 | | {“a”: “x”, “b”: “y”, “key2”: … Read more
With the help of Feras’s comment and some fiddling: SELECT u.user_id, u.user_name, g.user_group_id, g.group_name FROM user u LEFT JOIN user_group g on JSON_CONTAINS(u.user_groups, CAST(g.user_group_id as JSON), ‘$’) This appears to work, let me know if there’s a better way.
[*] MySQL 8.0 provides JSON_TABLE() to help with cases like this. select field_options.* from fields cross join json_table(fields.options, ‘$[*]’ columns( text text path ‘$.text’, value text path ‘$.value’ ) ) as field_options where field_options.value = 1; +——-+——-+ | text | value | +——-+——-+ | Grass | 1 | +——-+——-+ But you have to do this … Read more
If you have MySQL version >= 5.7, then you can try this: SELECT JSON_EXTRACT(name, “$.id”) AS name FROM table WHERE JSON_EXTRACT(name, “$.id”) > 3 Output: +——————————-+ | name | +——————————-+ | {“id”: “4”, “name”: “Betty”} | +——————————-+ Please check MySQL reference manual for more details: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html