1

I need to update a JSON's some of the nested values using Laravel Eloquent, But didn't get the exact result as I wanted as optimized. The below json needs to be updated frequently. I had a working solution to this, But needs to fine tune the solution.

{
  "a": {
    "b": 12,
    "c": 792,
    "d": 45,
    "e": {
      "1": {
        "name": "Guna",
        "city": "city1",
        "dob": 2000
      },
      "2": {
        "name": "Raj",
        "city": "city2",
        "dob": 2001
      },
      "3": {
        "name": "Dhamu",
        "city": "city3",
        "dob": 1985
      },
      "4": {
        "name": "Bhavi",
        "city": "city5",
        "dob": 1985
      }
    }
  }
}

Tried Code

$c_array=Json::decode($this->company->details, 1);
$c_array['a']['Player'][$p[0]]['st']=$p[1];
$c_array['a']['e'][1]['name'] = 'Bheem';
$c_array['a']['e'][1]['city'] = 'City10';
$c_array['a']['e'][2]['name'] = 'Dhanu';
$c_array['a']['e'][2]['city'] = 'City5';
$this->company()->update(['details' => Json::encode($c_array)]);

The above code is working fine, but the whole json is updated when checked the query. I need to update only the individual keys that needs to be updated.

We can update the code using Query Builder (DB::raw) with the following,

update ultimate_squad set squad=JSON_SET(details,'$.a.e."1".name','Bheem','$.a.e."1".city','City10','$.a.e."2".name','Dhanu','$.a.e."2".city','City5') where c_id=1;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Gunaseelan
  • 2,494
  • 5
  • 36
  • 43
  • 2
    Every question about using JSON in MySQL I've seen on Stack Overflow can be answered, "this would be both easier and more efficient if you didn't use JSON." – Bill Karwin Jan 10 '21 at 19:13
  • @BillKarwin I have the above solution to update JSON values, but needs to fine tune – Gunaseelan Jan 10 '21 at 19:16
  • 1
    Yes, I understood that. You already wrote nearly the same words above. – Bill Karwin Jan 10 '21 at 19:18
  • 1
    By implementing a complex data model in JSON instead of normal rows and columns, you are creating an [Inner-Platform Effect](https://en.wikipedia.org/wiki/Inner-platform_effect): creating "a system so customizable as to become a replica, and often a poor replica, of the software development platform they are using." If you want it to be more fine-tuned, then the first thing you must do is stop using JSON. Instead, use normal rows and columns. – Bill Karwin Jan 10 '21 at 19:20
  • 2
    You may say, "but it has to be flexible." Well, you can't make it so flexible _and_ optimized at the same time. – Bill Karwin Jan 10 '21 at 19:22
  • Can you make the frequently-changed values into columns, leaving the rest in a "flexible" JSON? – Rick James Feb 07 '21 at 02:46

2 Answers2

2

Yes, using Eloquent you can update a single property if using JSON cast:

class User extends Model
{
    protected $casts = [
        'options' => 'array',
    ];
}

$user = User::find(1);

$user->update(['options->enabled' => true]);

https://laravel.com/docs/8.x/eloquent-mutators#array-and-json-casting

If you need mass-assignment, you should also add it to the fillable

protected $fillable = [
    'options->enabled',
];

https://laravel.com/docs/8.x/eloquent#mass-assignment-json-columns

Also, you can create a more elaborated parsing for your JSON field, by using a predefined class or also creating your own custom class implementing Illuminate\Contracts\Database\Eloquent\Castable that returns your own logic.

use Illuminate\Database\Eloquent\Casts\AsArrayObject;

protected $casts = [
    'options' => AsArrayObject::class,
];

https://laravel.com/docs/8.x/eloquent-mutators#array-object-and-collection-casting

nelson6e65
  • 917
  • 11
  • 14
-2

You can create temptable in mysql and store whole json in there Then run update query with specefic key that you want. Read references below:

Create a temporary table in a SELECT statement without a separate CREATE TABLE

How to create TEMPORARY table in laravel

mostafa shahroudi
  • 32
  • 3