Somnath Muluk Somnath Muluk -4 years ago 85
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.

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download