Unwind in mongodb aggregate with multiple nested, but possibly empty, arrays

Question

I have a collection filled with documents looking somehting like this:

{
  _id: <id>
  arrayOne: [{name: <string>, listings: [<string>, <string>, ...]}],
  arrayTwo: [{name: <string>, listings: [<string>, <string>, ...]}]
}

What i need is a flat array where each string in listings from both arrayOne and arrayTwo is represented without duplicates, so I do this:

aggregate( [
  { $match: { _id: <id>} },
  { $unwind: '$arrayOne' },
  { $unwind: '$arrayOne.listings' },
  { $unwind: '$arrayTwo' },
  { $unwind: '$arrayTwo.listings' },
  { $group : { _id : '$_id', setOne: { $addToSet: '$arrayOne.listings'}, setTwo: {$addToSet: '$arrayTwo.listings'} } },
  { $project: {unique_appearances: {$setUnion: ['$setOne', '$setTwo']}}}
] );

This works just fine until we run this on a document where arrayOne or arrayTwo is empty.

Today, I solve it by adding a fake value (before unwinding), which i filter out at the last line, like this:

aggregate( [
  { $match: { _id: <id>} },
  { $project: {
      _id: '$_id',
      arrayOne: { $cond : [{$gt: ['$arrayOne', []]}, '$arrayOne', [{listings: ['cheezeburglars']}]] },
      arrayTwo: { $cond: [{$gt: ['$arrayTwo', []]}, '$arrayTwo', [{listings: ['cheezeburglars']}]] }
    }
  },
  { $unwind: '$arrayOne' },
  { $unwind: '$arrayOne.listings' },
  { $unwind: '$arrayTwo' },
  { $unwind: '$arrayTwo.listings' },
  { $group : { _id : '$_id', setOne: { $addToSet: '$arrayOne.listings'}, setTwo: {$addToSet: '$arrayTwo.listings'} } },
  { $project: {
    unique_appearances:  {
      $setDifference: [{$setUnion: ['$setOne: ', '$setTwo']}, ['cheezeburglars']]
    }
  }}
] );

This works, but I feel that my solution is a bit hacky. Is there a better way to solve this?

JohnnyHK · Accepted Answer · 2016-06-20 12:33:44Z

4

You can do this in MongoDB 3.2+ by including the preserveNullAndEmptyArrays: true option in the relevant $unwind stages:

aggregate( [
  { $match: { _id: <id>} },
  { $unwind: { path: '$arrayOne', preserveNullAndEmptyArrays: true } },
  { $unwind: '$arrayOne.listings' },
  { $unwind: { path: '$arrayTwo', preserveNullAndEmptyArrays: true } },
  { $unwind: '$arrayTwo.listings' },
  { $group : { _id : '$_id', setOne: { $addToSet: '$arrayOne.listings'}, setTwo: {$addToSet: '$arrayTwo.listings'} } },
  { $project: {unique_appearances: {$setUnion: ['$setOne', '$setTwo']}}}
] );

Setting that option will cause the $unwind to still include the document in the output, even when the field is empty (or null or missing).

answered Jun 20, 2016 at 12:33

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

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

Collectives™ on Stack Overflow

Unwind in mongodb aggregate with multiple nested, but possibly empty, arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related