mongodb aggregate filter and count on nodejs

Question

I have this collection named "points"

[
    { 'guid': 'aaa' },
    { 'guid': 'bbb' },
    { 'guid': 'ccc' },
]

and I have another named "stream"

[
    { 'title': 'pippo', 'guid': 'aaa', 'email': 'pippo@disney'},
    { 'title': 'pluto', 'guid': 'aaa', 'email': 'pluto@disney'},
    { 'title': 'goofy', 'guid': 'bbb', 'email': 'goofy@disney'},
    { 'title': 'duck', 'guid': 'aaa', 'email': 'duck@disney'},
    { 'title': 'minnie', 'guid': 'ccc', 'email': 'minnie@disney'},
]

I need to do an aggregate query where I want to search email that contains letter "o". The results that I expect is this

[
    { 'guid': 'aaa', items: 2 },
    { 'guid': 'bbb', item: 1 },
    { 'guid': 'ccc', item: 0 },
]

I have already done this

db.getCollection('points').aggregate([
    {
        $lookup: {
            from: "stream",
            localField: "guid",
            foreignField: "guid",
            as: "items"
        }
    },
    {
        $project: {
            _id: 0,
            guid: 1,
            items: {
                $size: "$items"
            }
        }
    }
]);

If I were in mysql the query will be this

SELECT DISTINCT 
    points.guid, 
    (
        SELECT COUNT(*) 
        FROM stream 
        WHERE guid = stream.guid and stream.email like '%o%'
    ) AS items 
FROM points

4J41 · Accepted Answer · 2016-09-27T09:03:13.237

Answer is edited based on comment.

You can try the following aggregation pipeline.

db.getCollection("points").aggregate([
                        {"$lookup":{"from":"stream", "localField":"guid", "foreignField":"guid", as:"items"}},
                        {"$unwind":"$items"}, 
                        {"$group":{"_id": "$guid", "guid":{"$first":"$guid"}, "emails":{"$push":"$items.email"}}},
                        {"$project":{"guid" :1, "emails":{"$setUnion":["$emails", [{"$literal":"io"}]]}}}, 
                        {"$unwind":"$emails"}, 
                        {"$match":{"emails":/[io]/}}, 
                        {"$group":{"_id":"$guid", "items":{"$sum":1}}}, 
                        {"$project":{"_id":0, "guid":"$_id", "items":{"$add":["$items", -1]}}}
                    ])

Sample Output:

{ "items" : 3, "guid" : "aaa" }
{ "items" : 1, "guid" : "ccc" }
{ "items" : 1, "guid" : "bbb" }

Note: I have introduced a dummy email id - 'oi', that satisfies matching condition, to ensure that data from point collection are not lost. And to compensate this dummy field -1 is subtracted finally from the count.

well done, but if I need this SELECT COUNT(*) FROM stream WHERE guid = stream.guid and (stream.email like '%o%' or stream.email like '%i%') thanks — Mauro Sala, Sep 27 '16 at 05:45
@MauroSala: Updated my answer. But all your emails have "i" in "disney"... — 4J41, Sep 27 '16 at 06:04
I need to do a query with a lot of or like this: SELECT COUNT(*) FROM stream WHERE guid = stream.guid and (stream.email like '%o%' or stream.title like '%i%'), but your answer is correct — Mauro Sala, Sep 27 '16 at 14:15

mongodb aggregate filter and count on nodejs

1 Answers1