retrieve values between range time mongodb in an embedded array golang

Question

Here is my mongodb database :

"_id" : ObjectId("58808d735ba19c2797f486ca"),
"userid" : ObjectId("58808d735ba19c2797f486c9"),
"history" : [
    {
        "floorId" : "309cf96f-1812-44f6-8d94-d5ce2b8839be",
        "time" : ISODate("2017-01-19T09:57:34.572Z"),
        "position" : {
            "latitude" : 48.815267598833806,
            "longitude" : 2.3630101271630677
        },
        "pointcoordinates" : {
            "pointX" : 503.82333,
            "pointY" : 339.00385
        }
    },
    {
        "floorId" : "309cf96f-1812-44f6-8d94-d5ce2b8839be",
        "time" : ISODate("2017-01-19T09:57:34.574Z"),
        "position" : {
            "latitude" : 48.815267598833806,
            "longitude" : 2.3630101271630677
        },
        "pointcoordinates" : {
            "pointX" : 503.82333,
            "pointY" : 339.00385
        }
    }, ... This array is very huge !

I want to retrieve some values from "history" on a date range.

First of all, I need to select the object of the "userid" I want to access then select "history". Then get values between date range I know.

I'm using Golang with mgo.v2 (mongodb) driver.

Here is my code :

id := queryValues.Get("id")
startTime := queryValues.Get("startTime")
endTime := queryValues.Get("endTime")

//Here I get times in forme time.Time
t_startTime, err := time.Parse(time.RFC3339Nano, startTime)
t_endTime, err := time.Parse(time.RFC3339Nano, endTime)
oid := bson.ObjectIdHex(id)
//I select the range of time what I want
selector := bson.M{"history": bson.M{"time": bson.M{"$gte": t_startTime, "$lte": t_endTime}}}
if err := uc.session.DB("TEST").C("history").Find(bson.M{"userid": oid}).Select(selector).All(&history); err != nil {
        fmt.Println(err)
        SendError(w, "GetHistory", "Error retrieving history")
    } else {
        spew.Dump(history)
    }

I got an error :

Can't canonicalize query: BadValue Unsupported projection option: history: { time: { $gte: new Date(1484819854576), $lte: new Date(1484819854576) } }

Can someone please help ?

Answer 1

1. You could read the documentation before writing code.

   func (q *Query) Select(selector interface{}) *Query

   Select enables selecting which fields should be retrieved for the results found. For example, the following query would only retrieve the name field:

   err := collection.Find(nil).Select(bson.M{"name": 1}).One(&result)

   Relevant documentation:

   https://docs.mongodb.com/v3.2/tutorial/project-fields-from-query-results/#return-the-specified-fields-and-the-id-field-only (link is changed to actual one)

   Source

   So your Select(selector) is not related to the query you are trying to make and I'm pretty sure it raises your error.

2. Your query is completely wrong. To get the data you should use aggregation framework and do the next:

   1. Select documents with required userid
   2. Unwind history arrays
   3. Find required history entries

   The short version (it doesn't include $projec step if you want to get only history entries) of MongoDB query would be (convert it to Go by yourself):

   db.history.aggregate(
       {$match: { userid: ObjectId("58808d735ba19c2797f486c9") }},
       {$unwind: "$history"},
       {$match: { "history.time": {"$gte":ISODate("2017-01-17T09:57:34.574Z"), "$lte":ISODate("2017-01-20T09:57:34.574Z")} }}
   )
   

   If history array is "very huge" as you wrote this operation will be very slow. I have no idea how can this array be "very huge" if max BSON size is 16Mb, but ok (I hope you read the docs about document size limit when decided to store user's history within nested array).

   Not so easy as it seems? Do yourself a favor - just use RDBMS and retrieve this data in single query to history table with indexed time and user_id fields.