在Golang中从MongoDB的嵌套数组中检索指定时间范围内的值。

huangapple go评论91阅读模式
英文:

retrieve values between range time mongodb in an embedded array golang

问题

这是我的mongodb数据库:

"_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
        }
    },
    ... 这个数组非常庞大!

我想从"history"中检索一些值,限定在一个日期范围内。

首先,我需要选择我想要访问的"userid"对象,然后选择"history"。然后获取我所知道的日期范围内的值。

我正在使用带有mgo.v2(mongodb)驱动的Golang。

这是我的代码:

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

// 这里我将时间解析为time.Time格式
t_startTime, err := time.Parse(time.RFC3339Nano, startTime)
t_endTime, err := time.Parse(time.RFC3339Nano, endTime)
oid := bson.ObjectIdHex(id)
// 我选择我想要的时间范围
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)
}

我得到了一个错误:

无法规范化查询:BadValue 不支持的投影选项:history: { time: { $gte: new Date(1484819854576), $lte: new Date(1484819854576) } }

有人可以帮忙吗?

英文:

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 ?

答案1

得分: 0

  1. 在编写代码之前,你可以阅读文档。

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

    Select函数用于选择应该检索的字段。例如,以下查询只会检索name字段:

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

    相关文档:

    https://docs.mongodb.com/v3.2/tutorial/project-fields-from-query-results/#return-the-specified-fields-and-the-id-field-only(链接已更改为实际链接)

    来源:https://godoc.org/gopkg.in/mgo.v2#Query.Select

    所以你的Select(selector)与你尝试进行的查询无关,我很确定它会引发错误。

  2. 你的查询完全错误。要获取数据,你应该使用聚合框架并执行以下操作:

    1. 选择具有所需userid的文档。
    2. 展开历史数组。
    3. 查找所需的历史条目。

    MongoDB查询的简短版本(如果你只想获取历史条目,则不包括$project步骤)如下所示(请自行将其转换为Go代码):

    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")} }}
    )
    

    如果history数组非常庞大(正如你所写的),这个操作将非常慢。我不知道如果最大BSON大小为16MB,这个数组如何会非常庞大,但没关系(我希望在决定将用户的历史记录存储在嵌套数组中时,你已经阅读了有关文档大小限制的文档)。

    不像看起来那么简单吧?给自己一个方便 - 只需使用关系型数据库,并通过对history表进行单个查询来检索此数据,该表具有索引的timeuser_id字段。

英文:
  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.

huangapple
  • 本文由 发表于 2017年1月20日 03:01:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/41749657.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定