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:
- By name, alphabetically.
- Then by username, alphabetically.
- 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?