Ndtyjky

I am wondering how could I achieve a specific result.

Starting of
I am using https://github.com/jenssegers/laravel-mongodb

The code sample below is used to get an array of documents that contains my specific slug in the rewards node. And till that point, everything works as intended.

 $array = [

            'rewards.slug' => ['$eq' => 'example_slug'],

            'expired' => ['$gte' => CarbonCarbon::now()->toDateTimeString()]

        ];

        $models = Master::raw(function ($collection) use (&$array) {

            return $collection->find(

                $array, ["typeMap" => ['root' => 'array', 'document' => 'array']])

                ->toArray();

        });

My example document

{

    "_id": {

        "$oid": "5be4464eafad20007245543f"

    },

    "some_int_value": 100,

    "some_string_value": "String",

    "rewards": [

        {

            "slug": "example_slug",

            "name": "Example slug",

            "quantity": 4,

            "estimated": {

                "value": 18750

            }

        },

        {

            "slug": "example_slug",

            "name": "Example slug",

            "quantity": 1,

            "estimated": {

                "value": 100

            }

        },

        {

            "slug": "other_example",

            "name": "Other slug example",

            "quantity": 1,

            "estimated": {

                "value": 100

            }

        }

    ],

    "expires": "2018-11-08 20:20:45",

}

Desired result

I would like to implement some more complex query, which would do the following.

• Retrieve documents that : pseudo select all documents that contain reward "slug": "example_slug", sum the quantity of them, return greater than X quantity documents, order by sum quantity desc
  


• and a very similar one to the above select all documents that contain reward "slug": "example_slug", sum estimated.value, return greater than X estimated.value documents, order by sum of estimated.value desc
  

If you do need more explanation feel free to ask, I feel like I don't even know where to start with this one.

All help is greatly appreciated

You can use below aggregation in 3.6.

$addFields to create an extra slugcount field to hold the result.

$filter rewards with slug matching example_slug followed by $sum to sum the quantity field.

$match with $gt > X - aggregation expression to filter documents where the sum of all matching quantities is greater than X

$sort slugcount desc and $project with exclusion to remove the slugcount from the final response.

db.colname.aggregate([

{"$addFields":{

  "slugcount":

   {"$let":{

    "vars":{

     "mslug":{

      "$filter":{

       "input":"$rewards",

       "cond":{"$eq":["$$this.slug","example_slug"]}

      }

     }

    },

    "in":{"$sum":"$$mslug.quantity"}

    }}

}}, 

{"$match":{"slugcount":{"$gt":X}}},

{"$sort":{"slugcount":-1}},

{"$project":{"slugcount":0}}

])

Something like

ModelName::raw(function ($collection) {

 return $collection->aggregate([

  ['$match' => ['expired' => ['$gte' => CarbonCarbon::now()->toDateTimeString()]]],

  ['$addFields' => [

    'slugcount'

     ['$let' => [

      'vars' => [

       'mslug' => [

        '$filter' => [

         'input' => '$rewards',

         'cond' => ['$eq' => ['$$this.slug','example_slug']]

        ]

       ]

      ],

      'in' => ['$sum' => '$$mslug.quantity']

      ]]

  ]],

  ['$match' => ['slugcount'=> ['$gt' => X]]],

  ['$sort' => ['slugcount' => -1]],

  ['$project' => ['slugcount' => 0]]]);

});

You can replace quantity with estimated.value for second aggregation.