Merging an array while grouping from MongoDB collection

Question

I have a collection that looks something like:

[
    {
        "_id": "5f0307520ac9361c0d7088e2",
        "productId": 1,
        "stock": 10,
        "unit": "item",
        "price": 20,
        "images": [
            "http://productimages.com/1/001.jpg",
            "http://productimages.com/1/002.jpg"
        ]
    },
    {
        "_id": "5f0307520ac9361c0d7088e3",
        "productId": 1,
        "stock": 20,
        "unit": "item",
        "price": 30,
        "images": [
            "http://productimages.com/1/003.jpg",
            "http://productimages.com/1/004.jpg"
        ]
    },
    {
        "_id": "5f0307520ac9361c0d7088e4",
        "productId": 2,
        "stock": 5,
        "unit": "item",
        "price": 15,
        "images": [
            "http://productimages.com/2/001.jpg",
            "http://productimages.com/2/002.jpg"
        ]
    },
    {
        "_id": "5f0307520ac9361c0d7088e5",
        "productId": 2,
        "stock": 5,
        "unit": "item",
        "price": 12,
        "images": [
            "http://productimages.com/2/003.jpg",
            "http://productimages.com/2/004.jpg"
        ]
    }
]

And I aggregate it as follows:

db.variants.aggregate([
    {
        "$group": {
            "_id": "$productId",
            "price": { "$min": "$price" },
            "stock": { "$sum": "$stock" },
            "unit": { "$first": "$unit" },
            "images": { "$push": "$images" },
            "variants": { "$push": "$$ROOT" }
        }
    }
]).pretty()

which produces the following output:

[
    {
        "_id": 2,
        "price": 12,
        "stock": 10,
        "unit": "item",
        "images": [
            [
                "http://productimages.com/2/001.jpg",
                "http://productimages.com/2/002.jpg"
            ],
            [
                "http://productimages.com/2/003.jpg",
                "http://productimages.com/2/004.jpg"
            ]
        ],
        "variants": [
            {
                "_id": "5f0307520ac9361c0d7088e4",
                "productId": 2,
                "stock": 5,
                "unit": "item",
                "price": 15,
                "images": [
                    "http://productimages.com/2/001.jpg",
                    "http://productimages.com/2/002.jpg"
                ]
            },
            {
                "_id": "5f0307520ac9361c0d7088e5",
                "productId": 2,
                "stock": 5,
                "unit": "item",
                "price": 12,
                "images": [
                    "http://productimages.com/2/003.jpg",
                    "http://productimages.com/2/004.jpg"
                ]
            }
        ]
    },
    {
        "_id": 1,
        "price": 20,
        "stock": 30,
        "unit": "item",
        "images": [
            [
                "http://productimages.com/1/001.jpg",
                "http://productimages.com/1/002.jpg"
            ],
            [
                "http://productimages.com/1/003.jpg",
                "http://productimages.com/1/004.jpg"
            ]
        ],
        "variants": [
            {
                "_id": "5f0307520ac9361c0d7088e2",
                "productId": 1,
                "stock": 10,
                "unit": "item",
                "price": 20,
                "images": [
                    "http://productimages.com/1/001.jpg",
                    "http://productimages.com/1/002.jpg"
                ]
            },
            {
                "_id": "5f0307520ac9361c0d7088e3",
                "productId": 1,
                "stock": 20,
                "unit": "item",
                "price": 30,
                "images": [
                    "http://productimages.com/1/003.jpg",
                    "http://productimages.com/1/004.jpg"
                ]
            }
        ]
    }
]

however I would like to get

[
    {
        "_id": 2,
        "price": 12,
        "stock": 10,
        "unit": "item",
        "images": [
            "http://productimages.com/2/001.jpg",
            "http://productimages.com/2/002.jpg",
            "http://productimages.com/2/003.jpg",
            "http://productimages.com/2/004.jpg"
        ],
        "variants": [
            {
                "_id": "5f0307520ac9361c0d7088e4",
                "productId": 2,
                "stock": 5,
                "unit": "item",
                "price": 15,
                "images": [
                    "http://productimages.com/2/001.jpg",
                    "http://productimages.com/2/002.jpg"
                ]
            },
            {
                "_id": "5f0307520ac9361c0d7088e5",
                "productId": 2,
                "stock": 5,
                "unit": "item",
                "price": 12,
                "images": [
                    "http://productimages.com/2/003.jpg",
                    "http://productimages.com/2/004.jpg"
                ]
            }
        ]
    },
    {
        "_id": 1,
        "price": 20,
        "stock": 30,
        "unit": "item",
        "images": [
            "http://productimages.com/1/001.jpg",
            "http://productimages.com/1/002.jpg",
            "http://productimages.com/1/003.jpg",
            "http://productimages.com/1/004.jpg"
        ],
        "variants": [
            {
                "_id": "5f0307520ac9361c0d7088e2",
                "productId": 1,
                "stock": 10,
                "unit": "item",
                "price": 20,
                "images": [
                    "http://productimages.com/1/001.jpg",
                    "http://productimages.com/1/002.jpg"
                ]
            },
            {
                "_id": "5f0307520ac9361c0d7088e3",
                "productId": 1,
                "stock": 20,
                "unit": "item",
                "price": 30,
                "images": [
                    "http://productimages.com/1/003.jpg",
                    "http://productimages.com/1/004.jpg"
                ]
            }
        ]
    }
]

instead. I have tried replacing the images expression with images.* but this produced an empty set.

I have also tried adding a $reduce projection to the pipeline as shown at combine array fields into a single array field mongo:

db.variants.aggregate([
    {
        "$group": {
            "_id": "$productId",
            "price": { "$min": "$price" },
            "stock": { "$sum": "$stock" },
            "unit": { "$first": "$unit" },
            "images": { "$push": "$images" },
            "variants": { "$push": "$$ROOT" }
        }
    },
    {
        "$project": {
            "images": {
                "$reduce": {
                    "input": { "$concatArrays": ["images.*"] },
                    "initialValue": [],
                    "in": { "$setUnion": ["$$this", "$$value"] }
                }
            }
        }
    }
]).pretty()

which fails with:

{
    "ok" : 0,
    "errmsg" : "Failed to optimize pipeline :: caused by :: $concatArrays only supports arrays, not string",
    "code" : 28664,
    "codeName" : "Location28664"
}

Does this answer your question: stackoverflow.com/questions/50638861/… ? — mickl
– mickl, Commented Jul 6, 2020 at 11:36
@mickl Sort of. It seems to be it's own aggregation rule, I'm not sure how I would use it inside a group aggregation. Would it have to be it's own step in the pipeline? I think an answer to this specific question might be more helpful to me and to others than just a link to that question. — kaan_atakan
– kaan_atakan, Commented Jul 6, 2020 at 11:40
Yes, you just need to add additional step after $group to flatten your array of arrays, just like in that example — mickl
– mickl, Commented Jul 6, 2020 at 11:41
@mickl I've tried that, but I get an error. This seems to be working with named arrays, whereas I have an array of arrays comming in from my group — kaan_atakan
– kaan_atakan, Commented Jul 6, 2020 at 11:53

thammada.ts · Accepted Answer · 2020-07-06 12:56:58Z

1

You can use $concatArrays together with $reduce.

Instead of $project you can use $addFields, available from MongoDB v3.4 or $set available from v4.2 to keep other fields

db.variants.aggregate([
  {
    "$group": {
      "_id": "$productId",
      "price": {
        "$min": "$price"
      },
      "stock": {
        "$sum": "$stock"
      },
      "unit": {
        "$first": "$unit"
      },
      "images": {
        "$push": "$images"
      },
      "variants": {
        "$push": "$$ROOT"
      }
    }
  },
  {
    "$addFields": { // or $set
      "images": {
        "$reduce": {
          "input": "$images",
          "initialValue": [],
          "in": {
            "$concatArrays": ["$$value", "$$this"]
          }
        }
      }
    }
  }
])

answered Jul 6, 2020 at 12:56

thammada.ts

5,2653 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merging an array while grouping from MongoDB collection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related