how can i write this query with mongodb?

Question

I want to write this query with mongodb

select   * 
from     tab1 a, tab2 c 
where    a.a_id = 2 
and      c.c_id = 3 
and      a.a_id = c.c_fk_account_id_created_by

I tried this code but didn't get a response:

$cursor = $collection->find(array('$and' => array(array("a_id" => 2), array("c_id" => 3))));

score 1 · Accepted Answer · answered Dec 07 '16 at 15:35

I will assume you have two collections, named tab1 and tab2 in the form of

tab1

   { 
        "_id" : ObjectId("58482a97a5fa273657ace535"), 
        "a_id" : NumberInt(2)
    }

tab2

{ 
    "_id" : ObjectId("58482acca5fa273657ace539"), 
    "c_id" : NumberInt(3), 
    "c_fk_account_id_created_by" : NumberInt(2)
}

You will need an aggregation query with two steps, first, $lookup to the second table, and second $match on the proper keys. Like this.

db.tab1.aggregate(
[
    {
        $lookup: {
            "from" : "tab2",
            "localField" : "a_id",
            "foreignField" : "c_fk_account_id_created_by",
            "as" : "c"
        }
    },
    {
        $match: {
            "a_id": 2,
            "c.c_id": 3             
        }
    },
]

);

This will give you an output like this

{ 
"_id" : ObjectId("58482a97a5fa273657ace535"), 
"a_id" : NumberInt(2), 
"c" : [
    {
        "_id" : ObjectId("58482acca5fa273657ace539"), 
        "c_id" : NumberInt(3), 
        "c_fk_account_id_created_by" : NumberInt(2)
    }
]

}

Good luck!

I like the answer from @Joshua and I'd suggest to use it. Just to show a different way... You get the answer also like this: `db.tab1.find().forEach( function (object) { var join=db.tab2.findOne({ c_fk_account_id_created_by: object._id, _id: 3 } ); if (join != null) { // here are the matches printjson(join) ; }else { // no match, nothing to do } });` — Michael Hoeller, Dec 07 '16 at 22:16
MBushveld, that is actually an interesting approach as well. It would at least be worth working through for the learning opportunity. — Joshua Lawrence Austill, Dec 07 '16 at 22:23

score 1 · Answer 2 · answered Dec 07 '16 at 15:44

I wrote an article on just this type of query:

MongoDB Aggregation Framework for T-SQL Pros #3: The $lookup Operator https://www.linkedin.com/pulse/mongodb-aggregation-framework-t-sql-pros-3-lookup-operator-finch

Essentially you are going to bring all documents from your second table into the results of the first table using the $lookup aggregation operator. You can then use the $match and $group operators to filter and aggregate your data.

It will go something like this:

db.tab1.aggregate([
{   $match:
      {  "tab1.a_id": 2 }
},
{   $lookup:
     {  from: "tab2",
        localField: "a_id",
        foreignField: "c_fk_account_id",
        as: "tab2_results"
     }
},
{   $match:
      {  "tab2_results.c_id": 3 }
}
]}

The matching joined documents will be added to the base table's document as an array. It acts as a LEFT join in that null values from the remote table are ignored and your base table document is still returned, only missing remote data.

Hope this helps!

Bill

Good call on matching a_id before the $lookup, I followed his example as closely as possible, but that is the right way to do it! — Joshua Lawrence Austill, Dec 07 '16 at 15:52

score 0 · Answer 3 · edited Dec 07 '16 at 20:42

Let's assume tab1 and tab2 have 3 fields each as a_id, aa1, aa2 and c_id, c_fk_account_id_created_by, cc1

The query will be as follows

db.tab1.aggregate([{$match:{a_id:2}},{$lookup:{from:'tab2', localField:'c_fk_account_id_created_by', foreignField:'a_id', as:'ccArray'}},{$unwind:'$ccArray'}, {$project:{a_id:1,aa1:1, aa2:1, c_id:'$ccArray.c_id',c_fk_account_id_created_by:'$ccArray.c_fk_account_id_created_by',cc1:'$ccArray.cc1'}},{$match:{c_id:3}}])

Explanation of the above query: As MongoDB doesn't allow to match from second table in the aggregation pipeline so we have to unwind the second table array and compare the value

select * from tab1 a, tab2 c where a.a_id = 2 ==> {$match:{a_id:2}} and c.c_id = 3 ==> (Cannot be done at first so it can be acheived as ) ==> {$unwind:'$ccArray'}, {$project:{a_id:1,aa1:1, aa2:1, c_id:'$ccArray.c_id',c_fk_account_id_created_by:'$ccArray.c_fk_account_id_created_by',cc1:'$ccArray.cc1'}},{$match:{c_id:3}} and a.a_id = c.c_fk_account_id_created_by ==> {$lookup:{from:'tab2', localField:'c_fk_account_id_created_by', foreignField:'a_id', as:'ccArray'}}

how can i write this query with mongodb?

3 Answers3

The query will be as follows