Laravel eloquent filter from a value of json column-CodePudding

I've taken over a project where they stored the date_start value in a JSON column.

(e.g, id, name, some_column, meta)
where in meta has a value of { propert1, property2, property3, "date_start": "2021-09-26", and so on...}

Now, in the front-end, I have to make a search feature, where I select a date.

Now in the back-end, since the value of date_start has been stored in a json column, I'd assume that I need to fetch all records first, loop through them and then decode the meta field.

Isn't it OVERALL bad? Or is there any other way?

CodePudding user response：

Modern versions of database often support JSON column types. These json columns can be queried using SQL.

preferences column in users table:

{"dining": {"meal": "salad", "time": "20:00"}}

Query:

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

Checkout the Laravel docs on JSON Where clauses: