MySql Update Query to insert JSON values

Question

In MySql I have two tables: staff and certifications. There is a one-to-many relationship where staff.pk = certifications.fk.

For each staff member, I need to insert their multiple certifications.name values into a JSON column in the staff table.

I also need to be able to read this as an array cast in a Laravel - does that mean it needs to be a JSON array, rather than a JSON object?

UPDATE:

I need to do this as a batch process, since I have many thousands of records to process. That's why I am concencentrating on the raw SQL. Instantiating Eloquent models would not work from a performance perspective.

May be this could helps: laravel.com/docs/5.6/eloquent-mutators#array-and-json-casting — Dharma Saputra
– Dharma Saputra, Commented Jun 26, 2018 at 9:26
you have already one to many relationship between staff and certifications then why do you want to store certifications in staff table ? — rkj
– rkj, Commented Jun 26, 2018 at 9:31
@rkj, I need to do the extraction because I am creating a temporary data cache from dozens of tables and passing it to a front-end app. — DatsunBing
– DatsunBing, Commented Jun 26, 2018 at 9:39

rkj · Accepted Answer · 2018-06-26 10:16:46Z

1

you can use Accessors & Mutators

namespace App;
use Illuminate\Database\Eloquent\Model;

class Staff extends Model
{
    //assuming you have certificates column in staff table to store json data

    public function setCertificatesAttribute($certificates)
    {
        $this->attributes['certificates'] = json_encode($certificates);
    }

    public function getCertificatesAttribute()
    {
        if($certificates != null){
           return json_decode($certificates, true); //force to array
        }

        return []; //default empty array
    }
}

Now if you create or update staff

Staff::create([
     'name' => 'Staff1',
     'certificates' => ['certificate1', 'certificate2'] 
]);

Then it will automatically saved as a json data in your staff table. And when you fetch data using $staff->certificates then it will return you array.

hope it may solve your problem

edited Jun 26, 2018 at 10:16

answered Jun 26, 2018 at 10:11

rkj

8,3172 gold badges30 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DatsunBing Over a year ago

See UPDATE in the OP

rkj Over a year ago

@KimPrince can't you use Staff::insert($staffs); or DB::table('staffs')->insert($staffs); for batch operations

Collectives™ on Stack Overflow

MySql Update Query to insert JSON values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related