MongoDb: Project an element's property inside an array in a document with conditions

Question

I have the following array of collection of books. I'm trying to project a single property of an element in an array.

My query is projecting the text property in comments array for book of year 2012 for author A1.

Example books array:

[
    {
        book_id: "1",
        title:'B1',
        year: 2012,
        comments: [
            {
                author: "A1",
                text: "C1"
            }
        ]
    },
    {
        book_id: "2",
        title:'B2',
        year: 2012,
        comments: [
            {
                author: "A2",
                text: "C2.0"
            },
            {
                author: "A1",
                text: "C2.1"
            },
            {
                author: "A1",
                text: "C2.2"
            },

        ]
    },
    {
        book_id: "3",
        title:'B3',
        year: 2013,
        comments: [
            {
                author: "A1",
                text: "C3"
            }
        ]
    },
]

expected result


[
    "C1",
    "C2.1",
    "C2.2"
]

I tried the following:


// query using find

db.books.find(
{year:2012, comments: {$elemMatch : {author:"A1" } } },{_id:0,'comments.text':1}
)

// results
/* 1 */
{
    "comments" : [ 
        {
            "text" : "C1"
        }
    ]
}

/* 2 */
{
    "comments" : [ 
        {
            "text" : "C2.0"
        }, 
        {
            "text" : "C2.1"
        }, 
        {
            "text" : "C2.2"
        }
    ]
}


// query using aggregation


db.books.aggregate(
[
    {$match: {year:2012}},
    {
        $project:{
            comments: {
                $filter:{
                    input: "$comments",
                    as:'comment',
                    cond: {$eq:['$$comment.author','A1']}
                }
            }

        }
     }
])

// results in

/* 1 */
{
    "_id" : ObjectId("5d3446f9d9eac0ed968aad4b"),
    "comments" : [ 
        {
            "author" : "A1",
            "text" : "C1"
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("5d3446f9d9eac0ed968aad4c"),
    "comments" : [ 
        {
            "author" : "A1",
            "text" : "C2.1"
        }, 
        {
            "author" : "A1",
            "text" : "C2.2"
        }
    ]
}

Ideally you'd show us some of the existing code?

Zlatko
– Zlatko

2019-07-21 10:56:57 +00:00
Commented Jul 21, 2019 at 10:56 — Zlatko
– Zlatko, Commented Jul 21, 2019 at 10:56
@Zlatko I added some of the queries i tried

Ali Kleit
– Ali Kleit

2019-07-21 11:22:13 +00:00
Commented Jul 21, 2019 at 11:22 — Ali Kleit
– Ali Kleit, Commented Jul 21, 2019 at 11:22

Ashh · Accepted Answer · 2019-07-21 11:41:08Z

4

You can use below aggregation

db.collection.aggregate([
  { "$match": { "year": 2012, "comments": { "$elemMatch": { "author": "A1" }}}},
  { "$unwind": "$comments" },
  { "$match": { "year": 2012, "comments.author": "A1" }},
  { "$group": {
    "_id": null,
    "data": { "$push": "$comments.text" }
  }}
])

Output

[
  {
    "_id": null,
    "data": [
      "C1",
      "C2.1",
      "C2.2"
    ]
  }
]

You cannot return simple array string with mongodb because it only returns the BSON document rather than just array of elements.

edited Jul 21, 2019 at 11:41

answered Jul 21, 2019 at 11:28

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ali Kleit Over a year ago

thank you, but whats the purpose of the multiple $match, i removed the first match and got the same result. but when i delete the second $match keeping the first one ($elemMatch) the array adds the author A2 of comment C2.0. any idea why { "$match": { "year": 2012, "comments.author": "A1" }} workes over the one with $elemMatch ?

Ashh Over a year ago

@AliKleit First $match is to filter out the documents which do not have the "comments.author" == A1, then $unwind deconstruct the comments array into multiple documents and last need to use $match again to again filter out the documents which do not have "comments.author" == "A1". You can remove one by one stages from result and can check the behaviour of various stages. mongoplayground.net/p/hJ_Ee8jPjAh

Collectives™ on Stack Overflow

MongoDb: Project an element's property inside an array in a document with conditions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related