在mongodb中的单个查询中获取最小值和最大值 [英] Get min and max value in single query in mongodb

问题描述

考虑以下单词文档：

[{
  _id: 1,
  usages: 2,
  word: "Name"
}, {
  _id: 2,
  usages: 1,
  word: "Street"
}, {
  _id: 3,
  usages: 1,
  word: "House"
}, {
  _id: 4,
  usages: 3,
  word: "Table"
}, {
  _id: 5,
  usages: 3,
  word: "Bread"
}, {
  _id: 6,
  usages: 4,
  word: "Door"
}]

如何获得使用次数最低或最高的所有记录？最低的应返回ID 2和3（及其单词），最高的应返回ID 6及其单词。

How can i get all the records where the number of usages is the lowest or highest? Lowest should return id 2 and 3(and their word), highest should return id 6 with its word.

我需要将此数据汇总到一个随机的最低价/最高记录（精确到50条），因此需要汇总。

I need to aggregate this data into a random amount of lowest/highest records (50 to be exact) so it needs to be an aggregate.

应该是单个查询，因此无法使用带有$ max或$ min的另一个查询来找到最小/最大。

It should be a single lookup, so the min/max cannot be found using another query with $max or $min.

MongoDB版本为3.4.7，Mongoose版本5.0.0-rc1。由于我可以使用原始查询，因此不需要Mongoose解决方案。 （但是，这是首选！）

The MongoDB version is 3.4.7, Mongoose version 5.0.0-rc1. Mongoose solution not requred since I can use a raw query. (It is preferred however!)

示例：

Words.aggregate([ 
  { 
    $match: { _
      usages: { WHAT_SHOULD_I_HAVE_HERE }
    }
  }, { 
    $sample: { 
      size: 50 
    } 
  }
])

谢谢！

推荐答案

您可以尝试以下聚合方式

You can try below aggregation

$ facet 会给你用法的两个最低和最高值，您可以轻松地 $ project 使用 $ filter 运算符

$facet will give you the two lowest and highest value for the usages and you can easily $project through them using $filter operator

db.collection.aggregate([
  { "$facet": {
    "minUsages": [{ "$sort": { "usages": -1 } }],
    "maxUsages": [{ "$sort": { "usages": 1 } }]
  }},
  { "$addFields": {
    "lowestUsages": {
      "$arrayElemAt": ["$minUsages", 0]
    },
    "highestUsages": {
      "$arrayElemAt": ["$maxUsages", 0]
    }
  }},
  { "$project": {
    "minUsages": {
      "$filter": {
        "input": "$minUsages",
        "as": "minUsage",
        "cond": {
          "$eq": ["$$minUsage.usages", "$lowestUsages.usages"]
        }
      }
    },
    "maxUsages": {
      "$filter": {
        "input": "$maxUsages",
        "as": "maxUsage",
        "cond": {
          "$eq": ["$$maxUsage.usages", "$highestUsages.usages"]
        }
      }
    }
  }}
])

或者您也可以简单地通过 find 查询来做到这一点

Or you can simply do this with find query as well

const minUsage = await db.collection.find({}).sort({ "usages": -1 }).limit(1)
const maxUsage = await db.collection.find({}).sort({ "usages": 1 }).limit(1)

const minUsages = await db.collection.find({ "usages": { "$in": [minUsages[0].usages] } })
const maxUsages = await db.collection.find({ "usages": { "$in": [maxUsages[0].usages] } })

看看此处

这篇关于在mongodb中的单个查询中获取最小值和最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！