CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Rafa Acioly
Rafa Acioly

Reputation: 636

How to retrieve specific keys when grouping on mongo while using $max on a field?

How can i retrieve keys beyond the grouped ones from mongodb?

Documents example:

{code: 'x-1', discount_value: 10, type: 1}
{code: 'x-2', discount_value: 8, type: 1}
{code: 'x-3', discount_value: 5, type: 2}

Query:

{
    $match: {
        type: 1
    }
},
{
    $group: {
        _id: null
        discount_value: {$max: '$discount_value'}
    }
}

This query will retrieve the max value from discount_value (10) key and the key _id but how i can do to retrieve the code and type key as well if i don't have operation to do those keys?

The current result:

{_id: null, discount_value: 10}

Expected result:

{_id: null, discount_value: 10, type: 1, code: 'x-1'}

Upvotes: 1

Views: 315

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

You can try below query :

db.collection.aggregate([
  {
    $match: { type: 1 }
  },
  {
    $group: {
      _id: null,
      doc: {
        $max: {
          discount_value: "$discount_value",
          type: "$type",
          code: "$code"
        }
      }
    }
  }
])

I believe it would get $max on field discount_value and get respective type & code values from the doc where discount_value is max.

In another way, since you're using $match as first stage, I believe your data will be less enough to perform $sort efficiently :

db.collection.aggregate([
  {
    $match: { type: 1 }
  },
  {
    $sort: { discount_value: -1 } // sort in desc order
  },
  {
    $limit: 1
  }
])

Test : mongoplayground

Note :

Test the first query on DB itself rather than in playground. In first query you can use $replaceRoot as last stage if you wanted to make doc field as root of your document.

Upvotes: 1

Related Questions