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?
i.e.
Suppose I have added user_name, user_role in ci_sessions. and user_data is JSON array.

SELECT *
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
user_data[user_role]
or
user_data->user_role
?

Update: This is possible in PostgresSQL DB.

Answer

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) {
            $user_data=$row->user_data;
            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
                }

            }
        }
Comments