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

user_15682523 0
Viewed 71

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.

aggregation-framework node.js mongodb grouping pipeline
2 Answers

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

user_107703700

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.

user_89871280
Related