MongoDB/Mongoose advanced search with $unwind, $sort & $group..?

Question

I have documents which are product groups, like this:

[
  {
    options: [],
    products: [
      {
        sku: '123',
        name: 'Product name 1'
      }, {
        sku: '476',
        name: 'Product name 2'
      }
    ]
  }, {
    options: [],
    products: [
      {
        sku: '265',
        name: 'Product name 3'
      }, {
        sku: '789',
        name: 'Product name 4'
      }
    ]
  }
]

What I need to do is an advanced query which includes sorting not only the product groups, but also the product arrays within.

The method I'm trying at the moment is to use aggregate() with $unwind and $sort, then to try to put the group back together (after the $unwind has broken the products array out).

Products.aggregate([
  { $unwind: '$products' },
  { $sort: {
    'products.sku': -1
  } },
  { $group: {
    '_id': '$_id',
    something: { $push: '$$ROOT' }
  } }
]).exec()

It's obviously the $group that's not right. I'm using '_id': '$_id' to put the original MongoDB _id back, but I don't know how to say:

"group by the product group id, and include all original fields"

So really I'm trying to $unwind and $sort the products array, then I want to $wind_it_back_up_again... :-)

How can I achieve this..?

Update: This question is about keeping all fields while grouping.

Possible duplicate of [Mongodb sort inner array](http://stackoverflow.com/questions/15388127/mongodb-sort-inner-array) — s7vr, Feb 15 '17 at 12:39
@Veeram I think it's question not only about sorting inner array, but also about keeping all fields while grouping, so it's different — Rashad Ibrahimov, Feb 15 '17 at 12:58
Okay. To keep the fields you can try something like this. `{$group: {_id: '$_id', options:{'$first:'$options'},'products': {$push: '$products'}}}` and keep rest as same. — s7vr, Feb 15 '17 at 13:02

score 2 · Accepted Answer · answered Feb 15 '17 at 13:02

2

Maybe I'm wrong but as I know there is no option to keep all fields while grouping, you have to manually define all properties. This is the code you're looking for, it sorts product groups by "products.sku" key, sorts inner "products" array by "sku" key, and keeps all fields during $group and $project stages.

db.test.aggregate([
    {$unwind: "$products"},
    {$sort: {"products.sku": -1}},
    {$group: {
        _id: {_id: "$_id", options: "$options"}, products: {$push: "$products"}
    }},
    {$project: {
        _id: "$_id._id",
        options: "$_id.options",
        products: "$products"
    }},
    {$sort: {"products.sku": -1}}
]).pretty();

answered Feb 15 '17 at 13:02

Rashad Ibrahimov

3,279
2
18
39

Thanks, this does indeed work. It does mean that if I change the `Products` schema to include more fields at the root level (along side `options` and `products`) I'll have to remember to also update this `aggregate()`, which is a pain. – Stephen Last Feb 15 '17 at 13:36
Yes, you have to define all fields explicitly at grouping stage. I'm also interested in this question, so I will continue research – Rashad Ibrahimov Feb 15 '17 at 13:56
You say your goal is to sort 'not only the product groups, but also the product arrays within.b' Do you sort the product group based on the product that has the highest sku? Because I am trying the same thing, I have the exact same problem, but on the last line of the aggregate, using '{$sort: {"products.sku": -1}}' and using '{$sort: {"products[0].sku": -1}}' gives me different results and I can't understand why. – Rony Azrak Jul 19 '18 at 22:05

MongoDB/Mongoose advanced search with $unwind, $sort & $group..?

1 Answers1