How to get just element that matches query in a mongo find operation?

Question

i've got some documents like this:

{
    "name" : "xxx",
    "address" : " ",
    "mail" : "",
    "url" : "",
    "pos" : {
        "lat" : yyy,
        "lng" : zzz
    },
    "rooms" : [ 
        {
            "_id" : ObjectId("540ce3f8e4b016292085b387"),
            "supplier" : "s1",
            "price" : 41000,
            "details" : [ 
                {
                    "price" : 25200,
                    "key" : "2-1"
                }, 
                {
                    "price" : 15800,
                    "key" : "2-0"
                }
            ]
        }, 
        {
            "_id" : ObjectId("540ce3f8e4b016292085b3fd"),
            "supplier" : "s2",
            "price" : 44900,
            "details" : [ 
                {
                    "price" : 27000,
                    "key" : "2-1"
                }, 
                {
                    "price" : 17900,
                    "key" : "2-0"
                }
            ]
        }, 
        {
            "_id" : ObjectId("540ce3f8e4b016292085b53d"),
            "supplier" : "s3",
            "price" : 53500,
            "details" : [ 
                {
                    "price" : 32100,
                    "key" : "2-1"
                }, 
                {
                    "price" : 21400,
                    "keykey" : "2-0"
                }
            ]
        }
    ]
}

What i need to do is execute a find with some filters AND price range query to fetch just the matching array elements and not all:

This is what i try:

var sort  = {};
var query = {name:new RegExp("xx",'i')};
query['$and'] = [{'rooms.price':{$gt:50000}},{'rooms.price':{$lt:100000}}];
var page  = 1;
var ppp   = 20; 

db.collection("myCollection").
  find(query).
  sort(sort).
  skip(page > 0 ? ((page-1)*ppp) : 0).limit(ppp).toArray(function(err, docs) {
    res.send(docs);
  });

and i retrieve the same document.

What i need is :

{
    "name" : "xxx",
    "address" : " ",
    "mail" : "",
    "url" : "",
    "pos" : {
        "lat" : yyy,
        "lng" : zzz
    },
    "rooms" : [ 
        {
            "_id" : ObjectId("540ce3f8e4b016292085b53d"),
            "supplier" : "s3",
            "price" : 53500,
            "details" : [ 
                {
                    "price" : 32100,
                    "key" : "2-1"
                }, 
                {
                    "price" : 21400,
                    "keykey" : "2-0"
                }
            ]
        }
    ]
}

I googled about but i find just aggregate or map/reduce element.

I'd like to avoid it.

Is there a good solution??

Thanks!

Answer 1

If you don't want to use aggregate or map/reduce then you could change your data structure. Extract rooms to it's own collection and make sure that every element has id reference to the appropriate document from myCollection. This would require at least two query though.

Alternatively, you could filter the content on the application side and not within mongodb.