mongodb c# driver join query

Question

public class Person
{
    public ObjectId _id { get; set; }
    public int AddressID { get; set; }
    public int Age { get; set; }
    public Person Father { get; set; }
    public string ID { get; set; }
    public double Income { get; set; }
    public string Name { get; set; }
}


public class Address
{
    public ObjectId _id { get; set; }
    public int HouseNo { get; set; }
    public int ID { get; set; }
    public string Street { get; set; }
}

How do I get Sum of Income by street? by using mongodb c# driver

Got income by Age.

var personcollection = this.cdb.GetCollection<Person>("person");
var aggregate = personcollection.Aggregate()
            .Group(new BsonDocument { { "_id", "$Age" }, { "sum", new BsonDocument("$sum", "$Income") } });
 var results = await aggregate.ToListAsync();

but I have know Idea how to link two documents for a single query.

Thanks for you help.

MongoDB has no joins so you would need to change your schema to embed Address in Person instead of using a reference. — JohnnyHK
– JohnnyHK, Commented Sep 1, 2015 at 13:59

Alex · Accepted Answer · 2015-09-02 08:31:48Z

1

This smells like a schema design flaw.

You should embed your address document into your Person document:

public class Person
{
    public ObjectId _id { get; set; }
    public Address Address { get; set; }
    public int Age { get; set; }
    public Person Father { get; set; }
    public string ID { get; set; }
    public double Income { get; set; }
    public string Name { get; set; }
}


public class Address
{
    public ObjectId _id { get; set; }
    public int HouseNo { get; set; }
    public int ID { get; set; }
    public string Street { get; set; }
}

You can then easily perform the requested query (to modify your existing query)

var personcollection = this.cdb.GetCollection<Person>("person");
var aggregate = personcollection.Aggregate()
            .Group(new BsonDocument { { "_id", "$Address.Street" }, { "sum", new BsonDocument("$sum", "$Income") } });
 var results = await aggregate.ToListAsync();

Notice the use of dot notation to reach inside embedded documents - Address.Street

answered Sep 2, 2015 at 8:31

Alex

38.6k55 gold badges217 silver badges340 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

thewayman Over a year ago

I know this can be done. I need to know if this can be done without embedding the address field inside the person.

Teilmann Over a year ago

What is addresses are pre-defined? Are we then going to have a collection with Address documents, aswell as having the Address documents embedded on persons?

Jason Buxton · Accepted Answer · 2016-06-17 15:53:06Z

1

I know it's an old thread - but I was composing a question relating to 'lookup' and I found this. If someone arrives by way of search, it's worth noting that the plumbing is now in place for 'joins' in MongoDB.

If you have the proper MongoDB and driver versions (3.2 and 2.2, respectively - I think), then you can use 'lookup' in the aggregation pipeline to join the two tables on ID.

answered Jun 17, 2016 at 15:53

Jason Buxton

1531 silver badge8 bronze badges

Comments

Rogerio Azevedo · Accepted Answer · 2019-04-03 18:21:09Z

I did lookup this way:

MongoDB

db.dbACESSO.aggregate([

{$match: {PartnerId: "2021", CD_CLIENTE: 4003}},

{$lookup: {from: "GRUPO_UNIDADE", localField: "CD_GRUPO_UNIDADE", foreignField: "CD_GRUPO_UNIDADE", as: "GRUPO"}},
{$lookup:{from: "UNIDADE", localField: "CD_UNIDADE", foreignField: "CD_UNIDADE",as: "UNIDADE"}},

{$unwind: "$GRUPO"},
{$unwind: "$UNIDADE"},

{ $project: { _id: 0, CD_CLIENTE : 1, CD_ACESSO : 1, NOME : 1, EMAIL : 1, FG_KIPER_MOBILE : 1, CD_GRUPO_UNIDADE : 1, CD_UNIDADE : 1, 
    GRUPO: "$GRUPO.NM_DESCRICAO", UNIDADE : "$UNIDADE.NM_DESCRICAO", 
    NU_TELEFONE: { $cond: [{ $eq : ["$NU_TELEFONE", { }] }, "", "$NU_TELEFONE"] }, 
    TAG: { $cond: [{ $eq: ["$NU_KIPER_TAG", { }] }, 0, 1] }, 
    CONTROLE: { $cond: [{ $eq: ["$NU_KIPER_RF", { }] }, 0, 1] }, 
    APPATIVO: { $cond: [{ $eq: ["$KEY_HASH", { }] }, "", "$KEY_HASH"] } } 
}   

])

C# Driver

var match = new BsonDocument { { "$match", new BsonDocument { { "PartnerId", cliente }, { "CD_CLIENTE", codCond } } } };
            var lookup1 = new BsonDocument { { "$lookup", new BsonDocument { { "from", "GRUPO_UNIDADE" }, { "localField", "CD_GRUPO_UNIDADE" }, { "foreignField", "CD_GRUPO_UNIDADE" }, { "as", "GRUPO" } } } };
            var lookup2 = new BsonDocument { { "$lookup", new BsonDocument { { "from", "UNIDADE" }, { "localField", "CD_UNIDADE" }, { "foreignField", "CD_UNIDADE" }, { "as", "UNIDADE" } } } };
            var unwind1 = new BsonDocument("$unwind", "$GRUPO");
            var unwind2 = new BsonDocument("$unwind", "$UNIDADE");

            var project = new BsonDocument
            {
                {
                    "$project", new BsonDocument
                    {
                        { "_id", 0},
                        { "CD_CLIENTE", 1},
                        { "CD_ACESSO", 1 },
                        { "NOME", 1},
                        { "EMAIL", 1 },
                        { "FG_KIPER_MOBILE", 1 },
                        { "CD_GRUPO_UNIDADE", 1 },
                        { "CD_UNIDADE", 1 },
                        { "GRUPO", "$GRUPO.NM_DESCRICAO" },
                        { "UNIDADE", "$UNIDADE.NM_DESCRICAO" },                        
                        { "NU_TELEFONE", new BsonDocument{{ "$cond", new BsonArray{new BsonDocument{{"$eq", new BsonArray{ "$NU_TELEFONE", new BsonDocument { } } }}, "","$NU_TELEFONE" } }}},
                        { "TAG", new BsonDocument{{ "$cond", new BsonArray{new BsonDocument{{"$eq", new BsonArray{ "$NU_KIPER_TAG", new BsonDocument { } } }}, 0, 1 } }}},
                        { "CONTROLE", new BsonDocument{{ "$cond", new BsonArray{new BsonDocument{{"$eq", new BsonArray{ "$NU_KIPER_RF", new BsonDocument { } } }}, 0, 1 } }}},
                        { "APP", new BsonDocument{{ "$cond", new BsonArray{new BsonDocument{{"$eq", new BsonArray{ "$FG_KIPER_MOBILE", false } }}, 0, 1 } }}},
                        { "APPATIVO", new BsonDocument{{ "$cond", new BsonArray{new BsonDocument{{"$eq", new BsonArray{ "$KEY_HASH", new BsonDocument { } } }}, "", "$KEY_HASH" } }}}
                    }
                }
            };

            var pipeline = new[] { match, lookup1, lookup2, unwind1, unwind2, project };
            var result = collection.Aggregate<BsonDocument>(pipeline).ToList();
            var lista = JsonConvert.DeserializeObject<List<UsuariosAcessos>>(result.ToJson()).ToList();

Collectives™ on Stack Overflow

mongodb c# driver join query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related