Question 115495 in Mongodb Question posted in
The official documentation can be found here.

Mongodb $sort based on bigger value of 2 parameters

Example Documents:

[
 {"subdocument": {"value":100,"additionalValue":300}},
 {"subdocument": {"value":100}                         // additionalValue doesn't exist on this one
]

What I want: at the end of my aggregation:

{
 "largest": // The entire first item because 300 is the highest overall value
 "smallest": // The entire second item because 100 is the smallest "" average
 "average": 150 // 1st item average is 200, 2nd item is 100 and their combined average is 150
}

What I did:

{ $sort: { 'subdocument.value': -1 } },
        {
          $group: {
            _id: null,
            average: { $avg: '$subdocument.value' },
            items: { $push: '$$ROOT' },
          },
        },
        { $set: { largest: { $first: '$items' } } },
        { $set: { smallest: { $last: '$items' } } },
        { $project: { largest: 1, smallest: 1, average: 1 } },

But this does not include the additonalValue field.
And I don’t know of any way to get a "larger one" expression

something like: average: { $max: { $larger: ['$subdocument.value', 'subdocument.additonalValue'] }},

IMPORTANT NOTE: the additionalValue field is optional.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Just as reference the solution by @ray combined with a filter instead of lookup that I came up with:

    { $sort: { 'subdoc.value': -1 } },
        {
          $group: {
            _id: null,
            min: { $min: '$subdoc.value' },
            average: {
              $avg: { $avg: ['$subdoc.value', '$subdoc.additionalValue'] },
            },
            max: {
              $max: { $max: ['$subdoc.additionalValue', '$subdoc.value'] },
            },
            items: { $push: '$$ROOT' },
          },
        },
        {
          $set: {
            largest: {
              $first: {
                $filter: {
                  input: '$items',
                  as: 'item',
                  cond: {
                    $eq: [
                      {
                        $max: [
                          '$$item.subdoc.value',
                          '$$item.subdoc.additionalValue',
                        ],
                      },
                      '$max',
                    ],
                  },
                },
              },
            },
          },
        },
        { $set: { smallest: { $last: '$items' } } },
        { $project: { largest: 1, smallest: 1, averaget: 1 } },

  2. 0

    Simply applying the operation twice on the values.

    db.collection.aggregate([
  {
    $group: {
      _id: null,
      largest: {
        $max: {
          $max: [
            "$subdocument.value",
            "$subdocument.additionalValue"
          ]
        }
      },
      smallest: {
        $min: {
          $min: [
            "$subdocument.value",
            "$subdocument.additionalValue"
          ]
        }
      },
      average: {
        $avg: {
          $avg: [
            "$subdocument.value",
            "$subdocument.additionalValue"
          ]
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "let": {
        l: "$largest"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [
                "$$l",
                {
                  $max: [
                    "$subdocument.value",
                    "$subdocument.additionalValue"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "largestItems"
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "let": {
        s: "$smallest"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [
                "$$s",
                {
                  $min: [
                    "$subdocument.value",
                    "$subdocument.additionalValue"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "smallestItems"
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search