database - 使用聚合管道对新计算的数组字段进行排序和连接

Question

我有一个用户集合和一个角色集合。用户 has_many 角色和角色存储为用户文档中的 id 数组。现在我想根据角色值对用户进行排序。用户可以具有一个或多个角色。如果用户有多个角色，那么我希望他们用逗号分隔:

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

这正是我想要按字母数字字符排序的数据，例如降序:

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

这是我试过的:

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

给我:

{ "_id" : ObjectId("5d0ac05c3ed149604a000003"), "email" : "me@gmail.com", "name" : "Administrator", "role_ids" : [ ObjectId("5d0ac01f3ed149604a000000"), ObjectId("cd0ab01f3ed129603a000550") ], "roles" : [ { "_id" : ObjectId("5d0ac01f3ed149604a000000"), "name" : "admin") }, { "_id" : ObjectId("cd0ab01f3ed129603a000550"), "name" : "marketing") } ] }

然后我想使用 $project 添加一个新计算的角色名称串联字段:

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 } }
])

我想弄清楚的正是这里的这一部分:

joinedRoles: { $concat: [] }

Answer 1

不幸的是，您正在尝试做 Mongo 目前非常不擅长的两件基本事情:

• 排序数组
• 将数组作为字符串加入

然而，我们可以应用一些阶段的混合，例如:

// { 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 }

请注意，我通过在输入中使用一系列干净的角色简化了您的问题。为了达到这一点，您可以首先应用 $map 转换，例如:

// { 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" ] }