Somnath Muluk Somnath Muluk - 3 months ago 6
JSON Question

How to select by condition on JSON column in MySQL?

How to write query for selecting data by where condition on column having JSON array?
Suppose I have added user_name, user_role in ci_sessions. and user_data is JSON array.

FROM `ci_sessions` where user_data[user_role]='admin';

This where condition is needs to be designed. I require data with having user_role "admin".

Update: To check user_role "admin" is main objective of where condition.

Is there any way to add where condition as

Update: This is possible in PostgresSQL DB.


I was keep on waiting for this type of query but I failed to do so. That's why I had made combination of sql and php.

  1. I have got results from all ci_sessions table.
  2. Then I checked in result by decoding field of JSON arraY. aND it's working fine.
SELECT * FROM `ci_sessions`;
foreach ($result as $row) {
            foreach (unserialize($user_data) as $k => $v) {
                $final[] = array('key' => $k, 'value' => $v);
                if($k=='user_role' && $v='admin') {
//make array of admin information required to show