2

I have documents which further have arrays. I want to query an array returned as a result of a previous query. For example I have

{
{_id : 001,
 data: [{
        value: 1,
        name: 'Roger'
       },
       {value: 2,
        name: 'Albert'
       },
       {value: 3,
        name: 'Allen'
       }]
},
{_id: 002,
data: [{value: 4,
        name: 'Allison'
       },
       {value: 5,
        name: 'Tom'
       }]
}
}

I can get document where _id is equal to 001 but I want to query it's data field where value is equal to 2 and 3. I don't know what is the solution for that. In SQL It could be performed with Sub-queries but I don't know how to do it in mongodb.

Salvador Dali
  • 214,103
  • 147
  • 703
  • 753
M Omayr
  • 1,351
  • 1
  • 9
  • 19
  • 1
    To be clear, do you mean you only want to get the matching elements of the `data` array where `value` is 2 and 3 ? If so, see the Aggregation Framework example on: [extract only the selected item in array](http://stackoverflow.com/questions/3985214). – Stennie Dec 16 '13 at 06:07
  • Yes absolutely I want to get only matching elements of 'data' array. Because mongodb only returns a single document therefore I'm having this problem. So aggregation is the solution? – M Omayr Dec 16 '13 at 06:19
  • 1
    Yes, you'll be best using Aggregation Framework if you only want the MongoDB server to return the matching `data` array elements rather than the whole `data` arrays. – Stennie Dec 16 '13 at 06:55
  • @Stennie I'm querying this collection as 'db.sample.find({}, {_id:0, data:{$elemMatch: value:{$gt:1, $lte:3}}})' but It only returns a single element of data array. Kindly help – M Omayr Dec 16 '13 at 06:58
  • Thank you very much for your suggestion. I'm going to have a look on aggregation framework now. Actually I have data array having hundreds of elements so I want to get only matching elements on the basis of my query. Regards – M Omayr Dec 16 '13 at 07:02
  • Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) – Blakes Seven Mar 05 '16 at 03:00

2 Answers2

2

Maybe you should use $elemMatch: http://docs.mongodb.org/manual/reference/operator/query/elemMatch/

a good example: http://docs.mongodb.org/manual/reference/operator/query/all/

1.db.test.save({d: [{k1:1, v1:1}]}) 
2.db.test.save({d: [{k1:1, v1:1}]}) 
3.db.test.find({d:{$elemMatch:{k1:1}}})

return:

{ "_id" : ObjectId("52aea61c54125a39453c8836"), "d" : [ { "k1" : 1, "v1" : 1 } ] }
{ "_id" : ObjectId("52aea62054125a39453c8837"), "d" : [ { "k1" : 1, "v1" : 1 } ] }

Note that $elemMatch query is different from $elemMatch projection.

Mark_H
  • 770
  • 1
  • 6
  • 19
  • The [`$elemMatch` projection](http://docs.mongodb.org/manual/reference/operator/projection/elemMatch/) only returns the first matching array element. This use case is to return multiple matching elements, so the best server-side option (as at MongoDB 2.4) would be to use the Aggregation Framework. – Stennie Dec 16 '13 at 06:53
  • Yes this is my problem because $elemMatch only returns first matching element but I want to get multiple elements – M Omayr Dec 16 '13 at 07:04
  • But what I said was "$elemMatch Query", not projection. – Mark_H Dec 16 '13 at 07:05
  • 1.db.test.save({d: [{k1:1, v1:1}]}) 2.db.test.save({d: [{k1:1, v1:1}]}) 3.db.test.find({d:{$elemMatch:{k1:1}}}) All the two records will be returned. – Mark_H Dec 16 '13 at 07:06
  • 1
    Thanks for helping, I used $elemMatch Query also but possibly you took me wrong. I don't want to get multiple records, I want to get a single record which further has a field and that field has array. I want to query those array elements and get them. I posted my problem above and lets suppose I want to get document where _id is equal to 001 and now I want to query data array and want to get only matching elements of that array. Hope you can help now – M Omayr Dec 16 '13 at 07:24
0

Inserted 2 records in 2 different collections for join :

> db.employee.insert({eid:1,name:"premaseem"})
WriteResult({ "nInserted" : 1 })
> db.salary.insert({ eid:1, salary:6000 })
WriteResult({ "nInserted" : 1 })

Validated data in 2 tables :

> db.salary.find({ eid:1})
{ "_id" : ObjectId("56da1a5b2253b2199c53025b"), "eid" : 1, "salary" : 6000 }
> db.salary.find({ eid: db.employee.find({eid:1})  })
> db.employee.find({name : "prem" })
{ "_id" : ObjectId("56da19d42253b2199c53025a"), "eid" : 1, "name" : "prem" }

simulated join to get salary for employee premaseem :

> db.employee.find({name : "premaseem" }).map(function(d){  var obj =  db.salary.findOne({eid : d.eid  }); print(obj.salary)  }  )
6000
Manish sharma
  • 838
  • 2
  • 8
  • 24
Aseem Jain
  • 333
  • 2
  • 7
  • This does not answer the question that was asked. There is data in the question and it is not asking to "join" data from two tables in any way. – Blakes Seven Mar 05 '16 at 03:01