CODEWITHSUNDEEP
mongodbaggregation-framework
Ashniu123
Ashniu123

Reputation: 407

mongoose group with subdocument properties

I was trying something with events and had to run a group by query but I couldn't get through it because it was mostly on subdocument.

Here is my DB currently:

{ 
"_id" : ObjectId("5bbb6e1c7f3fc62db401d05a"), 
"type" : "ADD_BOOK", 
"metadata" : {
    "_id" : ObjectId("5bbb6e1c7f3fc62db401d05b"), 
    "name" : "ad", 
    "author" : "ad", 
    "imgUrl" : "https://vignette.wikia.nocookie.net/simpsons/images/6/60/No_Image_Available.png"
}, 
"timestamp" : 1539010076240.0, 
"__v" : NumberInt(0)
},
{ 
"_id" : ObjectId("5bbb736ad441d505589ccecc"), 
"type" : "ADD_BOOK", 
"metadata" : {
    "_id" : ObjectId("5bbb736ad441d505589ccecb"), 
    "name" : "ada", 
    "author" : "ada", 
    "imgUrl" : "https://vignette.wikia.nocookie.net/simpsons/images/6/60/No_Image_Available.png"
}, 
"timestamp" : 1539011434826.0, 
"__v" : NumberInt(0)
},
{ 
"_id" : ObjectId("5bbb7373d441d505589ccecd"), 
"type" : "DELETE_BOOK", 
"metadata" : {
    "_id" : "5bbb736ad441d505589ccecb"
}, 
"timestamp" : 1539011443792.0, 
"__v" : NumberInt(0)
}

and I want the output to be like:

[
  {
    "_id": ObjectId("5bbb736ad441d505589ccecb"),
    "books": [
        {
            "type": "ADD_BOOK",
            "metadata": {
                "_id": ObjectId("5bbb736ad441d505589ccecb"),
                "name": "ada",
                "author": "ada",
                "imgUrl": "https://vignette.wikia.nocookie.net/simpsons/images/6/60/No_Image_Available.png"
            }
        }
        {
            "type": "DELETE_BOOK",
            "metadata": {
              "_id": ObjectId("5bbb736ad441d505589ccecb")
            }
        }
    ]
  },
  {
    "_id": ObjectId("5bbb6e1c7f3fc62db401d05b"),
    "books": [
        {
            "type": "ADD_BOOK",
            "metadata": {
                "_id": ObjectId("5bbb6e1c7f3fc62db401d05b"),
                "name": "ad",
                "author": "ad",
                "imgUrl": "https://vignette.wikia.nocookie.net/simpsons/images/6/60/No_Image_Available.png"
            }
        }
    ]
  }
]

My current implementation tries something like this:

db.bookevents.aggregate([
    { $match: { }},
    { $group: { _id: "$metadata._id", books: { $push: "$$ROOT" } } }
])

This doesn't work as expected. I am trying to read the documentation but I can't figure this one out yet.

Is it possible that the solution to this is something else and not $group? Also can we put a $sort on timestamp for each books[]?

P.S. I know the question is mostly code but I don't know how to put it in any other way.

Upvotes: 1

Views: 735

Answers (1)

mickl
mickl

Reputation: 49945

You're getting 3 groups instead of expected 2 because metadata._id for DELETE_BOOK is of type string whereas all the others are of type ObjectId. You have to manually fix that or if can use MongoDB 4.0 then you can use $toObjectId operator to convert that value before $group. Additionally you can apply $sort before grouping and get rid of unnecessary fields using $project. Try:

db.bookevents.aggregate([
    { $addFields: { "metadata._id": { $toObjectId: "$metadata._id" } } }, // or fix manually
    { $sort: { timestamp: 1 } },
    { $group: { _id: "$metadata._id", books: { $push: "$$ROOT" } } },
    { $project: { "books.timestamp": 0, "books.value": 0, "books.__v": 0 } }
])

Upvotes: 1

Related Questions