What is the best way to cross check the record in multiple collections

Question

I am developing the application in which system will show the campaign content to users which he/s never seen. To keep the logs either the specific user seen the specific content or not, i am maintaining logs of user activity in MongoDB collection. Here's what a campaign record in the database looks like.

campaigns

{
    "campaign_id": "123",
    "campaign_image": "<image path>",
    "min_user_age": 10
}

Here is how the user's activity log collection like

user_activity

{
    "i123i12391i2391i23kjnsa": {
        "user_id": 12,
        "campaign_ad_id": 34234,
    },
    "xajeou424u2934y293y4293": {
        "user_id": 32423,
        "campaign_ad_id": 34234,
    },
    ...
}

The user_activity collection will have approx 7 million records and campaigns will have more than 10 thousand records. I will always have user's id to get the campaign to show him. Here i want to get only those campaigns from campaigns collection which are not seen by the specific user.

I can do like fetch records from campaigns and then check that their records do not exist in user_activity collection based on specific user_id.

$campaigns = db.getCollection('campaigns').find({});

Loop though $campaigns and make sure activity does not exist for particular campaign_id and user_id.

$isExists = db.getCollection('user_activity').find({"campaign_id": 34234, "user_id": 12});

What is the best way to get the campaigns that are not seen by the user?

I am using mongodb 3.4.4.

Thanks

Answer 1

You could do an aggregation pipeline using the lookup operation, where you only need to specify the user ID.

Would be something like this:

var my_user_id = "1234";
db.getCollection('campaigns').aggregate([
{
    $lookup:
    {         
        from: "user_activity",
        let: { campaign_id_var: "$campaign_id" },
        pipeline: [
        { $match:
            { $expr:
                { $and:
                    [
                    { $eq: [ "$user_id",  my_user_id ] },
                    { $eq: [ "$campaign_ad_id", "$$campaign_id_var" ] }
                    ]
                }
            }
        },
        { $project: { user_id: 1, campaign_ad_id: 0 } }
        ],
        as: "someactivity"
    }
},
{
    $match: { "someactivity": { $eq: [] } }
}
]);