MongoDB: Filtering sub-array with $filter(aggregate)

Question

I'm beginning with MongoDB, so, sorry if my question is too basic. I have a collection in Mongo with the following structure:

{
    nome: "Diego", contas: [ 
    {
        banco: "itau",
        tipo: "corrente",
        investimentos: [{
            tipo: "fundo",
            transacoes: [{
                numero: 1,
                valor: 1000,
                data: "24/06/2017"
            },
            {
                numero: 2,
                valor: 1500,
                data: "01/02/2017"
            }]
        },
        {
            tipo: "poupanca",
            transacoes: [{
                numero: 1,
                valor: 600,
                data: "20/06/2017"
            }]
        }]
    },
    {
        banco: "bradesco",
        tipo: "poupanca",
        investimentos: []
    },
    {
        banco: "santander",
        tipo: "juridica",
        investimentos: [{
            tipo: "pic",
            transacoes: [{
                numero: 1,
                valor: 100,
                data: "20/06/2017"
            },
            {
                numero: 2,
                valor: 100,
                data: "20/05/2017"
            },
            {
                numero: 3,
                valor: 100,
                data: "20/05/2017"
            }]
        }]
    }]
}

And I want to filter the investimentos array of conta to contains only the entries where the field tipo is equals to fundo. There is my query:

db.teste.aggregate([
{
    $project: {
        nome: 1,
        "contas.investimentos": {
            $filter: {
                input: "$contas.investimentos",
                as: "investimento",
                cond: { $eq: ["$$investimento.tipo", "fundo"]}
            }
        }
    }
}]);

There is the result:

 {
    "_id" : ObjectId("59563cf574f77220ff2166ea"),
    "nome" : "Diego",
    "contas" : [ 
        {
            "investimentos" : []
        }, 
        {
            "investimentos" : []
        }, 
        {
            "investimentos" : []
        }
    ]
}

I can't undestand why result is null on field investimentos. I think there is something wrong in the way that iam handling with sub-arrays but i can't find where is the gap. Can someone help me, please ?

s7vr · Accepted Answer · 2017-06-30 14:19:22Z

1

You have to use $map to map contas values and reach to $filter investimentos.

Something like

db.teste.aggregate([{
    $project: {
        nome: 1,
        contas: {
            $map: {
                input: "$contas",
                as: "contas",
                in: {
                    banco: "$$contas.banco",
                    tipo: "$$contas.tipo",
                    investimentos: {
                        $filter: {
                            input: "$$contas.investimentos",
                            as: "investimento",
                            cond: {
                                $eq: ["$$investimento.tipo", "fundo"]
                            }
                        }
                    }
                }
            }
        }
    }
}]);

answered Jun 30, 2017 at 14:19

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

Collectives™ on Stack Overflow

MongoDB: Filtering sub-array with $filter(aggregate)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related