Find sub-documents using $in with MongoDB

Question

My task is to find individual authors(comments.user_id) comment on the article (_id)

{
    "_id" : ObjectId("56479d9c8510369a4ecea3a9"),
    "comments" : [ 
        {
            "text" : "222",
            "user_id" : ObjectId("563f2db0e2bf6c431b297d45"),
        }, 
        {
            "text" : "333",
            "user_id" : ObjectId("563f2db0e2bf6c431b297d45"),
        }, 
        {
            "text" : "444",
            "user_id" : ObjectId("563f2db0e2bf6c431b297d45"),
        }, 
        {
            "text" : "55555",
            "user_id" : ObjectId("563e3337e2bf6c431b297d41"),
        }, 
        {
            "text" : "00000",
            "user_id" : ObjectId("563f7c0a8db7963420cd5732"),
        }, 
        {
            "text" : "00001",
            "user_id" : ObjectId("563f7c0a8db7963420cd5732"),
        }
    ]
}

My query looks as follows

db.getCollection('messages').find({
  '_id': ObjectId("56479d9c8510369a4ecea3a9"),
  'comments.user_id': {$in : [
    ObjectId("563e3337e2bf6c431b297d41"),
    ObjectId("563f7c0a8db7963420cd5732")
  ]}
})

It returns all comments. Please help to understand why it happens.

Expected Result

{
    "_id" : ObjectId("56479d9c8510369a4ecea3a9"),
    "comments" : [ 
        {
            "text" : "55555",
            "user_id" : ObjectId("563e3337e2bf6c431b297d41"),
        }, 
        {
            "text" : "00000",
            "user_id" : ObjectId("563f7c0a8db7963420cd5732"),
        }, 
        {
            "text" : "00001",
            "user_id" : ObjectId("563f7c0a8db7963420cd5732"),
        }
    ]
}

update query (hopelessness)

db.getCollection('messages').find(
    {'_id': ObjectId("56479d9c8510369a4ecea3a9")},
    {'comments.user_id': {$in:  ["563f2db0e2bf6c431b297d45", "563e3337e2bf6c431b297d41"]}},
    {'comments.user_id': {$elemMatch: {$in:  ["563f2db0e2bf6c431b297d45", "563e3337e2bf6c431b297d41"]}}}
     )


db.getCollection('messages').find(
    {'_id': ObjectId("56479d9c8510369a4ecea3a9")},
     {comments: {$elemMatch: {'user_id': {$in : [ObjectId("563f2db0e2bf6c431b297d45"), ObjectId("563f7c0a8db7963420cd5732")]}}}}  
    )

I return only 1 record, and I have all the records from these authors

You might want to read [How do I ask a good question](http://stackoverflow.com/help/how-to-ask), which enhances the probability for getting a useful answer _drastically_. You might find [ESR](https://en.m.wikipedia.org/wiki/Eric_S._Raymond)'s excellent essay [How To Ask Questions The Smart Way](http://catb.org/~esr/faqs/smart-questions.html) helpful. Repeat: Read the documentation **before** asking a question. Since the document matches, it is returned. Use `elemMatch` and the positional `$`operator for projection. — Markus W Mahlberg, Nov 14 '15 at 19:43
Thanks for your reply `db.getCollection('messages').find( { '_id': ObjectId("564680c59bfd4a0a49745282"), 'comments.user_id': {$elemMatch: {$in: ["563e3337e2bf6c431b297d41", "563f2db0e2bf6c431b297d45"]}} })` try to realize what you have written, it is still not returns — Alex, Nov 14 '15 at 20:05
I have just made your code more readable – since it is your question, I did not change anything, of course. Please read the links I gave you and [edit your question](http://stackoverflow.com/posts/33712465/edit) accordingly. That is: provide sample documents and an expected output. — Markus W Mahlberg, Nov 14 '15 at 20:22
See [this answer](http://stackoverflow.com/a/24032549/1259510) to a similar question. — JohnnyHK, Nov 15 '15 at 05:09
@JohnnyHK Thanks, I did not get the desired results. the solution does not work $in and return only 1 sub document — Alex, Nov 15 '15 at 11:21

score 0 · Accepted Answer · answered Nov 15 '15 at 14:38

0

As you've seen, the $ and $elemMatch projection operators only include the first matching element.

To include multiple, filtered array elements in your projection of the comment array, your can use aggregate with the $redact operator instead of find:

db.getCollection('test').aggregate([
    {$match: {
        '_id': ObjectId("56479d9c8510369a4ecea3a9"),
        'comments.user_id': {$in : [
            ObjectId("563e3337e2bf6c431b297d41"),
            ObjectId("563f7c0a8db7963420cd5732")
        ]},
    }},
    {$redact: {
        $cond: {
            if: {
                $or: [
                    {$eq: ['$user_id', ObjectId("563e3337e2bf6c431b297d41")]},
                    {$eq: ['$user_id', ObjectId("563f7c0a8db7963420cd5732")]},
                    {$not: '$user_id'}
                ]
            },
            then: '$$DESCEND',
            else: '$$PRUNE'
        }
    }}
])

$redact iterates over each doc like a tree, keeping or trimming the fields of each doc as it's $cond expression dictates.

It gets a bit tricky to wrap your head around $redact, but it's basically saying that if the level's user_id field matches either of the two ObjectIds in your $in, or it's not present (i.e. as it is in the top level of the doc), include the data, otherwise remove it.

answered Nov 15 '15 at 14:38

JohnnyHK

305,182
66
621
471

Thank you for writing the decision, but I can not understand why I can not refer to the property `comments`, handling writes `undifined` – Alex Nov 16 '15 at 00:06
@Alex Sorry, I don't understand your comment. – JohnnyHK Nov 16 '15 at 01:02
@JohnyyHK, Sorry for my English, I mean that I can not appeal to data `comments`, return `undifined` `console.log(message[0].comments);` -->`undifined` – Alex Nov 16 '15 at 08:59
@JohnyyHk i remove `'comments.user_id': {$in: [ObjectId("563e3337e2bf6c431b297d41"), ObjectId("563f7c0a8db7963420cd5732")]}` in $match, and all was well! thank you, on no other choice in the mangoDB? – Alex Nov 17 '15 at 16:16
@Alex The [`$filter`](https://docs.mongodb.org/master/reference/operator/aggregation/filter/#exp._S_filter) array aggregation operator will make this easier in 3.2. – JohnnyHK Nov 17 '15 at 16:22
@JohnyyHk,Thank you then I will wait for 3.2 – Alex Nov 17 '15 at 16:35

Find sub-documents using $in with MongoDB

update query (hopelessness)

1 Answers1