How to write mongodb query to query only a certain field from an array of documents?

Question

I've just started to learn mongodb and I'm stuck on a problem. I have the following collection schema:

{
     "address":string,
      "users" : [{"id":integer, "timestamp":integer}] //users is an array of documents

}

I want to write a query that will fetch me all the user IDs for a particular address given that they are within a certain time frame. I.e. conditions:

1) address : given_address

AND

2) given_timestamp <= timestamp <= given_timestamp + X

Follow up, How do I query ONLY the user IDs instead of all documents, I tried setting

$project:{address:0,users.id:1 , users.timestamp:0 }

but that threw me an error.

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-03-26 14:40:35Z

1

You should be using aggregation-pipeline for that :

db.collection.aggregate([
  /** Filter docs where address: "abcd" */
  { $match: { address: "abcd" } },
  /** Re-create 'users' array field with elements which match given condition,
   *  it will empty array if nothing matched */
  {
    $addFields: {
      users: {
        $filter: {
          input: "$users",
          cond: {
            $and: [
              { $gte: ["$$this.timestamp", 12] },
              { $lte: ["$$this.timestamp", 1500] }
            ]
          }
        }
      }
    }
  }
]);

Test : MongoDB-Playground

answered Mar 26, 2020 at 14:40

whoami - fakeFaceTrueSoul

18.1k6 gold badges36 silver badges51 bronze badges

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

Comments

for_stack · Accepted Answer · 2020-03-26 14:16:28Z

1

$projection is redundant.

Try this:

db.user.find({"$and" : [{"address" : "xxx"}, {"users.timestamp" : {"$gte" : 12}}, {"users.timestamp" : {"$lte" : 200}}]}, {"users.id" : 1})

Also by default, fields won't be returned, so you don't need to set {"address" : 0, "users.timestamp" : 0}

answered Mar 26, 2020 at 14:16

for_stack

23.3k4 gold badges44 silver badges57 bronze badges

1 Comment

Aseem Kannal Over a year ago

It didn't work as expected.. Here's the data entry that I have: ``` { "_id" : ObjectId("5e7cad5eea67369a03a58418"), "address" : "abcd", "users" : [ { "id" : 100, "timestamp" : 1000 }, { "id" : 200, "timestamp" : 2000 } ] } ``` In your query, I put address as "abcd" and greater than 12 and less than 1500. It still returned both the IDs, when it should've only returned the first one.

Collectives™ on Stack Overflow

How to write mongodb query to query only a certain field from an array of documents?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related