MongoDB，将内部哈希键值按另一个键值分组

Question

我的收藏中有这 4 个元素:

/* 1 */
{
  "demographics": [
    {
      "key": "country",
      "value": "ES"
    },
    {
      "key": "city",
      "value": "Sevilla"
    },
    {
      "key": "region",
      "value": "Andalucía"
    }
  ]
}

/* 2 */
{
  "demographics": [
    {
      "key": "city",
      "value": "Cádiz"
    },
    {
      "key": "country",
      "value": "ES"
    },
    {
      "key": "region",
      "value": "Andalucía"
    }
  ]
}

/* 3 */
{
  "demographics": [
    {
      "key": "country",
      "value": "GB"
    },
    {
      "key": "region",
      "value": "Greater London"
    },
    {
      "key": "city",
      "value": "London"
    }
  ]
}

/* 4 */
{
  "demographics": [
    {
      "key": "country",
      "value": "ES"
    },
    {
      "key": "region",
      "value": "Andalucía"
    },
    {
      "key": "city",
      "value": "Sevilla"
    }
  ]
}

我想按以下方式对它们进行分组:

• demographic.value 当 demographic.key = "country"
• demographic.value 当 demographic.key = "region"
• demographic.value 当 demographic.key = "city"

结果如下:

{ "values": ["ES", "Andalucía", "Sevilla"], "count": 2 }
{ "values": ["ES", "Andalucía", "Cádiz"], "count": 1 }
{ "values": ["GB", "Greater London", "London"], "count": 1 }

注意:注意 demographics 数组元素的顺序可能并不总是相同。

我试过了

db.getCollection('test').aggregate(
  [
    { "$unwind": "$demographics" },
    {
      "$project" :{
        "_id": 0,
        "demographics.key": 1,
        "demographics.value": 1
      }
    },
    {
      "$group" : {
        "_id": {
          "key": "$demographics.key",
          "value": "$demographics.value"
        },
        "count": { "$sum": 1 }
      }
    },
    {
      "$group" : {
        "_id": "$_id.key",
        "values": { "$push": { "value": "$_id.value", "count": "$count" } }
      }
    }
  ]
)

这给了我这个结果:

/* 1 */
{
  "_id": "country",
  "values": [
    {
      "value": "GB",
      "count": 1.0
    },
    {
      "value": "ES",
      "count": 3.0
    }
  ]
}

/* 2 */
{
  "_id": "region",
  "values": [
    {
      "value": "Greater London",
      "count": 1.0
    },
    {
      "value": "Andalucía",
      "count": 3.0
    }
  ]
}

/* 3 */
{
  "_id": "city",
  "values": [
    {
      "value": "London",
      "count": 1.0
    },
    {
      "value": "Cádiz",
      "count": 1.0
    },
    {
      "value": "Sevilla",
      "count": 2.0
    }
  ]
}

但这不是我要找的群体

Answer 1

您可以尝试运行以下管道:

db.test.aggregate([
    { "$unwind": "$demographics" },
    { "$sort": { "demographics.key": 1, "demographics.value": 1 } },
    {
        "$group": {
            "_id": "$_id",
            "values": { "$push": "$demographics.value" }
        }
    },
    {  
        "$group": {
            "_id": "$values",
            "count": { "$sum": 1 }
        }
    },
    {
        "$project": {
            "_id": 0, "values": "$_id", "count": 1
        }
    }
])

示例输出

/* 1 */
{
    "count" : 2,
    "values" : [ 
        "Sevilla", 
        "ES", 
        "Andalucía"
    ]
}

/* 2 */
{
    "count" : 1,
    "values" : [ 
        "London", 
        "GB", 
        "Greater London"
    ]
}

/* 3 */
{
    "count" : 1,
    "values" : [ 
        "Cádiz", 
        "ES", 
        "Andalucía"
    ]
}