CODEWITHSUNDEEP
mongodbselectfind
iss42
iss42

Reputation: 2816

MongoDB: Query, return documents that contain at least some of the Select fields

Eg. The collection: { a: 1, b: 2, c: 3 } { a: 11, b: 12, c: 13 } { a: 111, b: 122 }

The query: db.records.find( {}, {b, c} )

What I am trying to get here is something like this: { b: 2, c: 3 } { b: 12, c: 13 } { b: 122, c: null }

But I am getting only: { b: 2, c: 3 } { b: 12, c: 13 }

Is it possible to get what I want in a single query to Mongo?

Upvotes: 0

Views: 73

Answers (2)

Neil Lunn
Neil Lunn

Reputation: 151112

This can be done using aggregate and the $ifNull operator:

db.records.aggregate([{$project: { _id: 0, b: 1, c: {$ifNull: ["$c" , null ]} }}])

It is still worth explaining the general effects of null, as there is a difference between whether a key exists in a document or if it contains null. Consider the following query output:

> db.records.find({},{ _id: 0, b:1, c:1})
{ "b" : 2, "c" : 3 }
{ "b" : 12, "c" : 13 }
{ "b" : 122 }

> db.records.find({ c:{$exists: true}},{ _id: 0, b:1, c:1}) 
{ "b" : 2, "c" : 3 } 
{ "b" : 12, "c" : 13 }

> db.records.find({ c:{$exists: false}},{ _id: 0, b:1, c:1})
{ "b" : 122 }
>

This shows the query with a projection for only the b and c fields, so that is what is returned in the first instance. The third document is returned because we didn't limit we just projected. Using $exists as a query modifier this can be filtered either way as the c field does not exist in one of the documents.

Let's suppose we alter the third document and then look at the queries again:

> db.records.update({ _id: ObjectId("52ed85ecb87e33d680adb0fa") }, {$set: { c: false }})

> db.records.find({},{ _id: 0, b:1, c:1})
{ "b" : 2, "c" : 3 }
{ "b" : 12, "c" : 13 }
{ "b" : 122, "c" : false }

> db.records.find({ c:{$exists: true}},{ _id: 0, b:1, c:1}) 
{ "b" : 2, "c" : 3 }
{ "b" : 12, "c" : 13 }
{ "b" : 122, "c" : false }

> db.records.find({ c: true },{ _id: 0, b:1, c:1})
>

> db.records.find({ c: false },{ _id: 0, b:1, c:1})
{ "b" : 122, "c" : false }
>

So after setting a false value for c we can see the field in the results. It responds to the exists operator because it is there, and if the query is changed to filter results it will return as appropriate.

More information on the link: http://docs.mongodb.org/manual/faq/developers/#faq-developers-query-for-nulls

Upvotes: 0

Zaid Masud
Zaid Masud

Reputation: 13443

Projections (second param in your find command) should be in this format: { b: 1, c: 1 }

I get output similar to what you're looking for with this syntax:

> db.records.find( { }, { _id: 0, b: 1, c: 1 } )
{ "b" : 2, "c" : 3 }
{ "b" : 12, "c" : 13 }
{ "b" : 122 }

Upvotes: 2

Related Questions