Group nested fields with aggregation and return results with others fields mongo db

Question

I need to change the structure of some field in my mongoDB document.

Here the sample:

[
    {

        _id: "ObjectId('997v2ha1cv9b0036fa648zx3')",
        title: "Adidas Predator",
        size: "8",
        colors: [
            {
                hex: "005FFF",
                name: "Blue"
            },
            {
                hex: "FF003A",
                name: "Red"
            },
            {
                hex: "FFFE00",
                name: "Yellow"
            },
            {
                hex: "07FF00",
                name: "Green"
            },

        ],
        extras: [
            {
                description: "laces",
                type: "exterior"
            },
            {
                description: "sole",
                type: "interior"
            },
            {
                description: "logo"
            },
            {
                description: "stud",
                type: "exterior"
            }
        ],
        media: {
            images: [
                {
                    url: "http://link.com",
                    type: "exterior"
                },
                {
                    url: "http://link3.com",
                    type: "interior"
                },
                {
                    url: "http://link2.com",
                    type: "interior"
                },
                {
                    url: "http://link4.com",
                    type: "exterior"
                }
            ]
        }
    }
];

My goal is to group some fields: colors need to be and array with just the colors, extras need to be an array with 3 object each one for a "type" (interior, exterior, null) the same for images that is inside media

Here what I expected:

{
    _id: "ObjectId('997b5aa1cv9b0036fa648ab5')",
    title: "Adidas Predator",
    size: "8",
    colors: ["Blue", "Red", "Yellow", "Green"],
    extras: [
        {type: exterior, description: ["laces", "stud"]},
        {type: interior, description: ["sole"]},
        {type: null, description: ["logo"]}
    ],
    images: [
        {type: exterior, url: ["http://link.com", "http://link4.com"]},
        {type: interior, url: ["http://link2.com", "http://link3.com"]},
    ]
};

With my code I can achieve my goal but I don't understand how to show all the information together through the pipeline. Here my code:


db.collection.aggregate([
    {
        $project: {
            title: 1,
            size: 1,
            colors: "$colors.name",
            extras: 1,
            media: "$media.images"
        },
    },
    {
        $unwind: "$media"
    },
    {
        $group: {
            _id: {
                type: "$media.type",
                url: "$media.url",
            },
        },
    },
    {
        $group: {
            _id: "$_id.type",
            url: {
                $push: "$_id.url"
            },
        },
    },
]);

The result is:

[
    {
        _id: "exterior",
        url: [
            "http://link.com",
            "http://link4.com"
        ]
    },
    {
        _id: "interior",
        url: [
            "http://link3.com",
            "http://link2.com"
        ]
    }
];

If I do the same thing with extras I get the same (correct) structure. How can I show all the data together like in the expected structure? Thanks in advice.

Tom Slabbaert · Accepted Answer · 2021-04-18 15:58:52Z

1

The strategy will be to maintain the require parent fields throughout the pipeline using $first to just grab the initial value, It ain't pretty but it works:

db.collection.aggregate([
  {
    "$addFields": {
      colors: {
        $map: {
          input: "$colors",
          as: "color",
          in: "$$color.name"
        }
      }
    }
  },
  {
    $unwind: "$extras"
  },
  {
    "$addFields": {
      imageUrls: {
        $map: {
          input: {
            $filter: {
              input: "$media.images",
              as: "image",
              cond: {
                $eq: [
                  "$$image.type",
                  "$extras.type"
                ]
              }
            }
          },
          as: "image",
          in: "$$image.url"
        }
      }
    }
  },
  {
    $group: {
      _id: {
        _id: "$_id",
        extraType: "$extras.type"
      },
      extraDescriptions: {
        "$addToSet": "$extras.description"
      },
      imageUrls: {
        "$first": "$imageUrls"
      },
      colors: {
        $first: "$colors"
      },
      size: {
        $first: "$size"
      },
      title: {
        $first: "$title"
      }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      colors: {
        $first: "$colors"
      },
      size: {
        $first: "$size"
      },
      title: {
        $first: "$title"
      },
      images: {
        $push: {
          type: {
            "$ifNull": [
              "$_id.extraType",
              null
            ]
          },
          url: "$imageUrls"
        }
      },
      extras: {
        $push: {
          type: {
            "$ifNull": [
              "$_id.extraType",
              null
            ]
          },
          description: "$extraDescriptions"
        }
      }
    }
  }
])

Mongo Playground

answered Apr 18, 2021 at 15:58

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

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

4 Comments

Kevin Over a year ago

First of all, thanks for the reply. I'm wondering if there is a way using some stage aggregation like $project instead

Tom Slabbaert Over a year ago

You mean project instead of group?

Kevin Over a year ago

I mean use more stage like group instead of grouping with map and then use project at the end of the pipeline to show the result

Tom Slabbaert Over a year ago

I guess it's possible in theory using $reduce and $filter but this would be a both a nightmare to write and much much MUCH worse efficiency wise, i'm not sure why you want to go that way?

turivishal · Accepted Answer · 2021-04-18 17:01:46Z

0

You can try $function operator, to defines a custom aggregation function or expression in JavaScript.

$project to show required fields and get array of colors name
$function, write your JS logic if you needed you can sort this logic of group, it will return result with 2 fields (extras, images)
$project to show required fields and separate extras and images field from result

db.collection.aggregate([
  {
    $project: {
      title: 1,
      size: 1,
      colors: "$colors.name",
      result: {
        $function: {
          body: function(extras, images) {
            function groupBy(objectArray, k, v) {
              var results = [], res = objectArray.reduce((acc, obj) => {
                  if (!acc[obj[k]]) acc[obj[k]] = [];
                  acc[obj[k]].push(obj[v]);
                  return acc;
              }, {});
              for (var o in res) {
                results.push({ [k]: o === 'undefined' ? null : o, [v]: res[o] })
              }
              return results;
            }
            return {
              extras: groupBy(extras, 'type', 'description'),
              images: groupBy(images, 'type', 'url')
            }
          },
          args: ["$extras", "$media.images"],
          lang: "js"
        }
      }
    }
  },
  {
    $project: {
      title: 1,
      size: 1,
      colors: 1,
      extras: "$result.extras",
      images: "$result.images"
    }
  }
])

Playground

IMPORTANT:
Executing JavaScript inside an aggregation expression may decrease performance. Only use the $function operator if the provided pipeline operators cannot fulfill your application's needs.

edited Apr 18, 2021 at 17:01

answered Apr 18, 2021 at 16:51

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

2 Comments

Kevin Over a year ago

Thank you. I agree with the "important" section you've wrote. Maybe the same operation can be done using some mongo operators instead of js?

turivishal Over a year ago

that is why i have added last option. i don't think its possible to do with just some mongodb operators. i would suggest you can do this kind of operations in programming language as well.

Collectives™ on Stack Overflow

Group nested fields with aggregation and return results with others fields mongo db

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related