joshuakielas's avatar

How to update json field in a list of items

How would I update Sally's name to Cindy in the example below using an update query for json field data? I've seen simple examples on the web, but nothing using collections of objects.

{
    "summary": {
        "comment": "Oops, not Sally, Cindy!",
        "entries": [
            {
                "id": 1,
                "name": "Jim"
            },
            {
                "id": 2,
                "name": "Sally"
            },
            {
                "id": 3,
                "name": "Tim"
            }
        ]
    }
}
0 likes
10 replies
joshuakielas's avatar

I should also add that I'm hoping to make the update without knowing ahead of time where in the list Sally is, so I need to address the update by id to find the correct record.

jekinney's avatar

Laravel makes it quite easy.

https://laravel.com/docs/5.3/queries#updating-json-columns

https://laravel.com/docs/5.3/queries#json-where-clauses

Also a lot of helpers too.

https://laravel.com/api/5.3/search.html?search=Json

Also you can dust of ol php and use json_decode witch will set the json object as an array. Change what you need a json_encode it back.

Or use laravel's collection and access the data like an object. Then toJson() it back. But you'll have to decode first or while creating the object.

collect(json_decode($json));

https://laravel.com/docs/5.3/collections#method-tojson

You have a ton of options. Pick one that works for you.

nyce's avatar

@jekinney I'm afraid that the documentation is insufficient for my needs.

  1. Firstly, in order that the code passes without error, you need to add the JSON column to the $fillable array. This doesn't seem to be documented in the links you reference, and anyway it is undesirable in my case.

  2. When I do add the column into the $fillable array, the code ceases to error. However, the data in the database doesn't seem to get updated. It just doesn't seem to work.

For completeness, I'm working on Postgres 9.6 on a JSONP column.

jekinney's avatar

@strejo @cbh

Create a text column (or medium text etc). use php to json_encode() the data.

this puts the data as a json string, which the db will take. When you parse it out you can json_decode into a php array or pass it to your javascript and parse it there.

{ 
    "settings": [
        {"requires": "auth"},
        {"permissions": "can_create"}
    ]
}


$cert->create(['settings' => json_encode( $request->settings )]);

$settings = json_decode($cert->settings, true); 
 // True boolean tells php to return an array

dd($settings);

[ 'requires' => 'auth', 'permission' => 'can_create']
marufalom's avatar

Hi, is there a way to update an only specific field? for example, I want to update only the name Jim to Kim ?

dmoxyeze's avatar

@titus123 , if you are looking to update a specific index in the array, for instance, you wanted to update the name in entries at index 0 to 'John'

$summary = $item->summary->entries;
$summary[0]['name'] = 'John';
$item->summary = $summary;
$item->save();

Please or to participate in this conversation.