How to select by condition on JSON column in MySQL?


Problem :

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.



Solution :

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
                }

            }
        }

Mysql Tutorials

Mysql Howto..

PHP / MySQL: How to reduce code to echo Select results (working code)

How insert 2 related records in mysql

How to archive tables in MySQL InnoDB

How to give a special format to a number with auto_increment in MYSQL?

How to convert date in Text box to MySQL DATETIME format

how to get the total row count with mysqli

How to match password username with php mysql with login form

How can I got clear the error while running UPDATE query in MySQL? [duplicate]

How to round only non integer numbers to one decimal place in MySQL

How to check if mysql entry is empty in PhP?

How efficient are Hibernate's interceptors compared to database triggers?

How to check if id exists in another table given table is 30 million records?

How do I search multiple mySQL tables to find one specific row?

how to remote debug Coldfusion AJAX application without CFadmin access?

How to design an index/query to avoid filesort in a simple MySQL query?

how can i create dyanmic chat.js using php and mysql [closed]

How to query a range of columns MYSQL

How do I use the passed value as the default in a dropdown menu?

How to manage “paging” with Solr?

How do i change metronic ajax table to php mysql

How to insert random numbers MySql

How to clear Error in populating list view from mysql? [Info Updated]

How to update mysql fields with Javascript

how to export MySQL database with triggers and procedures?

How to make a dropdown stay when processing different If/Else blocks?

how to insert/retrieve a userDefined Object in DB-MySql?

Spring Hibernate Mysql: how to get object for many-to-one, many-to-many, one-to-many

MySQL: How to query for a column and include information from related columns in the same table?

How can I convert this date format to mysql using script

how to select a highest float value from mysql with active record class codeigniter