CODEWITHSUNDEEP
mongodbmongodb-query
Arty
Arty

Reputation: 355

Query an element of an array and the ones before in MongoDB

Here is an example of my data.

{
        "_id" : ObjectId("5fa7d0e31abef4382b15efd0"),
        "users" : {
                "5fa667194f22083924eeca9e" : {
                        "toRead" : 0
                },
                "5fac51148566866156d09d36" : {
                        "toRead" : 1
                }
        },
        "messages" : [
                {
            "messageId": ObjectId("5fa667194f22083924eeca5e"),
                        "userId" : ObjectId("5fa667194f22083924eeca9e"),
                        "message" : "Hello"
                },
                {
            "messageId": ObjectId("5fa667194f22083924eeca6e"),
                        "userId" : ObjectId("5fac51148566866156d09d36"),
                        "message" : "Hi"
                },
                {
            "messageId": ObjectId("5fa667194f22083924eeca7e"),
                        "userId" : ObjectId("5fa667194f22083924eeca9e"),
                        "message" : "How are you ?"
                },
                {
            "messageId": ObjectId("5fa667194f22083924eeca8e"),
                        "userId" : ObjectId("5fac51148566866156d09d36"),
                        "message" : "Fine and you ?"
                },
                {
            "messageId": ObjectId("5fa667194f22083924eeca9f"),
                        "userId" : ObjectId("5fa667194f22083924eeca9e"),
                        "message" : "Fine thanks !"
                },
        ]
}

I would like to find the element of the array and two element before the one I found.

For example, if I'm looking for a message where :

messageId = ObjectId("5fa667194f22083924eeca8e")

The result should be an array "messages" containing a message where :

messageId = ObjectId("5fa667194f22083924eeca8e")

And two message before this one. In this example it would be :

messageId = ObjectId("5fa667194f22083924eeca7e") 
messageId = ObjectId("5fa667194f22083924eeca6e")

So the result would be :

        "messages" : [
                {
            "messageId": ObjectId("5fa667194f22083924eeca6e"),
                        "userId" : ObjectId("5fac51148566866156d09d36"),
                        "message" : "Hi"
                },
                {
            "messageId": ObjectId("5fa667194f22083924eeca7e"),
                        "userId" : ObjectId("5fa667194f22083924eeca9e"),
                        "message" : "How are you ?"
                },
                {
            "messageId": ObjectId("5fa667194f22083924eeca8e"),
                        "userId" : ObjectId("5fac51148566866156d09d36"),
                        "message" : "Fine and you ?"
                }
        ]

How can I do that ?

Upvotes: 1

Views: 40

Answers (1)

turivishal
turivishal

Reputation: 36104

  • $unwind deconstruct messages array
  • $match is messageId is less than or equal to your input messageId
  • $sort by messageId in descending order
  • $limit 3 documents
  • $group by _id and reconstruct messages array
db.collection.aggregate([
  { $unwind: "$messages" },
  {
    $match: {
      "messages.messageId": {
        $lte: ObjectId("5fa667194f22083924eeca8e")
      }
    }
  },
  { $sort: { "messages.messageId": -1 } },
  { $limit: 3 },
  {
    $group: {
      _id: "$_id",
      users: { $first: "$users" },
      messages: { $push: "$messages" }
    }
  }
])

Playground

Upvotes: 1

Related Questions