Querying inside a subdocument array in mongodb

Question

Am really new to MongoDB or NoSQL database. I have this userSchema schema

const postSchema = {
    title: String,
    posted_on: Date
}

const userSchema = {
    name: String,
    posts: [postSchema]
}

I want to retrieve the posts by a user in given range(/api/users/:userId/posts?from=date&to=date&limit=limit) using mongodb query. In a relational database, we generally create two different sets of tables and query the second table(posts) using some condition and get the required result. How can we achieve the same in mongodb? I have tried using $elemMatch by referring this but it doesn't seem to work.

Can you add the query you are trying to do? Also $elemMatch will return a single array element that matches the condition — PmmZenha
– PmmZenha, Commented Sep 13, 2021 at 16:48

Takis · Accepted Answer · 2021-09-13 18:39:33Z

2 ways to do it with aggregation framework, that can do much more than a find can do.

With find we mostly select documents from a collection, or project to keep some fields from a document that is selected, but here you need only some members of an array, so aggregation is used.

Local way (solution at document level) no unwind etc

Test code here

Query

filter the array and keep only posted_on >1 and <4 (i used numbers fro simplicity use dates its the same)
take the first 2 elements of the array (limit 2)

db.collection.aggregate([
  {
    "$match": {
      "name": {
        "$eq": "n1"
      }
    }
  },
  {
    "$set": {
      "posts": {
        "$slice": [
          {
            "$filter": {
              "input": "$posts",
              "cond": {
                "$and": [
                  {
                    "$gt": [
                      "$$this.posted_on",
                      1
                    ]
                  },
                  {
                    "$lt": [
                      "$$this.posted_on",
                      5
                    ]
                  }
                ]
              }
            }
          },
          2
        ]
      }
    }
  }
])

Uwind solution (solution at collection level) (its smaller a bit, but keeping things local is better, but in your case it doesn't matter)

Test code here

Query

match user
unwind the array, and make each member to be ROOT
match the dates >1 <4
limit 2

db.collection.aggregate([
  {
    "$match": {
      "name": {
        "$eq": "n1"
      }
    }
  },
  {
    "$unwind": {
      "path": "$posts"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$posts"
    }
  },
  {
    "$match": {
      "$and": [
        {
          "posted_on": {
            "$gt": 1
          }
        },
        {
          "posted_on": {
            "$lt": 5
          }
        }
      ]
    }
  },
  {
    "$limit": 2
  }
])

Collectives™ on Stack Overflow

Querying inside a subdocument array in mongodb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related