Laravel Query - Extract specific field in aray of objects in A JSON column and calculate

Question

I would like to get only a specific field in a json column and calculate its values. The json column is an array of objects

    [
       {
         "sku": "DT-ALD3-EA18", 
         "adId": 244077676726655, 
         "asin": "B07V3SSLN1", 
         "cost": 12, 
         "clicks": 0,
       },
       {
         "sku": "DT-ALF3-EA1F", 
         "adId": 24408767672665, 
         "asin": "B07V3SSLN7", 
         "cost": 0, 
         "clicks": 0,
       }
    ]

Currently, I just select the json column and loop it and extract the cost field, push it into an array and calculate it using php's array_sum. But it is not efficient since I have a very large table and when using date range with large interval causes a memory limit errors. My goal is to just to extract the needed field and calculate it. I tried

        $orders = DB::table('performance_reports')
        ->select(DB::raw('sum("JSON_EXTRACT(`info` , '$[*].cost'")'))
        ->whereBetween('date',[$from,  $to])
        ->get()->toArray();

But I got syntax validation error.

How do you query only the specific field in an array of objects in mysql's json field and calculate ?

Answer 1

I just solve it

   ->select(DB::raw("JSON_UNQUOTE(JSON_EXTRACT(info, '$[*].cost')) as cost"))

Hope it will help others too.