MongoDB select all where field value in a query list

Question

How to achieve below SQL in MongoShell?

Select TableA.* from TableA where TableA.FieldB in (select TableB.FieldValue from TableB)

Mongo doc gives some example of

db.inventory.find( { qty: { $in: [ 5, 15 ] } } )

I want that array be dynamically from another query. Is it possible?

Extending my question

I have a collection of bot names

bots collection

{
    "_id" : ObjectId("53266697c294991f57c36e42"),
    "name" : "teoma"
}

I have a collection of user traffic, in that traffic collection, I have a field useragent

userTraffic Collection

{
    "_id" : ObjectId("5325ee6efb91c0161cbe7b2c"),
    "hosttype" : "http",
    "useragent" : "Mediapartners-Google",
    "is_crawler" : false,
    "City" : "Mountain View",
    "State" : "CA",
    "Country" : "United States"
}

I want to select all user traffic documents where its useragent contains any name of bot collection

This is what I have come up with

var botArray = db.bots.find({},{name:1, _id:0}).toArray()

db.Sessions.find({
    useragent: {$in: botArray}
    },{ 
        ipaddress:1
        })

Here i believe it is doing equals to comparison, but I want it to do like %% comparison

Once I get the result, I want to do an update to that result set as is_crawler= true

Tried something like this, isn't helpful

db.bots.find().forEach( function(myBot) {
    db.Sessions.find({
        useragent: /myBot.name/
        },{ 
            ipaddress:1
            }) 
     });

Another way of looping through the records, but no match found.

var bots = db.bots.find( {
    $query: {}, 
    $orderby:{
        name:1}
        });
while( bots.hasNext()) {
    var bot = bots.next();
    //print(bot.name);
    var botName = bot.name.toLowerCase();
    print(botName);
     db.Sessions.find({
        useragent: /botName/,
        is_crawler:false
        },{ 
            start_date:1,
            ipaddress:1,
            useragent:1,
            City:1,
            State:1,
            Country:1,
            is_crawler:1,
            _id:0
        })
    }

Solution 1

Finally this is how I could accomplish it.

// Get a array with values for name field
var botArray = db.bots.find({},{name:1}).toArray();

// loop through another collection
        db.Sessions.find().forEach(function(sess){
            if(sess.is_crawler == false){ // check a condition
                 // loop in the above  array
                botArray.forEach(function(b){
                //check if exists in the array
                   if(String(sess.useragent).toUpperCase().indexOf(b.name.toUpperCase()) > -1){
                        db.Sessions.update({ _id : sess._id} // find by _id
                        ,{
                            is_crawler : true // set a update value
                            },
                            {
                                upsert:false // do update only
                            })
                    } 
                  });
            }
        });

Solution 2

Not in a single query it isn't.

There is nothing wrong with getting the results from a query and feeding that in as your in condition.

var list = db.collectionA.find({},{ "_id": 0, "field": 1 }).toArray();

results = db.collectionB.find({ "newfield": { "$in": list } });

But your actual purpose is not clear, as using SQL queries alone as the only example of what you want to achieve are generally not a good guide to answer the question. The main cause of this is that you probably should be modelling differently than as you do in relational. Otherwise, why use MongoDB at all?

I would suggest reading the documentation section on Data Modelling which shows several examples of how to approach common modelling cases.

Considering that information, then perhaps you can reconsider what you are modelling, and if you then have specific questions to other problems there, then feel free to ask your questions here.

Comments

[HaBo]

How to achieve same with like statement "newfield": { "$like": list }

[HaBo]

Extended my question, see if that make sense in Mongo Modeling