mongodb - $lookup 区分大小写的字段

Question

我知道如何进行不区分大小写的搜索。

例子 ：

• members.email：[e-mail]但是：

• 用户邮箱：[e-mail]

  db.getCollection('members').aggregate([
  {$lookup: {'from': 'users', 'localField': 'members.email', 'foreignField': 'email', 'as': 'users'}},
  {$unwind: {path: '$users', preserveNullAndEmptyArrays: true}},
  {$match : { users: { $exists: false } }},
  ]);
  

集合成员看起来像：

{
"_id" : ObjectId("5b439c02a5439e00568291fe"),
"members" : [ 
    {
        "email" : "[e-mail]"
    }, 
    {
        "email" : "[e-mail]"
    }, 
    {
        "email" : "[e-mail]"
    }
],
"groupname" : "abuse",
"active" : true,
"createdAt" : ISODate("2018-07-09T17:31:46.098Z"),
"updatedAt" : ISODate("2018-07-09T17:31:46.098Z"),
"__v" : 0
}

集合 member_user 看起来像：

{
"_id" : ObjectId("5a7dfce545e13f0233b2e451"),
"email" : "[e-mail]",
"__v" : 0
}

{
"_id" : ObjectId("5b43ac89bfd90aa9cc4a68c2"),
"email" : "[e-mail]",
"__v" : 0
}

这是我使用的代码：

    db.getCollection('member').aggregate([
    {$unwind: "$members"},
    {$lookup: {'from': 'member_user', 'localField': 'members.email', 'foreignField': 'email', 'as': 'users'}},
    {$unwind: {path: '$users', preserveNullAndEmptyArrays: true}},
    {$match : { users: { $exists: false } }},
], 
{
    collation: {
        locale: 'en_US',
        strength: 1
    }
}
    );

当数据集很小时，速度总是很快。但我有 24K 会员和 7K 用户。正如我所说，当 {$match : { users: { $exists: true } }} 时，速度还可以，但是当 {$match : { users: { $exists: false } }} 时，速度几乎是 60 倍。（1 秒对 1 分钟）

无需整理，速度总是很快

标签： mongodbmongodb-queryaggregation-framework

Answer 1

您需要使用聚合将localFieldandforeignField转换为小写，$toLower然后您可以轻松匹配您的字段。

db.collection.aggregate([
  { "$unwind": "$members" },
  { "$addFields": { "members.email": { "$toLower": "$members.email" }}},
  { "$lookup": {
    "from": "users",
    "let": { "membersEmail": "$members.email" },
    "pipeline": [
      { "$addFields": { "email": { "$toLower": "$email" }}},
      { "$match": { "$expr": { "$eq": [ "$email", "$$membersEmail" ] } } }
    ],
    "as": "members.email"
  }},
  { "$unwind": "$members.email" },
  {  "$group": {
    "_id": "$_id",
    "groupname": { "$first": "$groupname" },
    "active": { "$first": "$active" },
    "members": { "$push": "$members.email" }
  }}
])