Cannot get correct result when using MongoDB aggregation in meteor

Question

I am using MongoDB aggregation in meteor.

The items in database look like this:

// item1

{
  products: {
    aaa: 100,
    bbb: 200
  }
}

// item2

{
  products: {
    aaa: 300,
    bbb: 400
  }
}

My pipeline looks like this

 let pipeline = [{
    $limit: 10
  }, {
    $group: {
      _id: {
        // …
      },
      total: {
        $sum: "$products.aaa"
      }
    }
  }];

And it is working perfect. But when I change my database structure to this

// item1

{
  products: [
    {code: "aaa", num: 100},
    {code: "bbb", num: 200}
  ]
}

// item2

{
  products: [
    {code: "aaa", num: 300},
    {code: "bbb", num: 400}
  ]
}

The results I got for total is always 0, I think my pipeline is wrong. Please see the comment inside:

 let pipeline = [{
    $limit: 10
  }, {
    $group: {
      _id: {
        // …
      },
      total: {
        $sum: "$products.0.num"  // Neither this nor "$products[0].num" works
      }
    }
  }];

So how can I write it correctly? Thanks

Blakes Seven · Accepted Answer · 2016-03-15 06:04:48Z

1

With MongoDB 3.2 ( which won't be the bundled server with meteor, but there is noting stopping you using a seperate server instance. And actually would be recommended ) you can use $arrayElemAt with $map:

let pipeline = [
    { "$limit": 10 }, 
    { "$group": {
      "_id": {
        // …
      },
      "total": {
        "$sum": { "$arrayElemAt": [
            { "$map": {
                "input": "$products",
                "as": "product",
                "in": "$$product.num"
            }},
            0
        ]}
      }
    }}
];

With older versions, use "two" $group stages and the $first operator after processing with $unwind. And that's just for the "first" index value:

let pipeline = [
    { "$limit": 10 },
    { "$unwind": "$products" },
    { "$group": {
        "_id": "$_id",       // The document _id
        "otherField": { "$first": "$eachOtherFieldForGroupingId" },
        "productNum": { "$first": "$products.num" }
    }},
    { "$group": {
      "_id": {
        // …
      },
      "total": {
        "$sum": "$productNum"
      }
    }}
];

So in the latter case, after you $unwind you just want to use $first to get the "first" index from the array, and it would also be used to get every field you want to use as part of the grouping key from the original document. All elements would be copied for each array member after $unwind.

In the former case, $map just extracts the "num" values for each array member, then $arrayElemAt just retrieves the wanted index position.

Naturally the newer method for MongoDB 3.2 is better. If you wanted another array index then you would need to repeatedly get the $first element from the array and keep filtering it out from the array results until you reached the required index.

So whilst it's possible in earlier versions, it's a lot of work to get there.

answered Mar 15, 2016 at 6:04

Blakes Seven

50.5k14 gold badges132 silver badges136 bronze badges

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

3 Comments

Hongbo Miao Over a year ago

My meteor is using mongo 2.6.7. So I use the second way. At first I didn't understand the role of $eachOtherFieldForGroupingId, but now I got it! Thanks, works perfect!

Blakes Seven Over a year ago

@HongboMiao Sorry if that was an abstract term, but there was also the statement "...and it would also be used to get every field you want to use as part of the grouping key from the original document." Put in there to clarify. The most common aggregation pipeline mistake is people missing the concept that if you want to reference a field in a later stage after a $project or $group, then only the fields you actually ask that first usage to "emit" are actually going to be available.

Blakes Seven Over a year ago

@HongboMiao For future reference, you get a clearer example if you actually include a sample document and the full expression of your code rather than "_id": { ... } which is ambiguous and does not actually show the fields referenced. Also stated the "fact", "...which won't be the bundled server with meteor, but there is noting stopping you using a seperate server instance", which whilst might be "simple" to just start up the bundled server with meteor within your project in development, real world deployments are going to use an external server. So you should get used to that.

Collectives™ on Stack Overflow

Cannot get correct result when using MongoDB aggregation in meteor

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related