I would like to merge several documents. Most of the fields have the same values but there might be one or two fields that have different values. These fields are unknown beforehand. Ideally I would like to merge all the documents keeping the fields that are the same as is but creating an array of values only for those fields that have some variation.

For my first approach I grouped by a common field to my documents and kept the first document, this however discards some information that varies in other fields.

     group_documents = {
         "$group": {
           "_id": "$0020000E.Value",
           "doc": {
             "$first": "$$ROOT"
           }
         }
     }
   

     merge_documents = {
         "$replaceRoot": {
           "newRoot": "$doc"
         }
     }

     write_collection = { "$out": { "db": "database", "coll": "records_nd" } }

    objects = coll.aggregate(pipeline)

IF the fields that have different values where known I would have done something like this,

merge_sol1 or merge_sol2 or merge_sol3

The third solution is actually very close to my desired output and I could tweak it a bit. But these answers assume a-priori knowledge of the fields to be merged.

CodePudding user response：

You can first convert $$ROOT to array of k-v tuples by $objectToArray. Then, $group all fields by $addToSet to put all distinct values into an array first. Then, check the size of the result array and conditionally pick the first item if the array size is 1 (i.e. the value is the same for every documents in the field); Otherwise, keep the result array. Finally, revert back to original document form by $arrayToObject.

db.collection.aggregate([
  {
    $project: {
      _id: "$key",
      arr: {
        "$objectToArray": "$$ROOT"
      }
    }
  },
  {
    "$unwind": "$arr"
  },
  {
    $match: {
      "arr.k": {
        $nin: [
          "key",
          "_id"
        ]
      }
    }
  },
  {
    $group: {
      _id: {
        id: "$_id",
        k: "$arr.k"
      },
      v: {
        "$addToSet": "$arr.v"
      }
    }
  },
  {
    $project: {
      _id: "$_id.id",
      arr: [
        {
          k: "$_id.k",
          v: {
            "$cond": {
              "if": {
                $gt: [
                  {
                    $size: "$v"
                  },
                  1
                ]
              },
              "then": "$v",
              "else": {
                $first: "$v"
              }
            }
          }
        }
      ]
    }
  },
  {
    "$project": {
      doc: {
        "$arrayToObject": "$arr"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          {
            _id: "$_id"
          },
          "$doc"
        ]
      }
    }
  }
])

Mongo Playground