MYSQL JSON UPDATE STATEMENT

// where clause on json field
$redLovers = DB::table('users')
    ->where('meta->favorite_color', 'red')
    ->get();

// update on a json field
DB::table('users')
    ->where('id', 1)
    ->update(['meta->wants_newsletter' => false]);

@LOSTDREAMER_NL - The first section of the code doesn't have an update function and the where clause on the second update function is not querying a json column

What do you mean by "where code submitted from the form is same as code in the json column"? Can you provide an example?

@STAUDENMEIR - I have a json type column which stores inputs from a create item form in the format shown

[{"id": 1, "code":"1234", "name":"pen", "status":"in-store" }]

what i want is to update the status section to sold where the code sent from another form matches the one in the json element i.e 1234

Here is the code i have tried but returns false

''' $update = DB::update(DB::raw('UPDATE codes SET items_code = JSON_SET(items_code, "$.item_status", "sold") WHERE items_code->"$.code" = "$value"; '));

'''

I don't think that's possible with a single SQL query.

@magero, then throw the 2 together.... come one, programming is about solving problems right?

// update on a json field
DB::table('codes')
    ->where('items_code->code', request()->code)
    ->update(['items_code->status' => 'sold']);

// or ofcourse by model
Code::where('items_code->code', request()->code)
    ->update(['items_code->status' => 'sold']);