CODEWITHSUNDEEP
mongodbindexingmongodb-queryfilteringprojection
Bjornn Borg
Bjornn Borg

Reputation: 21

MongoDB projections and fields subset

I would like to use mongo projections in order to return less data to my application. I would like to know if it's possible.

Example:

user: {
  id: 123,
  some_list: [{x:1, y:2}, {x:3, y:4}],
  other_list: [{x:5, y:2}, {x:3, y:4}]
}

Given a query for user_id = 123 and some 'projection filter' like user.some_list.x = 1 and user.other_list.x = 1 is it possible to achieve the given result?

user: {
  id: 123,
  some_list: [{x:1, y:2}],
  other_list: []
}

The ideia is to make mongo work a little more and retrieve less data to the application. In some cases, we are discarding 80% of the elements of the collections at the application's side. So, it would be better not returning then at all.

Questions:

  1. Is it possible?
  2. How can I achieve this. $elemMatch doesn't seem to help me. I'm trying something with unwind, but not getting there
  3. If it's possible, can this projection filtering benefit from a index on user.some_list.x for example? Or not at all once the user was already found by its id?

Thank you.

Upvotes: 0

Views: 750

Answers (3)

dnickless
dnickless

Reputation: 10918

What you can do in MongoDB v3.0 is this:

db.collection.aggregate({
    $match: {
        "user.id": 123
    }
}, {
    $redact: {
        $cond: {
             if: {
                 $or: [ // those are the conditions for when to include a (sub-)document
                     "$user", // if it contains a "user" field (as is the case when we're on the top level
                     "$some_list", // if it contains a "some_list" field (would be the case for the "user" sub-document)
                     "$other_list", // the same here for the "other_list" field
                     { $eq: [ "$x", 1 ] } // and lastly, when we're looking at the innermost sub-documents, we only want to include items where "x" is equal to 1
                 ] 
             },
             then: "$$DESCEND", // descend into sub-document
             else: "$$PRUNE" // drop sub-document
        }
    }
})

Depending on your data setup what you could also do to simplify this query a little is to say: Include everything that does not have a "x" field or if it is present that it needs to be equal to 1 like so:

$redact: {
    $cond: {
         if: {
             $eq: [ { "$ifNull": [ "$x", 1 ] }, 1 ] // we only want to include items where "x" is equal to 1 or where "x" does not exist
         },
         then: "$$DESCEND", // descend into sub-document
         else: "$$PRUNE" // drop sub-document
    }
}

The index you suggested won't do anything for the $redact stage. You can benefit from it, however, if you change the $match stage at the start to get rid of all documents which don't match anyway like so:

$match: {
    "user.id": 123,
    "user.some_list.x": 1 // this will use your index
}

Upvotes: 1

Buzz Moschetti
Buzz Moschetti

Reputation: 7586

$filter is your friend here. Below produces the output you seek. Experiment with changing the $eq fields and target values to see more or less items in the array get picked up. Note how we $project the new fields (some_list and other_list) "on top of" the old ones, essentially replacing them with the filtered versions.

db.foo.aggregate([
{$match: {"user.id": 123}}
,{$project: { "user.some_list": { $filter: {
            input: "$user.some_list",
            as: "z",
            cond: {$eq: [ "$$z.x", 1 ]}
        }},
          "user.other_list": { $filter: {
    input: "$user.other_list",
            as: "z",
            cond: {$eq: [ "$$z.x", 1 ]}
              }}
}}
                ]);

Upvotes: 0

J Livengood
J Livengood

Reputation: 2738

Very possible.

With findOne, the query is the first argument and the projection is the second. In Node/Javascript (similar to bash):

db.collections('users').findOne( { 
    id = 123 
}, {
    other_list: 0
} )

Will return the who'll object without the other_list field. OR you could specify { some_list: 1 } as the projection and returned will be ONLY the _id and some_list

Upvotes: 0

Related Questions