Min nested array in mongo

Question

How can I use the $min function to get the min value within nested arrays (and add it to the document)?

[
  {
    "_id": "a357e77f-a76a-4bc2-8765-923280663e97",
    "customers": [
      {
        "_id": "97170117-4660-4c6f-b8da-2b34d4d0c9ce",
        "orders": [
          {
            "amount": 0.5
          },
          {
            "amount": 6.400001525878906
          }
        ]
      },
      {
        "_id": "7b9ccf5b-3acb-4ed1-8df4-e3b5afc49cba",
        "orders": [
          {
            "amount": 27.29999542236328
          },
          {
            "amount": 0.29999542236328125
          }
        ]
      }
    ]
  },
  {
    "_id": "58433224-8162-4f0a-8168-bc11b4306b0a",
    "customers": [
      {
        "_id": "8a6055d0-9b94-40be-8f96-8fd9088d24aa",
        "orders": [
          {
            "amount": 19.700000762939453
          }
        ]
      },
      {
        "_id": "a50a57b8-61e7-4727-a15a-4a4137b2f81a",
        "orders": [
          {
            "amount": 43.80000305175781
          }
        ]
      }
    ]
  }
]

How can I get the min amount value within the $customers.orders.amount path?

I've tried but it returns 0.

db.collection.aggregate([
  {
    $addFields: {
      "amount": {
        $sum: "$customers.orders.amount"
      }
    }
  }
])

Where is amount? Are you referring to total?

Gibbs
– Gibbs

2020-12-10 07:19:37 +00:00
Commented Dec 10, 2020 at 7:19 — Gibbs
– Gibbs, Commented Dec 10, 2020 at 7:19
Yes. Sorry, updated the question.

Chris Herring
– Chris Herring

2020-12-10 08:31:11 +00:00
Commented Dec 10, 2020 at 8:31 — Chris Herring
– Chris Herring, Commented Dec 10, 2020 at 8:31
Min value across all the customers?

Gibbs
– Gibbs

2020-12-10 08:40:41 +00:00
Commented Dec 10, 2020 at 8:40 — Gibbs
– Gibbs, Commented Dec 10, 2020 at 8:40

Gibbs · Accepted Answer · 2020-12-10 08:45:58Z

1

You can do as below for each customer

playground

db.collection.aggregate([
  {//Destruct
    "$unwind": "$customers"
  },
  {//Destruct
    "$unwind": "$customers.orders"
  },
  {//Group by customer id,
    $group: {
      "_id": "$customers._id",
      min: {
        $push: {
          "$min": "$customers.orders.amount"
        }
      }
    }
  }
])

You can use group by null if you want to find min across all the customers.

answered Dec 10, 2020 at 8:45

Gibbs

23.1k14 gold badges84 silver badges148 bronze badges

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

1 Comment

Chris Herring Over a year ago

That returned a min for each customer rather than across each root document (e.g. a site). Sorry if I wasn't clear. Also wanted to add the min to the existing documents. However I have found a way using reduce.

Chris Herring · Accepted Answer · 2020-12-14 05:40:45Z

0

Found a solution using reduce to create a flat array of the nested arrays and then use $min on that. MongoPlayground

db.collection.aggregate([
    {
        "$addFields": {
            "min": {
                $min: {
                    "$reduce": {
                        "input": "$customers",
                        "initialValue": [],
                        "in": {
                        "$concatArrays": [
                                "$$value",
                                "$$this.orders.amount"
                            ]
                        }
                    }
                }
            }
        }
    }
])

answered Dec 14, 2020 at 5:40

Chris Herring

3,6353 gold badges37 silver badges51 bronze badges

Collectives™ on Stack Overflow

Min nested array in mongo

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related