mongodb 根据参考Map计算子文档列表的最大值

mlmc2os5 于 2023-10-16 发布在 Go

关注(0)|答案(2)|浏览(101)

我有一个这样的文档结构：

[
    {
        "country": "UK",
        "shops": [
            {"city": "London", "fruits": ["banana", "apple"]},
            {"city": "Birmingham", "fruits": ["banana", "pineapple"]},
        ],
    },
    {
        "country": "DE",
        "shops": [
            {"city": "Munich", "fruits": ["banana", "strawberry"]},
            {"city": "Berlin", "fruits": ["kiwi", "pineapple"]},
        ],
    },
]

在我的python脚本中，我有一个dict将每个水果Map到一个类别值：

categories = {
    1: ["apple"],
    2: ["banana", "kiwi"],
    3: ["pineapple", "strawberry"]
}

已确认输出：

现在，我想使用mongo聚合框架，为每个文档获取max_category，它是根据categoryMap从shops子文档中找到的max值投影出来的。

[
    {
        "country": "UK",
        "shops": [
            {"city": "London", "fruits": ["banana", "apple"]},
            {"city": "Birmingham", "fruits": ["banana", "pineapple"]},
        ],
        "max_category": 3
    },
    {
        "country": "DE",
        "shops": [
            {"city": "Munich", "fruits": ["banana", "apple"]},
            {"city": "Berlin", "fruits": ["kiwi", "apple"]},
        ],
        "max_category": 2
    },
]

谢谢你的帮助！

mongodb

来源：https://stackoverflow.com/questions/77122446/calculate-max-value-for-list-of-subdocuments-based-on-reference-map

2条答案

按热度按时间

5w9g7ksd1#

你的category dict不适合mongo，因为mongo要求对象的键是字符串类型。您可以将dict转换为下面的形式以便于处理：

[
  {
    category: 1,
    fruits: [
      "apple"
    ]
  },
  {
    category: 2,
    fruits: [
      "banana",
      "kiwi"
    ]
  },
  {
    category: 3,
    fruits: [
      "pineapple",
      "strawberry"
    ]
  }
]

在聚合管道中，通过$reduce遍历wrangled category数组，有条件地更新累加器，以获得最大匹配类别。

db.collection.aggregate([
  {
    "$unwind": "$shops"
  },
  {
    "$set": {
      "max_category": {
        "$reduce": {
          "input": [
            {
              category: 1,
              fruits: [
                "apple"
              ]
            },
            {
              category: 2,
              fruits: [
                "banana",
                "kiwi"
              ]
            },
            {
              category: 3,
              fruits: [
                "pineapple",
                "strawberry"
              ]
            }
          ],
          "initialValue": null,
          "in": {
            "$cond": {
              "if": {
                $and: [
                  {
                    $gt: [
                      "$$this.category",
                      "$$value"
                    ]
                  },
                  {
                    $gt: [
                      {
                        $size: {
                          "$setIntersection": [
                            "$$this.fruits",
                            "$shops.fruits"
                          ]
                        }
                      },
                      0
                    ]
                  }
                ]
              },
              "then": "$$this.category",
              "else": "$$value"
            }
          }
        }
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "country": {
        $first: "$country"
      },
      "max_category": {
        $max: "$max_category"
      },
      "shops": {
        "$push": "$shops"
      }
    }
  }
])

Mongo Playground

赞(0）回复(0）举报 2023-10-16

ryevplcw2#

另一种选择是计算它，而不重新展开和分组：$reduce、$map和$filter：

db.collection.aggregate([
  {$set: {max_category: {
        $max: {$reduce: {
            input: "$shops",
            initialValue: 0,
            in: {$max: [
                "$$value",
                {$map: {
                    input: "$$this.fruits",
                    as: "f",
                    in: {$toInt: {$getField: {
                          field: "k",
                          input: {$first: {$filter: {
                                input: {$objectToArray: categories},
                                as: "val",
                                cond: {$in: ["$$f", "$$val.v"]}}
                          }}
                    }}}
              }}
          ]}
        }}
  }}}
])

了解它在playground example上的工作原理

赞(0）回复(0）举报 2023-10-16

我来回答

mongodb 根据参考Map计算子文档列表的最大值

2条答案

相关问题

热门标签

最新问答