mongoDB $sort 不一致的结果

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

mongoDB $sort inconsistent results

问题

我在go语言中有以下查询，运行良好：

  1. query["name"] = bson.M{"$regex": searchStr, "$options": "i"}
  2. query["likes"] = userSession.Id
  3. c.Find(query).Skip(0).Limit(2).Select(bson.M{"name":1, "profile":1, "description":1, "user_id":1, "likes":1}).Sort("-pro", "-check").All(&business)

然后我尝试使用聚合框架编写相同的查询：

  1. query["name"] = bson.M{"$regex": searchStr, "$options": "i"}
  2. query["likes"] = userSession.Id
  3. oe := bson.M{
  4.     "$match" :query,
  5. }
  6. oa := bson.M{
  7.     "$project": bson.M {"pro": 1, "check": 1, "name":1, "profile":1, "description":1, "user_id":1, "likes":1, "nrLikes": bson.M{ "$size": "$likes" }, "city": 1, "country": 1, "industry": 1},
  8. }
  9. ol := bson.M{
  10.     "$limit" :pageSize,
  11. }
  12. os := bson.M{
  13.     "$skip" :skips,
  14. }
  15. or := bson.M{
  16.     "$sort" : bson.M {"pro": -1, "check": -1},
  17. }
  19. pipe := c.Pipe([]bson.M{oe, oa, or, os, ol  })
  21. pipe.All(&business)

第二个查询大部分时间都能正常工作，但有10%的时间会返回不同顺序的结果。

有什么想法吗？

稍后编辑：以下是结果：

  1. []bson.M{
  2. {
  3.     "description": "<p>sasdfdasf</p>",
  4.     "profile":     []interface {}{
  5.         "rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg",
  6.     },
  7.     "likes": []interface {}{
  8.         "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  9.     },
  10.     "nrLikes":  int(1),
  11.     "name":     "ediloc.com2",
  12.     "city":     "Calimanesti",
  13.     "industry": "Automotive",
  14.     "_id":      "Yo\xd4f\x1a\xa9Q|w\tG^",
  15.     "user_id":  "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  16.     "country":  "Romania",
  17. },
  18. {
  19.     "_id":         "Yo\xc7\xd7\x1a\xa9Qy1[&#39;\xea",
  20.     "user_id":     "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  21.     "name":        "ediloc.com",
  22.     "country":     "Romania",
  23.     "description": "<p>a</p>",
  24.     "profile":     []interface {}{
  25.         "1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg",
  26.     },
  27.     "likes": []interface {}{
  28.         "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  29.     },
  30.     "nrLikes":  int(1),
  31.     "city":     "Calimanesti",
  32.     "industry": "Accounting",
  33. },
  34. }
  37. []bson.M{
  38. {
  39.     "likes": []interface {}{
  40.         "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  41.     },
  42.     "_id":     "Yo\xd4f\x1a\xa9Q|w\tG^",
  43.     "name":    "ediloc.com2",
  44.     "city":    "Calimanesti",
  45.     "country": "Romania",
  46.     "profile": []interface {}{
  47.         "rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg",
  48.     },
  49.     "user_id":     "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  50.     "industry":    "Automotive",
  51.     "nrLikes":     int(1),
  52. },
  53. {
  54.     "_id":      "Yo\xc7\xd7\x1a\xa9Qy1[&#39;\xea",
  55.     "user_id":  "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  56.     "industry": "Accounting",
  57.     "profile":  []interface {}{
  58.         "1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg",
  59.     },
  60.     "likes": []interface {}{
  61.         "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  62.     },
  63.     "nrLikes":     int(1),
  64.     "name":        "ediloc.com",
  65.     "city":        "Calimanesti",
  66.     "country":     "Romania",
  67.     "description": "<p>a</p>",
  68. },
  69. }
  72. []bson.M{
  73. {
  74.     "nrLikes":     int(1),
  75.     "user_id":     "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  76.     "description": "<p>a</p>",
  77.     "profile":     []interface {}{
  78.         "1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg",
  79.     },
  80.     "country":  "Romania",
  81.     "industry": "Accounting",
  82.     "likes":    []interface {}{
  83.         "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  84.     },
  85.     "_id":  "Yo\xc7\xd7\x1a\xa9Qy1[&#39;\xea",
  86.     "name": "ediloc.com",
  87.     "city": "Calimanesti",
  88. },
  89. {
  90.     "name":        "ediloc.com2",
  91.     "industry":    "Automotive",
  92.     "description": "<p>sasdfdasf</p>",
  93.     "likes":       []interface {}{
  94.         "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  95.     },
  96.     "user_id": "Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9",
  97.     "city":    "Calimanesti",
  98.     "country": "Romania",
  99.     "profile": []interface {}{
  100.         "rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg",
  101.     },
  102.     "nrLikes": int(1),
  103.     "_id":     "Yo\xd4f\x1a\xa9Q|w\tG^",
  104. },
  105. }
  107. pro和check字段是int32类型，具有较高pro字段编号的文档应优先于具有较高check字段的文档。
  109. <details>
  110. <summary>英文:</summary>
  112. I have the following query in go lang which works fine:
  114.     query[&quot;name&quot;] = bson.M{&quot;$regex&quot;: searchStr, &quot;$options&quot;: &quot;i&quot;}
  115.     query[&quot;likes&quot;] = userSession.Id
  116.     c.Find(query).Skip(0).Limit(2).Select(bson.M{&quot;name&quot;:1, &quot;profile&quot;:1, &quot;description&quot;:1, &quot;user_id&quot;:1, &quot;likes&quot;:1}).Sort(&quot;-pro&quot;, &quot;-check&quot;).All(&amp;business);
  118. Then I tried to write the same query using the aggregation framework:
  120.     query[&quot;name&quot;] = bson.M{&quot;$regex&quot;: searchStr, &quot;$options&quot;: &quot;i&quot;}
  121.     query[&quot;likes&quot;] = userSession.Id
  122.     oe := bson.M{
  123.         &quot;$match&quot; :query,
  124. 	}
  125. 	oa := bson.M{
  126.         &quot;$project&quot;: bson.M {&quot;pro&quot;: 1, &quot;check&quot;: 1, &quot;name&quot;:1, &quot;profile&quot;:1, &quot;description&quot;:1, &quot;user_id&quot;:1, &quot;likes&quot;:1, &quot;nrLikes&quot;: bson.M{ &quot;$size&quot;: &quot;$likes&quot; }, &quot;city&quot;: 1, &quot;country&quot;: 1, &quot;industry&quot;: 1},
  127. 	}
  128. 	ol := bson.M{
  129.         &quot;$limit&quot; :pageSize,
  130. 	}
  131. 	os := bson.M{
  132.         &quot;$skip&quot; :skips,
  133. 	}
  134. 	or := bson.M{
  135.         &quot;$sort&quot; : bson.M {&quot;pro&quot;: -1, &quot;check&quot;: -1},
  136. 	}
  138.     pipe := c.Pipe([]bson.M{oe, oa, or, os, ol  })
  139. 	
  140. 	pipe.All(&amp;business)
  143. The second query works fine 90% of the time, but 10% of the times it returns a different order for results. 
  145. Any thoughts?
  147. Later edit: Here are the resuls
  149.     []bson.M{
  150.     {
  151.         &quot;description&quot;: &quot;&lt;p&gt;sasdfdasf&lt;/p&gt;&quot;,
  152.         &quot;profile&quot;:     []interface {}{
  153.             &quot;rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg&quot;,
  154.         },
  155.         &quot;likes&quot;: []interface {}{
  156.             &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  157.         },
  158.         &quot;nrLikes&quot;:  int(1),
  159.         &quot;name&quot;:     &quot;ediloc.com2&quot;,
  160.         &quot;city&quot;:     &quot;Calimanesti&quot;,
  161.         &quot;industry&quot;: &quot;Automotive&quot;,
  162.         &quot;_id&quot;:      &quot;Yo\xd4f\x1a\xa9Q|w\tG^&quot;,
  163.         &quot;user_id&quot;:  &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  164.         &quot;country&quot;:  &quot;Romania&quot;,
  165.     },
  166.     {
  167.         &quot;_id&quot;:         &quot;Yo\xc7\xd7\x1a\xa9Qy1[&#39;\xea&quot;,
  168.         &quot;user_id&quot;:     &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  169.         &quot;name&quot;:        &quot;ediloc.com&quot;,
  170.         &quot;country&quot;:     &quot;Romania&quot;,
  171.         &quot;description&quot;: &quot;&lt;p&gt;a&lt;/p&gt;&quot;,
  172.         &quot;profile&quot;:     []interface {}{
  173.             &quot;1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg&quot;,
  174.         },
  175.         &quot;likes&quot;: []interface {}{
  176.             &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  177.         },
  178.         &quot;nrLikes&quot;:  int(1),
  179.         &quot;city&quot;:     &quot;Calimanesti&quot;,
  180.         &quot;industry&quot;: &quot;Accounting&quot;,
  181.     },
  182.     }
  185.     []bson.M{
  186.     {
  187.         &quot;likes&quot;: []interface {}{
  188.             &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  189.         },
  190.         &quot;_id&quot;:     &quot;Yo\xd4f\x1a\xa9Q|w\tG^&quot;,
  191.         &quot;name&quot;:    &quot;ediloc.com2&quot;,
  192.         &quot;city&quot;:    &quot;Calimanesti&quot;,
  193.         &quot;country&quot;: &quot;Romania&quot;,
  194.         &quot;profile&quot;: []interface {}{
  195.             &quot;rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg&quot;,
  196.         },
  197.         &quot;user_id&quot;:     &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  198.         &quot;industry&quot;:    &quot;Automotive&quot;,,
  199.         &quot;nrLikes&quot;:     int(1),
  200.     },
  201.     {
  202.         &quot;_id&quot;:      &quot;Yo\xc7\xd7\x1a\xa9Qy1[&#39;\xea&quot;,
  203.         &quot;user_id&quot;:  &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  204.         &quot;industry&quot;: &quot;Accounting&quot;,
  205.         &quot;profile&quot;:  []interface {}{
  206.             &quot;1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg&quot;,
  207.         },
  208.         &quot;likes&quot;: []interface {}{
  209.             &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  210.         },
  211.         &quot;nrLikes&quot;:     int(1),
  212.         &quot;name&quot;:        &quot;ediloc.com&quot;,
  213.         &quot;city&quot;:        &quot;Calimanesti&quot;,
  214.         &quot;country&quot;:     &quot;Romania&quot;,
  215.         &quot;description&quot;: &quot;&lt;p&gt;a&lt;/p&gt;&quot;,
  216.     },
  217.     }
  220.     []bson.M{
  221.     {
  222.         &quot;nrLikes&quot;:     int(1),
  223.         &quot;user_id&quot;:     &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  224.         &quot;description&quot;: &quot;&lt;p&gt;a&lt;/p&gt;&quot;,
  225.         &quot;profile&quot;:     []interface {}{
  226.             &quot;1ssSySNRZwGJJwqzXghL6qzAVfWZis1ssSySNRZwGJJwqzXghL6qzAVfWZis=1.jpg&quot;,
  227.         },
  228.         &quot;country&quot;:  &quot;Romania&quot;,
  229.         &quot;industry&quot;: &quot;Accounting&quot;,
  230.         &quot;likes&quot;:    []interface {}{
  231.             &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  232.         },
  233.         &quot;_id&quot;:  &quot;Yo\xc7\xd7\x1a\xa9Qy1[&#39;\xea&quot;,
  234.         &quot;name&quot;: &quot;ediloc.com&quot;,
  235.         &quot;city&quot;: &quot;Calimanesti&quot;,
  236.     },
  237.     {
  238.         &quot;name&quot;:        &quot;ediloc.com2&quot;,
  239.         &quot;industry&quot;:    &quot;Automotive&quot;,
  240.         &quot;description&quot;: &quot;&lt;p&gt;sasdfdasf&lt;/p&gt;&quot;,
  241.         &quot;likes&quot;:       []interface {}{
  242.             &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  243.         },
  244.         &quot;user_id&quot;: &quot;Yo\xc7;\x1a\xa9Qy\b\xb8\xa2\xf9&quot;,
  245.         &quot;city&quot;:    &quot;Calimanesti&quot;,
  246.         &quot;country&quot;: &quot;Romania&quot;,
  247.         &quot;profile&quot;: []interface {}{
  248.             &quot;rKwMmXPWheGczwvGn2TzSRU7jRorhorKwMmXPWheGczwvGn2TzSRU7jRorho=0.jpg&quot;,
  249.         },
  250.         &quot;nrLikes&quot;: int(1),
  251.         &quot;_id&quot;:     &quot;Yo\xd4f\x1a\xa9Q|w\tG^&quot;,
  252.     },
  253.     }
  255. Pro and check fields are in32, the documents with higher pro field number should have priority over the documents that have higher check fields.
  258. </details>
  261. # 答案1
  262. **得分**: 2
  264. 确保在限制和跳过阶段之前设置排序管道阶段。只有在排序输入上，才能可靠地获得相同的结果。
  266. **编辑**
  268. 意识到您正在使用`bson.M {&quot;pro&quot;: -1, &quot;check&quot;: -1}`来定义排序顺序。在Go中，映射的迭代顺序是未指定的，可能会发生变化。这可能是您得到不一致结果的原因。
  270. 尝试将其更改为`bson.D`，以便保持按列排序的顺序。
  272. 可以查看查询[Sort](http://bazaar.launchpad.net/+branch/mgo/v2/view/head:/session.go#L2130)方法如何从提供的字符串构建它。
  274. 对于您的用例，您可以将`or`变量更改为：
  276.     or := bson.M{
  277.         &quot;$sort&quot;: bson.D{
  278. 	    	bson.DocElem{Name: &quot;pro&quot;, Value: -1},
  279. 		    bson.DocElem{Name: &quot;check&quot;, Value: -1},
  280.          },
  281. 	}
  283. <details>
  284. <summary>英文:</summary>
  286. Make sure you have your sort pipeline stage *before* your limit &amp; skip stages. You can only reliably obtain the same results with limit/skip on sorted input. 
  288. **EDIT**
  290. Realised that you are using `bson.M {&quot;pro&quot;: -1, &quot;check&quot;: -1}` to define your sort order. The iteration order of a map is unspecified in Go and can change. Hence this is probably why you are getting inconsistent results.
  292. Try changing this to a `bson.D` so that the order of columns to sort by is maintained.
  294. It make help to see how the query [Sort](http://bazaar.launchpad.net/+branch/mgo/v2/view/head:/session.go#L2130) method constructs this from the strings you provide.
  296. For your use case, you would change the `or` variable to:
  298.     or := bson.M{
  299.         &quot;$sort&quot;: bson.D{
  300. 	    	bson.DocElem{Name: &quot;pro&quot;, Value: -1},
  301. 		    bson.DocElem{Name: &quot;check&quot;, Value: -1},
  302.          },
  303. 	}
  305. </details>

huangapple
  • 本文由 发表于 2017年7月26日 07:08:54
  • 转载请务必保留本文链接：https://go.coder-hub.com/45314871.html
  • database
  • go
  • mongodb
匿名

发表评论

匿名网友

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

确定