mongodb - 转换 Mongo 查询返回的数据

Question

我收藏的文件如下：

[
  {"cat": "A", "catDesc": "abc", "subcat": "1", "subcatDesc": "123", "name" : "Tom", "age" : 22},
  {"cat": "A", "catDesc": "abc", "subcat": "1", "subcatDesc": "123", "name" : "Paul", "age" : 19},
  {"cat": "A", "catDesc": "abc", "subcat": "2", "subcatDesc": "456", "name" : "Jack", "age" : 25},
  {"cat": "A", "catDesc": "abc", "subcat": "2", "subcatDesc": "456", "name" : "Liu", "age" : 21},
  {"cat": "A", "catDesc": "abc", "subcat": "3", "subcatDesc": "789", "name" : "Ace", "age" : 18},
  {"cat": "A", "catDesc": "abc", "subcat": "3", "subcatDesc": "789", "name" : "Pan", "age" : 20},
  {"cat": "B", "catDesc": "def", "subcat": "1", "subcatDesc": "123", "name" : "Shawn", "age" : 23},
  {"cat": "B", "catDesc": "def", "subcat": "1", "subcatDesc": "123", "name" : "Dom", "age" : 25},
  {"cat": "B", "catDesc": "def", "subcat": "2", "subcatDesc": "456", "name" : "Paul", "age" : 16},
  {"cat": "B", "catDesc": "def", "subcat": "2", "subcatDesc": "456", "name" : "Ace", "age" : 21}
]

现在我想编写一个 mongo 查询，它会在返回时转换数据。应该是这样的：

[ {"cat" : "A",
   "catDesc" : "abc",
    subcats : 
    [
      {"subcat" : "1",
       "subcatDesc" : "123",
       "Details" : [{"name" : "Paul", "age" : 19}, {"name" : "Tom", "age" : 22}]
      },
      {"subcat" : "2",
       "subcatDesc" : "456",
       "Details" : [{"name" : "Liu", "age" : 21}, {"name" : "Jack", "age" : 25}]
      },
      {"subcat" : "3",
       "subcatDesc" : "789",
       "Details" : [{"name" : "Ace", "age" : 18}, { "name" : "Pan", "age" : 20}]
      }
    ]
  },

  {"cat" : "B",
   "catDesc" : "def",
    subcats : 
    [
      {"subcat" : "1",
       "subcatDesc" : "123",
       "Details" : [{"name" : "Shawn", "age" : 23}, {"name" : "Dom", "age" : 25}]
      },
      {"subcat" : "2",
       "subcatDesc" : "456",
       "Details" : [{"name" : "Paul", "age" : 16}, {"name" : "Ace", "age" : 21}]
      }
    ]
  }
]

数据应如上转换，也"cat"应按字母顺序"subcats"排序，应按子目录编号排序，"details"应按年龄排序

标签： mongodbmongoosemongodb-queryaggregation-framework

Answer 1

这很容易实现，只需$group两次，如下所示：

db.collection.aggregate([
    {
        $sort: {
            age: 1
        }
    },
    {
        $group: {
            _id: {cat: "$cat", subcat: "$subcat"},
            details: {$addToSet: {name: "$name", age: "$age"}},
            subcatDesc: {$first: "$subcatDesc"},
            catDesc: {$first: "$catDesc"}
        }
    },
    {
        $group: {
            _id: "$_id.cat",
            catDesc: {$first: "$catDesc"},
            subcats: {
                $push: {
                    subcat: "$_id.subcat",
                    subcatDesc: "$subcatDesc",
                    Details: "$details"
                }
            }
        }
    },
    {
        $project: {
            subcats: 1,
            catDec: 1,
            cat: "$_id"
        }
    },
    {
        $sort: {
            cat: 1,
            catDec: 1
        }
    },
])