Mongodb – the best way to cross check the record in multiple collections

mongodbnosqloptimizationperformancequery-performance

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

Best Answer

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: [] } }
}
]);

Related Solutions

MongoDB optimization on high data

First, let me say that with the information given it will be hard to get at the root cause - it is usually an iterative process that takes multiple attempts to track down the culprit. In the interest of answering your "what next?" portion of the question rather than identifying the root cause, read on.....

First, a couple of recommendations:

Get the host into MMS (it's free) - see http://mms.10gen.com - so you can graph stats over time and get a view of the issues without having to be sitting on the box running commands
Get munin-node installed too, so you can correlate ops etc. with IO (install docs for MMS explain this).

Next, a couple of quick checks for common causes:

What is your filesystem/kernel? - these generally need to be ext4/XFS and recent enough to have fallocate working (2.6.23 and 2.6.25 respectively) so that new file allocation is not slow
Assuming you don't get MMS and munin installed, get iostat output to match up with mongostat to determine if IO is the root cause for the bottleneck
Do you do any periodic batch updates that grow the documents significantly (i.e. that would cause moves)? Moves are expensive and can cause IO to get backed up
Is your disk up to the data volume you are writing to it? MongoDB fsyncs to disk every 60 seconds by default, if the volume that needs to be synced after 60 seconds is massive (say because of an insert spike) then you can also run into issues

That's not an exhaustive list, I have seen other issues cause this, but that should get you started down the right path.

Mongodb – Choosing shard key and friendly URL Ids for the MongoDB

Before you go further you need to answer a few questions

how do you represent files within folders into the database
how do you represent folders
do you have relations between folders (parent -> child)
how often do you expect to create folders and files
how often do you update existing files into folders and what is the number of files you update

Based on your answers you can have a write optimized schema or a read optimized schema. Write optimized is a schema that contains many entries that are very small or you can use built in operators like $inc over a collection. Read optimized is generally a larger collection like the one you described, into your scenario you could have very easy something like this (assuming all folders are at the same level)

{ "userid" : "email or id",
  [ 
     { "folder1" : [ "file1", "file2"] },  
     { "folder2" : [ "file3", "file4"] },
  ]
}

But with this schema it gets quite complicated if you need to link a folder to a parent folder ... But is obvious that the userid is the shard key.

Related Question