mongodb - 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" }

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

• 按组连接字符串
• 获取不同的记录值

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

• 该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 }}
])

标签： mongodb

Answer 1

你快完成了，使用$reducewith$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/V4lrNI​​WUR29

您还可以$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