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

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 :

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

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

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

Click here to upgrade the MySQL version from 5.5 to 5.7

Phone: (+91) 8800417876
Noida, 201301
Attention Required! | Cloudflare

Sorry, you have been blocked

You are unable to access ressim.net

Why have I been blocked?

This website is using a security service to protect itself from online attacks. The action you just performed triggered the security solution. There are several actions that could trigger this block including submitting a certain word or phrase, a SQL command or malformed data.

What can I do to resolve this?

You can email the site owner to let them know you were blocked. Please include what you were doing when this page came up and the Cloudflare Ray ID found at the bottom of this page.