0

Take a local table which represents users:

{
   _id: 'a',
   name: 'One',
   subscription_id: '1234'
}

And a foreign table which represents a user's subscription:

{
   _id: '1234',
   plan: 'intro'
}

You could then get an array of all users, with their subscription included as a child field:

db.users.aggregate([{
    "$lookup": {
      "from": "subscriptions",
      "as": "subscriptions",
      "let": {
        "id": "$_id"
      },
      "pipeline": [{
          "$match": {
            "$expr": {
              "$eq": ["$$id", "$subscription_id"]
            }
          }
        }
      ]
    }
  },
  {
    "$addFields": {
      "subscription": {
        "$arrayElemAt": ["$subscriptions", 0]
      }
    }
  }
])

Each element in the resulting array would look something like this:

{
   _id: 'a',
   name: 'One',
   subscription_id: 1234,
   subscription: {
       _id: 1234,
       plan: 'intro'
   }
}

Now, say I want to sort this overall array of users with these options:

  1. By name, alphabetically.
  2. Then by username, alphabetically.
  3. Then by subscription plan, in logical order of the plan.

By name and username (alphabetically) is straight forward because you can just sort on those fields in ascending order.

{
  $sort: {
    name: 1,
    username: 1,
    subscription.plan: 1 // this wouldn't be logical, would it work at all?
  }
}

However, the subscription plan isn't so straight forward. The subscription plans cannot be alphabetically ordered while remaining logical, like if the plans were 'intro', 'basic', 'independent', 'professional' or something similar.

Is there a way to provide logical sort order for these elements? Could aggregation generate an integer field specifically sort sort by plan?

afollestad
  • 2,929
  • 5
  • 30
  • 44
  • Presumably `'intro', 'basic', 'independent', 'professional'` all would have a logical "order" you want, so add in the content as a new numerical value in order to `$sort` on that. `$cond` or `$switch` should be able to test the document value to a set of values from an external list in generating the statement. – Neil Lunn Jun 14 '18 at 01:24
  • @NeilLunn that and the other question came in very useful, thank you. – afollestad Jun 14 '18 at 02:14
  • Ended up using `$switch` within an `$addFields` stage – afollestad Jun 14 '18 at 02:14

0 Answers0