Mongodb: get distinct records with all columns, and concat value against another column at the same time

user_7374008 0
Viewed 274

Is there any way to get distinct records with all columns, and at the same time, concat against another column. For example I have Records:

{ _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" }

what I expected is(distinct on name, concat on 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" }

I found some useful answers like:

The problem is I can't properly combine them together.

  • The skill column returns null with below query
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 }}
])

the skill column is null

  • Almost make it, but this query returns nested record on each line(just remove { "$replaceRoot": { "newRoot": "$doc" }}, section of above example)
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 }}
])

nested record on each line

mongodb
2 Answers

You're almost done, use $reduce with $concat instead of $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 }
  }
])

Working example: https://mongoplayground.net/p/V4lrNIWUR29

You can also use $replaceWith with $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 }
  }
])

Working example: https://mongoplayground.net/p/K-7JUKy4MPS

user_111297510

The thing is: I don't really understand mongo, The simple truth is:

  • If you create a new field, you must arrange it with other fields in a appropriate level, rather than ignore it.
  • If you already have a superset of the document you are looking for, you only need to do corresponding mapping, it's enough.

Corrections on the two examples in my question(annotation to the above two conclusions)

  1. Add new field under the ROOT doc with $addFields, then doing the replacement with $replaceRoot(changing made on my first code block: use doc.skill instead of skill, switch the order of $addFields and $replaceRoot)
db.collection.aggregate([
  { "$group": {
    "_id": "$name",
    "doc": { "$first": "$$ROOT" },
    "skillArray": { "$push": "$skill" },
  }},
  { "$addFields": {
    "doc.skill": {
      "$reduce": {
        "input": "$skillArray",
        "initialValue": "",
        "in": {
          "$cond": {
            "if": { "$eq": [ "$$value", "" ] },
            "then": "$$this",
            "else": {
              "$concat": ["$$value", ",", "$$this"]
            }
          }
        }
      }
    }
  }},
  { "$replaceRoot": {
    "newRoot": "$doc"
  }},
  { $sort: { birth: -1 }}
])
  1. Create the super document of the required document, then get the sub document with $project(changing made on my second code block: just add $project section after $addFields)
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"]
            }
          }
        }
      }
    }
  }},
  {$project: {
    _id: "$doc._id",
    name: "$_id", //or "$doc.name",
    skill:"$skill",
    birth:"$doc.birth",
  }},
  { $sort: { birth: -1 }}
])
user_73740080
Related