vincent15000's avatar

Update JSON column for nested properties

Hello,

I have a question about this documentation.

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

DB::table('users')
	->where('id', 1)
	->update(['options->enabled' => true]);

But I'd like to update a deeper property in the JSON field.

DB::table('users')
	->where('id', 1)
	->update(['options->visible->enabled' => true]);

I have tested, it doesn't work ... or perhaps I have to code this differently ?

GIven that the application receives several events from micro-services, I need to avoid read/write collisions, so the only way seems to update the table via a single query.

Thanks for your help.

V

0 likes
8 replies
vincent15000's avatar

The AI suggests me this.

DB::table('rules')
    ->where('id', 1)
    ->update([
        'options' => DB::raw("JSON_SET(options, '$.visible.enabled', true)"),
    ]);

But it doesn't work. I get no error, but the options field remains unchanged.

Tray2's avatar

@vincent15000 Relational Database Management Service, in other terms MySQL/MariaDB/PostgreSQL/Oracle/MS SQL Server

1 like
vincent15000's avatar

@jlrdw @tray2 Thank you both for your advice ... hmmm you both have already suggested me several times to not use JSON columns.

@tray2 your post on your website is a great resource to build strong tables, already read some weeks ago.

But it's in a project for which I didn't decide by myself to use a JSON column, it's a choice from the former boss.

I'm working in parallel on 2 different applications, say his application and my application. In my application, I effectively use JSON columns too. But for the moment I don't have found the best database structure so that I have a big flexibility to store new structures of datas for the same type of datas in the future.

So in dev mode it's probably a first step storing the data in a JSON field for my application, and later, before putting the application in production, I will probably replace the JSON field by several tables.

So if you have any solution to update the JSON column for subkeys, don't hesitate to tell me ;).

Tray2's avatar

@vincent15000 We'll continue you tell you not to use them :)

Sorry no, I never used one in Laravel.

1 like

Please or to participate in this conversation.