Mongo DB aggregation pipeline sort on nested array object values

Question

I have mongo documents with the following structure:

{
    name: 'AKL to DUD Via CHC',
    rates: [
        {
            container_id: 'abc',
            buyRate: 380
        },
        {
            container_id: 'def',
            buyRate: 410
        }
    ]
}
{
    name: 'AKL to DUD',
    rates: [
        {
            container_id: 'abc',
            buyRate: 400
        },
        {
            container_id: 'def',
            buyRate: 420
        }
    ]
}

I would like to have a sort in my aggregation pipeline based on the buyRate of a specific container_id, e.g. if I passed in a container_id of abc I would like to sort on the buyRate of just abc so that (in this case) the second document is returned first.

I can get it working by adding in a field with rates filtered by the container_id and then sorting on that but it seems overly complex:

db.services.aggregate([
    { $addFields: { "containerBuyRate":
            { $filter: {
                input: "$rates",
                as: "rate",
                cond: { $eq: [ "$$rate.container_id", "abc") ] }
            }}
     }},
     { $sort: {'containerBuyRate.buyRate': -1}}
])

Is there a more concise way to achieve this?

Hardik Shah · Accepted Answer · 2019-08-21 10:59:45Z

3

Your approach is fine.

Another way:

Difference is $addFields will add one more field as an object.

Working example.

db.getCollection('Example').aggregate([
  { $addFields: {
    containerBuyRate: {$arrayElemAt: [ "$rates", { $indexOfArray: [ "$rates.container_id", "abc" ] } ] }}
  },
  { $sort: {'containerBuyRate.buyRate': -1}}
])

Output:

[
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "containerBuyRate": {
      "buyRate": 405,
      "container_id": "abc"
    },
    "name": "AKL to DUD Via CHC",
    "rates": [
      {
        "buyRate": 405,
        "container_id": "abc"
      },
      {
        "buyRate": 500,
        "container_id": "def"
      }
    ]
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "containerBuyRate": {
      "buyRate": 400,
      "container_id": "abc"
    },
    "name": "AKL to DUD",
    "rates": [
      {
        "buyRate": 400,
        "container_id": "abc"
      },
      {
        "buyRate": 420,
        "container_id": "def"
      }
    ]
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "containerBuyRate": {
      "buyRate": 380,
      "container_id": "abc"
    },
    "name": "AKL to DUD Via CHC",
    "rates": [
      {
        "buyRate": 380,
        "container_id": "abc"
      },
      {
        "buyRate": 410,
        "container_id": "def"
      }
    ]
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "containerBuyRate": {
      "buyRate": 300,
      "container_id": "abc"
    },
    "name": "AKL to DUD Via CHC",
    "rates": [
      {
        "buyRate": 300,
        "container_id": "abc"
      },
      {
        "buyRate": 700,
        "container_id": "def"
      }
    ]
  },
  {
    "_id": ObjectId("5a934e000102030405000004"),
    "containerBuyRate": {
      "buyRate": 100,
      "container_id": "abc"
    },
    "name": "AKL to DUD Via CHC",
    "rates": [
      {
        "buyRate": 1000,
        "container_id": "def"
      },
      {
        "buyRate": 100,
        "container_id": "abc"
      }
    ]
  }
]

Thank you @Alex to point out my mistake on the last solution.

edited Aug 21, 2019 at 10:59

answered Aug 21, 2019 at 5:55

Hardik Shah

4,2082 gold badges22 silver badges45 bronze badges

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

6 Comments

Alex Blex Over a year ago

It's completely wrong. First of all if you run your aggregation on the OPs data you get 2 documents, not 3: mongoplayground.net/p/FJsbD84cCwA Secondly, you change order of elements in the array which may have side effects. Most importantly $sort doesn't sort by first element but by element with max value. Here mongoplayground.net/p/OjtWJd1OgqG I only changed {container_id: "def", buyRate: 410} to {container_id: "def", buyRate: 510} and it changed order of documents in the output.

Hardik Shah Over a year ago

@AlexBlex 1) I have tried with one more documents. So the result is in 3 documents. 2) $sort doesn't sort by the first element but by element with max value. Could you please give me any link so I can refer this sentence, maybe I am confused here. 3) You changed value buyrate to 510 and it changed the order of document which is correct as per OP's requirement. Thank you!

Alex Blex Over a year ago

I changed buyRate for subdoc with container_id: "def". It should not change order if we sort by buyRate of subdocs with container_id: "abc", right?

Hardik Shah Over a year ago

@AlexBlex, Thank you to point me in the right direction. I had completely missed out negative scenario.

Alex Blex Over a year ago

That's much better. Sorry I couldn't find a link to the docs about sorting. I guess it's the case where stackoverflow fills the gaps in the documentation.

|

Ashok · Accepted Answer · 2019-08-21 05:54:19Z

1

For sorting containerBuyRate array you need to unwind it and sort it

db.services.aggregate([
     { $addFields: { "containerBuyRate":
        { $filter: {
            input: "$rates",
            as: "rate",
            cond: { $eq: [ "$$rate.container_id", "abc" ] }
        }}
     }},
     { $unwind: "$containerBuyRate" },
     { $sort: {"containerBuyRate.buyRate": -1}},
     { $group: { 
         _id: "$_id",
         containerBuyRate: {$push: "$containerBuyRate"},
         name: { $first: "$name"},
         rates: {$first: "$rates"} 
     }}
])

answered Aug 21, 2019 at 5:54

Ashok

2,9621 gold badge15 silver badges21 bronze badges

Collectives™ on Stack Overflow

Mongo DB aggregation pipeline sort on nested array object values

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related