MongoDB - Aggregation - To get unique items in array

Question

Here's my MongoDB collection:

{
    "_id" : ObjectId("515d8f53175b8ecb053425c2"),
    "category" : "Batteries",
    "products" : [
        {
            "brand" : "Duracell",
            "item" : [
                "AA",
                "AAA"
            ]
        },
        {
            "brand" : "Everyday",
            "item" : [
                "9V",
                "AA",
                "12V"
            ]
        }
    ]
}

The output that I need is

1) Unique list of all items

{["AA", "AAA", "9V", "12V"]}

and 2. unique list of items per product

{
    "category" : "Batteries",
    "item": ["AA", "AAA", "9V", "12V"]
}

I'm very new to MongoDB, and I tried different aggregations functions and nothing seems to work. Please help.

Debor

Answer 1

After few more tries, I had solved this. Here's the commands:

db.xyz.aggregate( {$project: {a: '$products.item'}}, 
    {$unwind: '$a'}, 
    {$unwind: '$a'}, 
    {$group: {_id: 'a', items: {$addToSet: '$a'}}});

and

db.xyz.aggregate( {$project: {category: 1, a: '$products.item'}}, 
    {$unwind: '$a'}, 
    {$unwind: '$a'}, 
    {$group: {_id: '$category', items: {$addToSet: '$a'}}});

Debor

Why are you $unwinding twice ? – Re

because 'item' is nested in an array. – Debor

If you only have 1 item in an array, you can use $first instead of unwind. It returns the first item from an array and is much cheaper than $unwind. Use it in combination with $addFields. E.g {$addFields: {item: {$first: 'products.item'}}} Now you can unwind "item" and use it in the "$group" stage. – Darreldarrell

Answer 2

After mongodb3.4, there is a $reduce operator, so we can flat a array without extra stage.

1.

col.aggregate([
  {
    $project: {
      items: {
        $reduce: {
          input: "$products.items",
          initialValue: [],
          in: { $concatArrays: ["$$value", "$$this"] },
        },
      },
    },
  },
  { $unwind: "$items" },
  { $group: { _id: null, items: { $addToSet: "$items" } } },
]);

2.

col.aggregate([
  {
    $project: {
      category: 1,
      items: {
        $setUnion: {
          $reduce: {
            input: "$products.items",
            initialValue: [],
            in: { $concatArrays: ["$$value", "$$this"] },
          },
        },
      },
    },
  },
]);

Unaware

Answer 3

I know this is an old question but I would like to show an easier way of doing it! A setDifference function takes two sets and returns an array containing the elements that only exist in the first set. It ignores duplicate entries while doing it.

Therefore, I trick this by using an empty array at second variables.

Full Code

db.xyz.aggregate([
{
    $match: { 
        _id: ObjectId("515d8f53175b8ecb053425c2"),
        category: "Batteries"
    }
},
{
    $set: { 
        item: { $setDifference: ["$products.item", []] }
    }
}
])

Adler

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Walkabout

Answer 4

I know it is an old question and you've solved it several years ago! But there is a small problem in the answer you've marked as correct and it may not suitable for all cases. The $unwind is an expensive operator and may affect latency and memory consumption for large datasets. I think the $reduce operator is more performant in this case.

Estell

Perhaps you could add a demonstration of how to use reduce to get a unique set of items in this case? – Euchromosome

Answer 5

I am not sure what you all you have tried in the aggregation function but i thought unwind will help you to do the same , assuming you are not able to get it done , we have a map-reduce which will allow you to easily do this one . You can look into the http://docs.mongodb.org/manual/applications/map-reduce/ . It allow you to get the data in a manner you want and you can easily get the list . I think $unwind on the tags column and then $group them will always give the us the list of distinct tags as required by you in 1 and for 2nd case create $group on two key category and item which was $unwind earlier.

Re

Devesh, thanks for your response. I was able to solve this with just aggregation. Posted my answer as well. – Debor