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 ?

score 2 · Answer 1 · answered Jun 04 '21 at 00:26

2

I just solve it

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

Hope it will help others too.

answered Jun 04 '21 at 00:26

rai

197
2
14

score 1 · Answer 2 · answered Feb 01 '23 at 12:56

1

This works for me

->select('info->cost AS cost')

Also to query

->where('info->cost', '>', 0)

See more on the Laravel Docs

answered Feb 01 '23 at 12:56

CodeMonkey

3,271
25
50

What if the key has a dash character? For example 'min-cost'. – Ali Salavati Jun 08 '23 at 07:15
@AliSalavati works for me with hyphen - in the property – CodeMonkey Jun 08 '23 at 15:15

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

2 Answers2