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

Question

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

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

Fraction · Accepted Answer · 2021-08-16T13:26:21.890

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

Thanks for your answer, it works! in fact I just resolved it myself with the inspiration from this answer in the question https://stackoverflow.com/questions/51298993/replaceroot-in-mongodb，add `$project` in the end of my second code block OR just like your answer, generate a new column under the root doc --> `doc.skill` then do the replacing with `$replaceRoot` — Kevin Xiong, Aug 16 '21 at 11:51
I added a second solution which I think is better than the first one — Fraction, Aug 16 '21 at 12:40
I have to say it's a cleaner solution, I expected string instead of Array, though. thanks for your time & kindly reply. — Kevin Xiong, Aug 16 '21 at 12:58

score 0 · Answer 2 · answered Aug 16 '21 at 12:46

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)

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

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

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

2 Answers2