Laravel- how to update JSON data type column in MySQL 5.7

In this tutorial, I will tell you how to update json data for a specific key exist in json object in MySQL 5.7

Laravel provides the way to easily modify the value of specific key in JSON column.

As per Normalization technique, every column should have a single value, but in some scenario, there is need to hold the json data.

Note : Do not save json data in a column which is regularly used for searching because JSON fields can not be indexed.

Let’s have a table with JSON field :

Create a table for user details which hold address in json format.

CREATE TABLE 'members' (
  'id' int(11) unsigned NOT NULL AUTO_INCREMENT,
  'name' varchar(200) NOT NULL,
  'address' json DEFAULT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB;

In address field data will be stored in json object :

{
    "id": 1,
    "name": "Ajay",
    "address": {
        "city": "Noida",
        "country": "India"
    }
}

Ok, Let’s update the city in address field using Laravel DB query builder :

DB::table('members')
->where('id',1)
->update(['address->city'=>'Varanasi']);

DB::table('members')
    ->where('id', 1)
    ->update(['address->city' => 'Varanasi']);

Now you can get the list of members from Varanasi city :

$members= DB::table('members')
             ->where('address->city','Varanasi')
             ->get();

$members = DB::table('members')
    		 ->where('address->city', 'Varanasi')
    		 ->get();

Click here to upgrade the MySQL version from 5.5 to 5.7