Sort and concatenate a newly computed array field using the aggregation pipeline

Question

I have a users collection and a roles collection. A user has_many roles and roles is stored as an array of ids on the user document. Now I want to sort users based on their roles value. The user can have one role or many. If the user has more than one role, then I want them delimited by a comma:

'admin'
admin, marketing'
'user, marketing'

And this exactly the data I want sorted by the alphanumeric characters, e.g. descending sort:

'user, marketing'
'admin, marketing'
'admin'

This is what I have tried:

db.users.aggregate([
  { $lookup : { from: 'roles', localField: 'role_ids', foreignField: '_id', as: 'roles' } },
])

Gives me:

{ "_id" : ObjectId("5d0ac05c3ed149604a000003"), "email" : "[email protected]", "name" : "Administrator", "role_ids" : [ ObjectId("5d0ac01f3ed149604a000000"), ObjectId("cd0ab01f3ed129603a000550") ], "roles" : [ { "_id" : ObjectId("5d0ac01f3ed149604a000000"), "name" : "admin") }, { "_id" : ObjectId("cd0ab01f3ed129603a000550"), "name" : "marketing") } ] }

Then I thought to use $project to add a newly computed concatenated field of the role names:

db.users.aggregate([
  { $lookup : { from: 'roles', localField: 'role_ids', foreignField: '_id', as: 'roles' } },
  { $project : {_id: 1, email: 1, name: 1, joinedRoles: { $concat: [] }  } }
  { $sort : { joinedRoles: -1 } }
])

It's this part right here that I am trying to figure out:

joinedRoles: { $concat: [] }

Xavier Guihot Xavier Guihot · Accepted Answer · 2019-06-25T05:17:54

Unfortunately, you're trying to do two basic things that Mongo is currently really bad at doing:

sorting an array
joining an array as a string

Yet we can apply some mixture of stages such as:

// { id: 1, roles: ["marketing", "admin", "user"], b: 42 }
// { id: 2, roles: ["admin"], b: 11 }
// { id: 3, roles: [], b: 1 }
db.collection.aggregate([
  // First thing, let's handle empty arrays by replacing them with
  // an array containing `""`. This way, the next stage (`$unwind`)
  // will not get rid of these empty arrays. This might not be needed
  // if you know you'll always have a role for a user. `roles: []`
  // becomes `roles: [""]` other arrays are left untouched:
  { $addFields: {
    roles: { $cond: {
      if: { $eq: [ { $size: "$roles" }, 0 ] }, then: [""], else: "$roles"
    }}
  }},
  // Transforms each document in as many documents as there are items in
  // `roles`. For instance, `{ id: 1, roles: ["marketing", "admin", "user"], b: 42 }`
  // becomes these 3 documents: `{ id: 1, roles: "marketing", b: 42 }`,
  // `{ id: 1, roles: "admin", b: 42 }` and `{ id: 1, roles: "user", b: 42 }`
  // That's also why we handled empty arrays in the previous stage:
  { $unwind: "$roles" },
  // Now we can alphabetically sort all documents on the `roles` field:
  { $sort: { roles: 1 } },
  // And we can transform back roles to arrays by grouping on `id` using
  // `$push` within a `$group` stage. Since this preserves the order, the
  // `roles` array is now sorted (due to the `$sort` stage applied
  // just before on unwind elements). Note that you have to specify all
  // other fields in your documents that you want to keep using `$first`.
  // At this point you'll get `{ _id: 1, roles: ["admin", "marketing", "user"], b: 42 }`:
  { $group: { _id: "$id", roles: { $push: "$roles" }, b: { $first: "$b" } } },
  // Finally, we can join an array of strings as a string by coupling
  // `$reduce` and `$substr` operations:
  { $addFields: { roles: {
      // The `$substr`ing handles results of `$reduce` such as
      // `", admin, marketing"` by removing the first 2 characters:
      $substr: [
        // `$reduce` joins elements by applying `$concat` as many times as
        // their are items in the array:
        { $reduce: {
          input: "$roles",
          initialValue: "",
          in: { $concat: ["$$value", ", ", "$$this"] }
        }},
        2,
        -1
      ]
  }}},
  // And, you can obviously sort the resulting documents on this field:
  { $sort: { roles: -1 } }
])
// { _id: 3, roles: "", b: 1 }
// { _id: 2, roles: "admin", b: 11 }
// { _id: 1, roles: "admin, marketing, user", b: 42 }

Note that I've simplified a bit your problem by using a clean array of roles in input. In order for you to get to that point, you can first apply a $map transformation such as:

// { roles: [{ id: 1, role: "admin" }, { id: 2, role: "marketing" }] }
db.collection.aggregate({ $addFields: { roles: { $map: { input: "$roles", as: "role", in: "$$role.role" } } } })
// { roles: [ "admin", "marketing" ] }

Sort and concatenate a newly computed array field using the aggregation pipeline

1 Answers