Get max value from an array in mongodb

Question

Assuming we have the following documents in our collection:

{
  name: 'Product1',
  prices: [
    { shop: 'shop1', price: 1 },
    { shop: 'shop2', price: 2 }
  ]
},
{
  name: 'Product2',
  prices: [
    { shop: 'shop3', price: 5 },
    { shop: 'shop4', price: 3 }
  ]
}

I need to get the following results from these documents using mongodb:

{
  name: 'Product1',
  bestPrice: { shop: 'shop1', price: 1 }
},
{
  name: 'Product2',
  bestPrice: { shop: 'shop4', price: 3 }
}

In other words, I want to get documents with their lowest prices ordered by value of the price field. How could I do it using using mongodb aggregation framework?

Anthony, I never tried to create that query, because I don't have an idea what approach should be used to solve this problem. Reading official docs and searching in the internet won't give me answers. — Georgy Farniev
– Georgy Farniev, Commented Oct 5, 2018 at 7:59

Ashh · Accepted Answer · 2018-10-05 08:11:02Z

1

You can try below aggregation

db.collection.aggregate([
  { "$project": {
    "name": 1,
    "bestPrice": {
      "$arrayElemAt": [
        "$prices",
        {
          "$indexOfArray": [
            "$prices.price",
            { "$min": "$prices.price" }
          ]
        }
      ]
    }
  }}
])

answered Oct 5, 2018 at 8:11

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Georgy Farniev Over a year ago

Exactly what I needed, thank you! About indexes: as I understand, it's enough to have index for prices.price to get best efficiency from this query?

Ashh Over a year ago

No you have gone wrong. Indexes only works with $match and $sort stage. You can read more about it here docs.mongodb.com/manual/indexes

Collectives™ on Stack Overflow

Get max value from an array in mongodb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related