I have some table called services which i store service records. i have a situation where i can add those services from 2 different places [ Reservation , POS ].

table structure for services table is like the following
id,number,….,meta [ json column ].

with each service added i store meta information in the json but here is my problem the services added from reservation meta is inserted like this

{"qty": 1, "category": "service", "services": [{"id": 2503, "qty": 1, "ttx": 0, "vat": 0, "price": "10", "category": "service", "statement": "beans", "sub_total": 10, "ttxIsChecked": false, "vatIsChecked": false, "totalGeneralSum": 10}], "statement": "beans", "sub_total": 10, "ttx_total": "0.00", "vat_total": "0.00", "payment_type": null, "total_with_taxes": "10.00"}

services added from pos meta is inserted like this

{"pos": true, "qty": 1, "date": "2024-04-20 13:02", "from": "alex", "note": "1 × beans ,", "type": 107272, "address": null, "category": "service-deposit", "employee": "Mr man", "services": [{"id": 2503, "qty": 1, "ttx": 0, "vat": "1.50", "price": "10.00", "category": "service", "statement": "beans", "sub_total": 10, "ttxIsChecked": false, "vatIsChecked": true, "totalGeneralSum": 11.5}], "pay_later": true, "reference": null, "statement": "beans", "sub_total": "10.00", "ttx_total": "0.00", "vat_total": "1.50", "tax_number": null, "payment_type": null, "customer_name": null, "total_with_taxes": "11.50"}

So i need to say here query let your first priority to search with the date value of the json but if there is no date value in the json please search with the created at column of the record.

What i have tried so far.

 $from = Carbon::parse($value)->format('Y-m-d H:i');
        return $query->where('meta->date', '>=', $from)
                ->orWhere('created_at', '>=', $from);

I’ve also tried this

 $from = Carbon::parse($value)->format('Y-m-d H:i');
        return $query->where(function($query) use ($from) {
            $query->whereRaw("JSON_CONTAINS_PATH(meta, 'one', '$.date')");
            if($query->exists()){
                $query->where('meta->date', '>=', $from);
            }else{
                $query->Where('created_at', '>=', $from);
            }
        });

Both ways are wrong and dropping some records. so what am missing guys.

I also though of sticking with one way that all records should have date value in the json and this will work but i have to edit over 113k records to copy created at value then get the meta of the record then update the meta and insert new date json value