CODEWITHSUNDEEP
c#mongodb
thewayman
thewayman

Reputation: 41

mongodb c# driver join query

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.

Upvotes: 0

Views: 4535

Answers (3)

Rogerio Azevedo
Rogerio Azevedo

Reputation: 794

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();

Upvotes: 0

Jason Buxton
Jason Buxton

Reputation: 153

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.

Upvotes: 1

Alex
Alex

Reputation: 38499

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

Upvotes: 1

Related Questions