Mongodb:获取所有列的不同记录，并同时针对另一列连接值

Question

有什么方法可以获取所有列的不同记录，同时针对另一列进行连接。例如我有记录:

{ _id : 611a20dfcfbd65f2fbc4aad8, name : "John", skill: "sql", birth :"2000-07-01" }
{ _id : 611a20dfcfbd65f2fbc4aadb, name : "David",  skill: "java", birth :"1993-06-08" }
{ _id : 611a20dfcfbd65f2fbc4aade, name : "Tom",  skill: "C#", birth :"1990-12-15" }
{ _id : 611a20dfcfbd65f2fbc4aae1, name : "John",  skill: "js", birth :"2000-07-01" }
{ _id : 611a20dfcfbd65f2fbc4aae4, name : "Tom", skill: "sql", birth :"1990-12-15"}
{ _id : 611a20dfcfbd65f2fbc4aae7, name : "John", skill: "java", birth :"2000-07-01" }

我期望的是(在 name 上不同，在 skill 上连接)

{ name : "John", skill: "sql,js,java", birth :"2000-07-01" }
{ name : "David",  skill: "java", birth :"1993-06-08" }
{ name : "Tom",  skill: "C#,sql", birth :"1990-12-15" }

我找到了一些有用的答案，例如:

Concat String by Group

Get distinct records values

问题是我无法将它们正确组合在一起。

skill列返回 null以下查询

db.collection.aggregate([
  { "$group": {
    "_id": "$name",
    "doc": { "$first": "$$ROOT" },
    "skillArray": { "$push": "$skill" },
  }},
  { "$replaceRoot": {
    "newRoot": "$doc"
  }},
  { "$addFields": {
    "skill": {
      "$reduce": {
        "input": "$skillArray",
        "initialValue": "",
        "in": {
          "$cond": {
            "if": { "$eq": [ "$$value", "" ] },
            "then": "$$this",
            "else": {
              "$concat": ["$$value", ",", "$$this"]
            }
          }
        }
      }
    }
  }},
  { $sort: { birth: -1 }}
])

差不多可以了，但是这个查询在每一行都返回嵌套记录(只需删除上面示例的 { "$replaceRoot": { "newRoot": "$doc" }}, 部分)

db.collection.aggregate([
  { "$group": {
    "_id": "$name",
    "doc": { "$first": "$$ROOT" },
    "skillArray": { "$push": "$skill" },
  }},
  { "$addFields": {
    "skill": {
      "$reduce": {
        "input": "$skillArray",
        "initialValue": "",
        "in": {
          "$cond": {
            "if": { "$eq": [ "$$value", "" ] },
            "then": "$$this",
            "else": {
              "$concat": ["$$value", ",", "$$this"]
            }
          }
        }
      }
    }
  }},
  { $sort: { birth: -1 }}
])

Answer 1

你快完成了，使用 $reduce与 $concat而不是 $cond :

db.collection.aggregate([
  {
    $group: {
      _id: "$name",
      doc: { $first: "$$ROOT" },
      skillArray: { $push: "$skill" },    
    }
  },
  {
    $addFields: {
      "doc.skill": {
        $reduce: {
          input: "$skillArray",
          initialValue: "",
          in: {
            $concat: [
              "$$value",
              { $cond: [{ $eq: ["$$value", ""] }, "", ", "] },
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    $replaceRoot: { newRoot: "$doc" }
  },
  {
    $sort: { birth: -1 }
  }
])

工作示例:
https://mongoplayground.net/p/V4lrNIWUR29
您也可以使用 $replaceWith 与 $mergeObjects :

db.collection.aggregate([
  {
    $group: {
      _id: "$name",
      doc: { $first: "$$ROOT" },
      skillArray: { $push: "$skill" },
      
    }
  },
  {
    $replaceWith: {
      $mergeObjects: [
        "$doc",
        {
          skill: {
            $reduce: {
              input: "$skillArray",
              initialValue: "",
              in: {
                $concat: [
                  "$$value",
                  { $cond: [{ $eq: ["$$value",""] }, "", ", " ] },
                  "$$this"
                ]
              }
            }
          }
        }
      ]
    }
  },
  {
    $sort: { birth: -1 }
  }
])

工作示例: https://mongoplayground.net/p/K-7JUKy4MPS