How to copy all fields from first document in an aggregate (re)grouping

Question

In a collection called test I have some documents that are similar to:

{
    "_id" : "PRQA2311",
    "name": "Binder",
    "category": "Office Supplies",
    "price": 3.99,
    "...": "...",
    "variants" : [
        {
            "barcode" : "2314399903212",
            "attributes" : {
                "color" : "red",
                "size" : "Letter"
            }
        },
        {
            "barcode" : "2314399903213",
            "attributes" : {
                "color" : "red",
                "size" : "A4"
            }
        },
        {
            "barcode" : "2314399903214",
            "attributes" : {
                "color" : "blue",
                "size" : "Letter"
            }
        },
        {
            "barcode" : "2314399903215",
            "attributes" : {
                "color" : "blue",
                "size" : "A4"
            }
        }
    ]
}

and I use the following aggregation pipeline to split and regroup them so that each product has only one color:

db.test.aggregate([
    {
        $unwind: "$variants"
    },
    {
        $group: {
            _id: {
                $concat: [
                    { "$toString": "$_id" },
                    ".",
                    { "$toString": "$variants.attributes.color" }
                ]
            },
            name: { $first: "$name" },
            variants: { $push: "$variants" }
        }
    }
]).pretty();

This works fine, but I would also like to copy the other fields. While I can do this by explicitly adding the fields to the group aggregation like I have done with the name field. It would make development and maintenance easier if I could just copy all of the fields.

Looking at the accepted answer of this question it seems like it might not have been possible in the past. However the second answer hints that it might be possible with newer versions of MongoDB, even though the provided solution is not immediately applicable to my problem.

I think a solution might be possible with $mergeObjects and $replaceRoot, but I haven't been able to work it out yet.

How about `data:{$push:$$ROOT}` instead of all the fields i.e name, variants. You will get all the fields from your previous pipeline? — Gibbs, Jul 07 '20 at 14:44
@Gibbs I've actually been playing around with something similar `"object": {"$first": "$$ROOT"}` however I still need to figure out how to then transform that so the fields end up in the correct position. I'm thinking either a $addFields or $mergeObjects followed by replaceRoot. But I haven't had much luck yet. — kaan_a, Jul 07 '20 at 14:50

score 1 · Accepted Answer · answered Jul 07 '20 at 14:57

You can put the whole "root" document ($$ROOT) in a field, then use $mergeObjects to merge that with the variants field

db.test.aggregate([
  {
    $unwind: "$variants"
  },
  {
    $group: {
      _id: {
        $concat: [
          { "$toString": "$_id" },
          ".",
          { "$toString": "$variants.attributes.color" }
        ]
      },
      variants: {
        $push: "$variants"
      },
      _root: {
        $first: "$$ROOT" // put all fields inside _root
      }
    }
  },
  {
    $replaceRoot: { // replace the root document with the combination of _root and variants, and _id if you want to keep the concatenated _id
      newRoot: {
        $mergeObjects: [
          "$_root",
          { _id: "$_id", variants: "$variants" }
        ]
      }
    }
  }
])

Mongo Playground

TIP: For MongoDB v4.2 or later you can use { $replaceWith: <doc> } instead of { $replaceRoot: { newRoot: <doc> } }

kaan_a · Answer 2 · 2020-07-08T13:37:37.633

Update

I have verified that this also works, and is currently my preferred method, pending a benchmark:

db.test.aggregate([
    {
        $unwind: "$variants"
    },
    {
        $group: {
            _id: {
                $concat: [
                    { "$toString": "$_id" },
                    ".",
                    { "$toString": "$variants.attributes.color" }
                ]
            },
            object: { $first: "$$ROOT" },
            variants: { $push: "$variants" }
        }
    },
    {
        $set: {
            "object._id": "$_id",
            "object.variants": "$variants",
        }
    },
    {
        $replaceRoot: { newRoot: "$object" }
    }
]).pretty();

I figured it out, though I like thammada.ts' answer better

db.test.aggregate([
    {
        $unwind: "$variants"
    },
    {
        $group: {
            _id: {
                $concat: [
                    { "$toString": "$_id" },
                    ".",
                    { "$toString": "$variants.attributes.color" }
                ]
            },
            object: { $first: "$$ROOT" },
            variants: { $push: "$variants" }
        }
    },
    {
        $unset: ["object._id", "object.variants"]
    },
    {
        $set: {
            "object._id": "$_id",
            "object.variants": "$variants",
        }
    },
    {
        $replaceRoot: { newRoot: "$object" }
    }
]).pretty();

You don't actually need `$unset` if you are going to overwrite those fields — thammada.ts, Jul 07 '20 at 15:11
@thammada.ts Wow, that's neat. I actually think I might end up using my answer without the unset stage then since it's easier on the eyes. Though I am curious about performance. I'll have to run some benchmarks — kaan_a, Jul 07 '20 at 17:22

How to copy all fields from first document in an aggregate (re)grouping

2 Answers2