How to group orders by different cities using Laravel, MySql, JSON

Trying to build a report result of orders related information of a laravel project. But struggling to find out a solution on a specific data structure.

My Request :

// Date Ranges
$fromDate = $this->request->fromDate;
$toDate = $this->request->toDate;

So the result should be within a date range.

My orders table :

order_id    order_addresses    grand_total
------------------------------------------
  1           JSON DATA          3,000.00
  2           JSON DATA          2,000.00
  3           JSON DATA          1,500.00

So My JSON DATA looks like this :

{
   "delivery_address":{
      "House":"Offline House",
      "Street":"Offline Road",
      "Area":"Offline Area",
      "PostCode":"1230",
      "City":"City1",
      "Country":"Country1",
      "ContactPerson":"Offline User",
      "ContactNumber":"01XXXXXXXX"
   }
}

I just want the response as :

    {
        "orders": 3, // How many orders are there in that particular city
        "totals": "5,500.00", // Total how much the cost of orders in that city
        "groupby": "City1",
    },
    {
        "orders": 5,
        "totals": "7,500.00", 
        "groupby": "City2",
    },
    ...,
    ...

I am seeking a solution using query builder in laravel with MySQL.

This is an existing project so I can't really change the structure how it was built. So, any suggestions on how I can extract the cities from JSON DATA having relation with the orders identity along with the totals and all. I just need the order_ids I think city wise then I can structure my result anyway I like to achieve end result.

If anything confusion here, please let me know so that I can make it clear.

Thanks in Advance !

via Chebli Mohamed