Group by Multiple Fields In Meteor

Question

What I am trying to achieve is, within a given date range, I want to group Users by First time and then by userId.

I tried below query to group by Multiple Fields,

ReactiveAggregate(this, Questionaire,
[
    {
        "$match": {
            "time": {$gte: fromDate, $lte: toDate},
            "userId": {'$regex' : regex}
        }
    },
    {
        $group : {
            "_id": {
                "userId": "$userId",
                "date": { $dateToString: { format: "%Y-%m-%d", date: "$time" } }
            },
            "total": { "$sum": 1 }
           }
    }
], { clientCollection: "Questionaire" }
);

But When I execute it on server side, it shows me below error,

Exception from sub Questionaire id kndfrx9EuZ5EejKmE 
Error: Meteor does not currently support objects other than ObjectID as ids

Neil Lunn · Accepted Answer · 2017-09-03T13:41:57.120

The message actually says it all, since the "compound" _id value that you are generating via the $group is not actually supported in the clientCollection output which will be published.

The simple solution of course is to not use the resulting _id value from $group as the "final" _id value in the generated output. So just as the example on the project README demonstrates, simply add a $project that removes the _id and renames the present "compound grouping key" as a different property name:

ReactiveAggregate(this, Questionaire,
[
    {
        "$match": {
            "time": {$gte: fromDate, $lte: toDate},
            "userId": {'$regex' : regex}
        }
    },
    {
        $group : {
            "_id": {
                "userId": "$userId",
                "date": { $dateToString: { format: "%Y-%m-%d", date: "$time" } }
            },
            "total": { "$sum": 1 }
           }
    },
    // Add the reshaping to the end of the pipeline
    {
        "$project": {
            "_id": 0,           // remove the _id, this will be automatically filled
            "userDate": "$_id", // the renamed compound key
            "total": 1
        }
    }
], { clientCollection: "Questionaire" }
);

The field order will be different because MongoDB keeps the existing fields ( i.e "total" in this example ) and then adds any new fields to the document. You can cou[nter that by using different field names in the $groupand $project stages rather than the 1 inclusive syntax.

Without such a plugin, this sort of reshaping is something that has been regularly done in the past, by again renaming the output _id and supplying a new _id value compatible with what meteor client collections expect to be present in this property.

On closer inspection of how the code is implemented, it is probably best to actually supply an _id value in the results because the plugin actually makes no effort to create an _id value.

So simply extracting one of the existing document _id values in the grouping should be sufficient. So I would add a $max to do this, and then replace the _id in the $project:

ReactiveAggregate(this, Questionaire,
[
    {
        "$match": {
            "time": {$gte: fromDate, $lte: toDate},
            "userId": {'$regex' : regex}
        }
    },
    {
        $group : {
            "_id": {
                "userId": "$userId",
                "date": { $dateToString: { format: "%Y-%m-%d", date: "$time" } }
            },
            "maxId": { "$max": "$_id" },
            "total": { "$sum": 1 }
           }
    },
    // Add the reshaping to the end of the pipeline
    {
        "$project": {
            "_id": "$maxId",           // replaced _id
            "userDate": "$_id",       // the renamed compound key
            "total": 1
        }
    }
], { clientCollection: "Questionaire" }
);

This could be easily patched in the plugin by replacing the lines

  if (!sub._ids[doc._id]) {
    sub.added(options.clientCollection, doc._id, doc);
  } else {
    sub.changed(options.clientCollection, doc._id, doc);
  }

With using Random.id() when the document(s) output from the pipeline did not already have an _id value present:

  if (!sub._ids[doc._id]) {
    sub.added(options.clientCollection, doc._id || Random.id(), doc);
  } else {
    sub.changed(options.clientCollection, doc._id || Random.id(), doc);
  }

But that might be a note to the author to consider updating the package.

Ohh! So `$Project` is all that has to be changed. Thanks ton! let me try this and get back to the conclusion. — Ankur Soni, Sep 03 '17 at 09:00
@AnkurSoni The point is to basically remove the "compound _id" value that the error is complaining about. According to the documentation this should be automatically filled in the created client collection with a default value. I also linked to the venerable response from Andrew Mao where we previously populated the returned content with [`Random.id()`](https://docs.meteor.com/packages/random.html) in the `_id` of the published collection for the same reason. — Neil Lunn, Sep 03 '17 at 09:03
@AnkurSoni I see you tried to make an edit, but the edit should have no bearing on the result. There is nothing wrong with a specific property ( other than the _id in the result collection) containing a compound value, as it's perfectly valid. The reason it's put that way is to align with the question you asked. If you desire separate fields in your own output, then that's up to your own implementation. But it has nothing to do with the question or the answer. — Neil Lunn, Sep 03 '17 at 12:39
I agree! but since I am executing your code, I get nothing populated on my UI, the error was `Uncaught Error: Expected to find a document already present for removed`. Is it because `_id` is suppressed? — Ankur Soni, Sep 03 '17 at 12:41
@AnkurSoni That's sounding like something unrelated to the code here. Maybe you need to restart the project. If I have some time I can try to spin up a meteor instance with a minimal example. But the general "compound _id" issue is something I know of ( hence the answer ) and going by the documentation a new `_id` "should" be generated. You might also want to inspect the target collection directly and see what is in there. Be careful if you are using something like collection2 with a defined schema that does not match the output shape. — Neil Lunn, Sep 03 '17 at 12:48
_id is undefined on UI. It not getting filled automatically. — Ankur Soni, Sep 03 '17 at 13:02
@AnkurSoni Hmm. Yes, looking at the source. The reactive aggregate wrapper basically has `sub.added(options.clientCollection, doc._id, doc)` to write to the collection. It's presuming `_id` is present, which is contrary to the documentation. Like I said, I've typically written this myself in the past and used `Random.id()` here instead. Still doing some digging. The Reactive aggregate package does not really add anything of much detail, and it might be better just to write in the specifics and not use it at all. But still digging. — Neil Lunn, Sep 03 '17 at 13:07
Thank you so much for helping out. But I assume further assistance as well as I am new to this specific aggregate stuff. — Ankur Soni, Sep 03 '17 at 13:12
I got the output when I set `"_id": { $concat: [ "$_id.userId", " - ", "$_id.date" ] },` in `$project`. — Ankur Soni, Sep 03 '17 at 13:32
@AnkurSoni I actually came up with something different ( see added details ), that when reading the code should suffice. But essentially have "something" in the `_id` when output ( not a compound value ) in order to keep it happy. The actual code increments a "version" internally, so every refresh should return fresh data. Regardless of what is actually in the `_id`. — Neil Lunn, Sep 03 '17 at 13:44

Group by Multiple Fields In Meteor

1 Answers1