CODEWITHSUNDEEP
mongodbaggregation-framework
user11211122
user11211122

Reputation:

MongoDB $in inside $cond

I burned out my brain trying to group and count itens in MongoDB. There are lots of posts but no one is that i need.

This is an exemple based on: styvane answer

db.VIEW_HISTORICO.aggregate([

{ 
    $match: { PartnerId: "2021", DT_RECEBIDO: {$gte: "2019-03-10 00:00:00", $lte: "2019-03-12 23:59:59"}}   
},

{ 
    $group: { _id: null,

            Controle1: { $sum: {$cond: [{ $gt: ['$CD_EVENTO', 0]}, 1, 0]}},
            Controle2: { $sum: {$cond: [{ $lt: ['$CD_EVENTO', 0]}, 1, 0]}}

            }
}

])

I need to group based on $in and $match together, with more then one result.

ONE RESULT WORKING

db.VIEW_HISTORICO.aggregate([

{ 
    $match: { PartnerId: "2021", DT_RECEBIDO: {$gte: "2019-03-10 00:00:00", $lte: "2019-03-12 23:59:59"}, "CD_EVENTO": { $in: ["K127", "9027"] }, }   
},

{
    $count : "Controles"
}

])

Using MSSQL i got best perfermance this way:

SELECT 
            SUM(CASE WHEN CD_EVENTO = 'K102' THEN 1 ELSE 0 END) AS Interfone,
            SUM(CASE WHEN CD_EVENTO IN('9015', '9016', '9017', '9018', '9019', '9020', '9021', '9022', '9023', '9024', '9025', '9026', 'K154', 'K155') THEN 1 ELSE 0 END) AS Tag,
            SUM(CASE WHEN CD_EVENTO IN('9027', '9028', '9029', '9030', '9031', '9032', '9033', '9034', 'K127') THEN 1 ELSE 0 END) AS Controle,
            SUM(CASE WHEN CD_EVENTO IN('K203', 'K204') THEN 1 ELSE 0 END) AS QrCode,
            SUM(CASE WHEN CD_EVENTO IN('K183', 'K184') THEN 1 ELSE 0 END) AS Convite
            FROM VIEW_ANALYTICS
            WHERE DT_RECEBIDO BETWEEN GETDATE()-30 AND GETDATE()

I hard tryied but I couldnt translate my MSSQL to MongoDB. My query.

db.VIEW_HISTORICO.aggregate([

{ 
    $match: { PartnerId: "2021", DT_RECEBIDO: {$gte: "2019-03-10 00:00:00", $lte: "2019-03-12 23:59:59"}}   
},

{ 
    $group: { _id: null,

            Controle1: { $sum: {$cond: [{ "CD_EVENTO": { $in: ["K127", "9027"] }}, 1, 0]}},


            Controle2: { $sum: {$cond: [{ "CD_EVENTO": { $in: ["K154", "K155"] }}, 1, 0]}}

            }
}

])

Upvotes: 6

Views: 8579

Answers (1)

mickl
mickl

Reputation: 49945

Syntax for $in operator is a bit different than what you're trying to do here. It takes an array of two elements: first one represent a reference to a field (must start with dollar sign) and second one is an array of values, so your $group stage should look like below:

db.VIEW_HISTORICO.aggregate([
    { 
        $group: { _id: null,
                Controle1: { $sum: {$cond: [{ $in: ["$CD_EVENTO", ["K127", "9027"]]}, 1, 0]}},
                Controle2: { $sum: {$cond: [{ $in: ["$CD_EVENTO", ["K154", "K155"]]}, 1, 0]}},
                }
    }
])

Upvotes: 11

Related Questions