mongodb $lookup + regex

Question

Mongo Users, I have a problem and I would appreciate your help.

I have three mongo collections which behave like in relational database tables:

contacts_collecion:

{
    "_id" : ObjectId("..."),
    "cid" : "1",
    "email" : "a1@a.aaa"
}
{
    "_id" : ObjectId("..."),
    "cid" : "2",
    "email" : "a2@a.aaa"
}
{
    "_id" : ObjectId("..."),
    "cid" : "3",
    "email" : "a3@a.aaa"
}

groups_collection:

{
    "_id" : ObjectId("..."),
    "gid" : "1",
    "group_name" : "group1"
}
{
    "_id" : ObjectId("..."),
    "gid" : "1",
    "group_name" : "group2"
}

contacts_groups_collection:

{
    "_id" : ObjectId("..."),
    "cid" : "2",
    "gid" : "1"
}
{
    "_id" : ObjectId("..."),
    "cid" : "3",
    "gid" : "1"
}
{
    "_id" : ObjectId("..."),
    "cid" : "3",
    "gid" : "2"
}

So I have mamy-to-many relation - every contact can be in every group. I would like query the mongodb with mongo equivalnet of sql:

SELECT contacts.*
FROM  dbo.contacts INNER JOIN dbo.contacts_groups ON dbo.contacts.cid = dbo.contacts_groups.cid
WHERE dbo.contacts.email LIKE '%a%' AND dbo.contacts_groups.gid = '1'
ORDER BY dbo.contacts.email

1) I know how to build a first filter (WHERE dbo.contacts.email LIKE '%a%').

db.contacts.find({"email": /a/})

With a "C#/.NET Driver Version 2.3" I can do it with (I also need pagination so I have:

FilterDefinition<ContactSerial> filter = Builders<ContactSerial>.Filter.Regex("Email", "aaa");
List<ContactSerial> contactsList = m_mongoConn.ContactsColl.Find(filter).Skip(0).Limit(5).ToList();

2) I suppose how to achieve an equivalent (not ideal) of INNER JOIN (even though mongo is not relational db).
I have found a $lookup: db.contacts.aggregate([{ $lookup: {from: "contacts_groups", localField: "cid", foreignField: "cid", "as": "details"}}])

With "C#/.NET Driver Version 2.3": mongoConn.ContactsColl.Aggregate().Lookup("contacts_groups", "cid", "cid", "details");
$lookup will give me

{
    "_id" : ObjectId("..."),
    "cid" : "1",
    "email" : "a1@a.aaa",
    "details" : [ ]
}
{
    "_id" : ObjectId("..."),
    "cid" : "2",
    "email" : "a2@a.aaa",
    "details" : [
            {
                    "_id" : ObjectId("..."),
                    "cid" : "2",
                    "gid" : "1"
            }
    ]
}
{
    "_id" : ObjectId("..."),
    "cid" : "3",
    "email" : "a3@a.aaa",
    "details" : [
            {
                    "_id" : ObjectId("..."),
                    "cid" : "3",
                    "gid" : "1"
            },
            {
                    "_id" : ObjectId("..."),
                    "cid" : "3",
                    "gid" : "2"
            }
    ]
}

My question is:
a) How to combine a regex filter (1) with a $lookup (2) in a one MongoShell query?
How to do this with a "C#/.NET Driver Version 2.3"
b) how to add a second filter (AND dbo.contacts_groups.group_id = '1') in a MongoShell query - note this is from the second collection: contacts_gropups not contacts
How in "C#/.NET Driver Version 2.3"?
c) how to add to all of this an (ORDER BY dbo.contacts.email)

Answer 1

1) To pre-filter values for your aggregate query, use match :

db.contacts.aggregate([
  {$match:{email: {"$regex":/a/}}},
  {$lookup: {from: "contacts_groups", localField: "cid", foreignField: "cid", "as": "details"}}])

You could also use your predefined filter for aggregation in c# :

m_mongoConn.ContactsColl
         .Aggregate()
         .Match(filter)
         .Lookup("contacts_groups", "cid", "cid", "details")

2) You need to execute another project after the lookup. See an example here: Filter $lookup results

3) To sort the output in the aggregation pipeline, Sort may be used. Note, in your aggregation you get Contacts from Match, but after Lookup you only have Bson. Therefore, it is better to sort between Match and Lookup

m_mongoConn.ContactsColl
         .Aggregate()
         .Match(filter)
         .Sort(Builders<ContactSerial>.Sort.Ascending(c=>c.email))
         .Lookup("contacts_groups", "cid", "cid", "details")