0

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.

Bjornn Borg
  • 21
  • 6
  • *"$elemMatch doesn't seem to help me"* So what did you actually try? Seems that `db.user.find({},{ "some_list": { "$elemMatch": { "x": 1 } }, "other_list": { "$elemMatch": { "x": 1 } } })` does exactly what you ask. Note that there are **two** different [`$elemMatch`](https://docs.mongodb.com/manual/reference/operator/projection/elemMatch/) operators, where the linked one is the "projection" operator. You can do more fancy things with `.aggregate()`, but a simple "singular" match is handled by that very simple case. – Neil Lunn Oct 13 '17 at 00:51
  • I found this at the documentation: "The $elemMatch operator limits the contents of an field from the query results to contain only the first element matching the $elemMatch condition.". In my example there is one element maching the filter ```user.some_list.x = 1``` but in my real case, can existis several elements in the array matching the filtering criteria. – Bjornn Borg Oct 13 '17 at 02:25

3 Answers3

1

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
}
dnickless
  • 10,733
  • 1
  • 19
  • 34
  • Awesome! this looks like exactly what I'm looking for. I'm reading the docs to try to understand what is happening, LoL. One "strange" behavior, is that I have other collections (```another_collection```) and looks like it got filtered too, even not being listed in the redact. Can you imagine why? But it's does not look like a problem, because I will filter all the 4 collections in my document, and used only two in this toy example. – Bjornn Borg Oct 13 '17 at 13:27
  • I presume your real data looks a little bit different to the one you posted here. And the solution I suggested is somewhat shaky as the presence of fields with equal names on different levels of sub-documents can cause some unexpected behaviour. So I guess, you will need to experiment a little with your real data model to reach your goal (or upgrade MongoDB and use $filter which would be a good idea anyway). – dnickless Oct 13 '17 at 15:33
  • All my inner objects have a date field that will be used in the comparison. Heard to say if it's not present could cause errors, but it's not the case. I made some tests using all 4 real collections and worked like a charm. Thank you again. But my challenge now is to write it using the groovy driver language. I'm fighting hard with ```BasicDBObject``` and this kind of stuff. – Bjornn Borg Oct 13 '17 at 21:20
  • Have you seen this: https://stackoverflow.com/questions/39701591/mongodb-aggregation-command-to-java-code – dnickless Oct 14 '17 at 06:44
  • Looks like I'm getting some "side effect" with `redact`. Both `some_list` and `other_list` have inner collections. In my real example there are 4 collections, and for 1 of these, the inner collection is returning with size 0 (filtered somehow). Why is it happening? I would like to filter `user.some_list.x == 1`, and return it if true, but `user.some_list.inner_list` should be left intact. Any clue why did it happened? These inner lists have nothing to do with the outer objects (properties, etc). They don't have the property `x` and aren't meant to have. They are not the subject of the filter. – Bjornn Borg Oct 16 '17 at 19:37
  • See the updated answer. MongoDB will otherwise not "$$DESCEND" into your other object and hence "$$PRUNE" (as in "omit") it. – dnickless Oct 16 '17 at 20:01
  • Thanks for your support @dnickless, but I`m not getting there. Add ```inner_list``` to my ```or``` clause didn't change the result. If understand well, looks like mongo will try to match all the fields in my ```or``` clause against ```x eq 1``` and "include" it in the result if true or "drop" it otherwise. Is it right? If so, ```inner_list``` doesn't have a x property. Another "strange" behavior I noticed, is that if have only one collection in ```or``` clause (eg. some_list), it causes the other to got filtered too (eg. other_list). Is it expected? Will try post some prints when at the office – Bjornn Borg Oct 17 '17 at 12:55
0

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

J Livengood
  • 2,729
  • 1
  • 12
  • 26
  • Thanks for the answer. But I don't know in advance that I want to get rid of all elements of the other_list. I want to get rid of all elements that does not satisfies a given condition. In the example there will be 0 left for ```other_list``` and 1 for ```some_list```. I need to retrieve the whole ```user``` document, but filtering the nested collection ```some_list``` and ```other_list``` – Bjornn Borg Oct 13 '17 at 00:28
0

$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 ]}
              }}
}}
                ]);
Buzz Moschetti
  • 7,057
  • 3
  • 23
  • 33
  • After a `invalid operator '$filter'` error, I found this in the docs: "New in version 3.2." I'm using mongo 3.0. My bad not to tell it. I will try to see what I could use instead, but please provide another query if you can. Thank you. – Bjornn Borg Oct 13 '17 at 02:42