代码之家 › 专栏 › 技术社区 › mateusz_mp

MongoDB按复合字段分组和计数

pymongo grouping mongodb

mateusz_mp · 技术社区 · 1 年前

以下是我的记录:

我是通过使用 collection.find().limit(1)

[
  {
    "_id": {"$oid": "..."},
    "husband.firstName": "John",
    "husband.secondName": "Smith",
    "wife.firstName": "Alice",
    "wife.secondName": "Watson",
    "...": "...",
  }
]

“丈夫和妻子”字段仅包含“名字”和“姓氏”

我想统计一下每个丈夫和妻子的名字组合有多常见。

我想象结果的形式如下:

[
  {
    "husband.firstName": "John",
    "husband.secondName": "Smith",
    "wife.firstName": "Alice",
    "wife.secondName": "Watson",
    "count": "456",
  },
  {
    "husband.firstName": "Jack",
    "husband.secondName": "Smith",
    "wife.firstName": "Alice",
    "wife.secondName": "Watson",
    "count": "123",
  }
]

我使用Python和pymongo,所以我尝试了以下方法:

pipeline = [
    {
        "$group": {
            "_id": {
                "husband": "$husband",
                "wife": "$wife"
            },
            "count": {"$sum": 1}
        }
    },
    {
        "$sort": {"count": -1}
    },
]

然而,这将返回一个空结果:

[
  {
    "_id": "{}",
    "count": 47553
  }
]

我也试着按字段单独分组,但结果是一样的。

1 回复 | 直到 1 年前

ray 1 年前

正如@cmgchess在评论中指出的那样,您的案例的主要困难在于字段名中的点。您可能希望将模式重构为以下内容:

{
    "husband": {
      "firstName": "John",
      "secondName": "Smith"
    },
    "wife": {
      "firstName": "Alice",
      "secondName": "Watson"
    }
  }

然而,对于您当前的模式,您可以通过使用 $getField .

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "husband": {
          "firstName": {
            "$getField": {
              "field": "husband.firstName",
              "input": "$$ROOT"
            }
          },
          "secondName": {
            "$getField": {
              "field": "husband.secondName",
              "input": "$$ROOT"
            }
          }
        },
        "wife": {
          "firstName": {
            "$getField": {
              "field": "wife.firstName",
              "input": "$$ROOT"
            }
          },
          "secondName": {
            "$getField": {
              "field": "wife.secondName",
              "input": "$$ROOT"
            }
          }
        }
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$sort": {
      "count": -1
    }
  }
])

Mongo Playground