CODEWITHSUNDEEP
mongodb
Henrique Barcelos
Henrique Barcelos

Reputation: 7900

Mongo query for field name

I have a collection that summarizes some data related to documents in other collections. It's structure is roughly like:

{
    campaignId : ObjectId(...)
    impressions : {
        ...
        '2016-09-20': 1800,
        '2016-09-21': 1500,
        '2016-09-22': 2000
    },
    clicks : {
        ...
        '2016-09-20': 60,
        '2016-09-21': 55,
        '2016-09-22': 80
    }
}

I realize there are better ways of doing it, but it's something I can't control.

The issue is that I need to query for documents that had impressions in the previous 7 days.

So I need to query based on the field key instead of the value.

Is it possible doing this in a query?

Upvotes: 2

Views: 3843

Answers (2)

Rahul Kumar
Rahul Kumar

Reputation: 2831

Interesting.

I think you have to dynamically create a or query with $exists with seven dates as an argument, I don't think there is any efficient way to query on field by conditions like lte or gte.

db.campaign.find({$or : [{impressions.2016-09-21 : {$exists : true}},{impressions.2016-09-20 : {$exists : true}},{impressions.2016-09-19 : {$exists : true}}....]})

I know that you cannot hard code the dates in the query because last 7 days would be dynamic, but you can easily use a function for creating dynamic query string and then use that for query.

Another way (which I might not work but deserves a shot) is trying for $where in the query, $where accepts a function, you can try to manipulate the function to achieve the needed, It would be messy but all you have to do is use 'this' as document, return true if the document validate your condition otherwise return false.

All in all there is no easy way around this.

Upvotes: 0

RootHacker
RootHacker

Reputation: 1107

As you need to query based on the field key instead of the value you first need to provide keys for all previous N days(7 days in your case). After building these keys(programmatically or manually ), you can achieve this in different ways -----

1 - Using $where

db.collectionName.find({$where: function() { 
  var previous2days = ["2016-09-21","2016-09-20"] 
  for (var field in this.impressions) { 
      if(previous2days.indexOf(field) > -1) return true; 
  }
  return false;
}})

2. Using $exists

db.getCollection('Test').find({
 "$or":
    [
     { "impressions.2016-09-21" : { "$exists": true } },
     { "impressions.2016-09-20" : { "$exists": true } }
     //add more conditional objects to consider data for more previous dates
    ]
})

To build this kind of query programmatically (taking JavaScript for this example to build query)

var previous2days = ["2016-09-21","2016-09-20"]
var query = { "$or" : [] }
for(var key in previous2days) {
    var q = {}
    q["impressions."+previous2days[key]] = { "$exists" : true }
    query.$or.push(q)
}
console.log(JSON.stringify(query))

This will give you the final query in required format

{"$or":[{"impressions.2016-09-21":{"$exists":true}},{"impressions.2016-09-20":{"$exists":true}}]}

Upvotes: 1

Related Questions