Lab 4.2 Aggregation Performance

I am currently on Lab 4.2 and I am getting a different error than what the question reports.
I imported the restaurants.json file using this command:

$ mongoimport -d m201 -c restaurants --drop restaurants.json

I have confirmed no indexes are on the db or collection.
However, I receive this error (shows commands entered and output):

cw00dw0rd-desktop(mongod-3.6.3) test> db.restaurants.aggregate([   
{ $match: { stars: { $gt: 2 } } },   { $sort: { stars: 1 } },   
{ $group: { _id: "$cuisine", count: { $sum: 1 } } } ])
printStackTrace@src/mongo/shell/utils.js:112:15
DBCollection.prototype.aggregate@/home/cw00dw0rd/.mongorc.js:263:13
@(shell):1:1

 2018-10-25T16:15:42.314-0400 E QUERY    [js] uncaught exception: aggregate failed: {
  "ok": 0,
  "errmsg": "The 'cursor' option is required, except for aggregate with the explain argument",
  "code": 9,
  "codeName": "FailedToParse"
}

Where the question has this as the expected outcome of the initial aggregate command:

{
  "ok": 0,
  "errmsg": "Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.",
  "code": 16819,
  "codeName": "Location16819"
}

I suspected this could be a version issue so I looked up cursor with aggregate and found this.
That indicates that cursor is:

Optional. Specifies the initial batch size for the cursor. The value of the cursor field is a document with the field batchSize . See Specify an Initial Batch Size for syntax and example.

New in version 2.6.

So I updated the query to have use the cursor and batchSize field and now get this as my result:

cw00dw0rd-desktop(mongod-3.6.3) m201> db.restaurants.aggregate([   
{ $match: { stars: { $gt: 2 } } },   { $sort: { stars: 1 } },   { $group: { _id: "$cuisine", count: { $sum: 1 } } }], {cursor: { batchSize: 0} })
{
  "cursor": {
    "firstBatch": [ ],
    "id": NumberLong("9115248565169826387"),
    "ns": "m201.restaurants"
  },
  "ok": 1
}

I am not sure now if I am able to proceed with this question as the syntax no longer seems to be valid. Any insight on this would be greatly appreciated, as I am fairly confused at this point.

  • Chris

@ Christopher_79126

This isn’t a version issue, assuming you are using (as shown in your post) version 3.6.x. But the first line that you show ’ cw00dw0rd-desktop(mongod-3.6.3) test>’ suggests that you’re looking at the ‘test’ database, not ‘m201’, while the second line that you show `cw00dw0rd-desktop(mongod-3.6.3) m201>’ does use ‘m201’.

To clarify this, would you run the following set of commands and post the results back here:

MongoDB Enterprise > db
m201
MongoDB Enterprise > show collections
orders
people
restaurants
MongoDB Enterprise > db.restaurants.getIndexes()
[
{
“v” : 2,
“key” : {
“_id” : 1
},
“name” : “id”,
“ns” : “m201.restaurants”
}
]
MongoDB Enterprise >

This shows my namespace for this Lab; your results will be a bit different, but should be essentially the same. Then run your aggregation query and let’s see what you get. Post those back here and I’ll try to help.

Hi @DHz,
I had been troubleshooting before starting my question and had been restarting the server, rebuilding database, etc but also caught that I was using ‘test’ about halfway through my question. I have done as you asked and pasted the output below. Thank you for any help you can offer!

  • Chris

Edit: Re-Added Code as initial code did not format properly.

cw00dw0rd-desktop(mongod-3.6.3) m201> db
m201
cw00dw0rd-desktop(mongod-3.6.3) m201> show collections
restaurants → 173.024MB / 77.934MB
cw00dw0rd-desktop(mongod-3.6.3) m201> db.restaurants.getIndexes()
[
  {
    "v": 2,
    "key": {
      "_id": 1
    },
    "name": "_id_",
    "ns": "m201.restaurants"
  }
]
cw00dw0rd-desktop(mongod-3.6.3) m201> db.restaurants.aggregate([
...   { $match: { stars: { $gt: 2 } } },
...   { $sort: { stars: 1 } },
...   { $group: { _id: "$cuisine", count: { $sum: 1 } } }
... ])
printStackTrace@src/mongo/shell/utils.js:112:15
DBCollection.prototype.aggregate@/home/cw00dw0rd/.mongorc.js:263:13
@(shell):1:1

2018-10-26T14:31:12.458-0400 E QUERY    [js] uncaught exception: aggregate failed: {
  "ok": 0,
  "errmsg": "The 'cursor' option is required, except for aggregate with the explain argument",
  "code": 9,
  "codeName": "FailedToParse"
}
cw00dw0rd-desktop(mongod-3.6.3) m201>

@ Christopher_79126

Thanks. That’s very helpful actually. So, when I put this into my test system all as one line

db.restaurants.aggregate([ { $match: { stars: { $gt: 2 } } }, { $sort: { stars: 1 } }, { $group: { _id: “$cuisine”, count: { $sum: 1 } } }])

I get a somewhat different error message, as follows

2018-10-26T10:19:52.489-0700 E QUERY [thread1] SyntaxError: illegal character @(shell):1:104

And it looks like you are using an editor that is putting “smart quotes” for your quotation marks around the field name “$cuisine”. You need to be sure to use a very simple-minded text-only editor for all your queries etc. I don’t know what OS you’re using, but most systems have a very simple text-based editor – for example, Notepad on Windows, or Mousepad on Ubuntu/Debian. Unfortunately, I don’t think there’s one on a Mac, so you may have to search around for one if you’re using Mac OS.

Anyway, change the quotes around “$cuisine” to simple quotation marks and try again and let me know how it goes. Good luck.

1 Like

@DHz thanks for the reply!

I am currently using Ubuntu 18.04 and copying directly from the lab into the terminal/shell. I have not made any changes to the text being provided in the lab. Since I am not getting the memory limit error I haven’t even moved past that step.
When attempting to confirm if “smart quotes” are sneaking in there somehow, I don’t believe they are. I believe this is happening when pasting the content into this forum. So for you, when copying it out of my reply.

I took a screenshot of my terminal to better showing how it is formatted. Also, if I put it on one line I get the same cursor required message, however, if I copy the line from this forum, I get the SyntaxError message.

Thanks again for following up! I will await your reply!

Edit: I updated my most recent post before this one. Although, the 4 spaces were there for all of the lines, it was not formatting as preformatted text. Removed and re-added and now should properly show quotes and formatting.

@ Christopher_79126

Well, I’m not having any luck in trying to replicate your problem. Let’s approach this in a different way and see what we can do.

Please go into your shell and just type in (do not cut & paste) the following

db.restaurants.aggregate([ { $match: { stars: { $gt: 2 } } } ])

This should give you the first set of matching documents, with no errors. At least, it does on my system. Next enter – again, directly into the shell and not cut & paste – the following

db.restaurants.aggregate([ { $match: { stars: { $gt: 2 } } }, { $sort: { stars: 1 } } ])

That should throw the expected error. Then post what you get back here and I’ll try to see what’s going on. Good luck.

Well I have some interesting news. I followed your direction but still just received the cursor message. So I decided to just try and use the cursor option that I found here. The below output is of the two commands you requested and the third command is the aggregate with sort and the cursor and as you see it reports code: 16819, good news! Also, below that one I took your first request without the sort and applied the cursor option to it and I get a result.

I think this will let me move on with the lab, now that I am getting the appropriate error, but if you think otherwise please let me know. Thanks for your help with all of this!

cw00dw0rd-desktop(mongod-3.6.3) m201> db.restaurants.aggregate([ { $match: { stars: {$gt: 2 } } } ])
printStackTrace@src/mongo/shell/utils.js:112:15
DBCollection.prototype.aggregate@/home/cw00dw0rd/.mongorc.js:263:13
@(shell):1:1

2018-10-29T11:08:40.232-0400 E QUERY    [js] uncaught exception: aggregate failed: {
  "ok": 0,
  "errmsg": "The 'cursor' option is required, except for aggregate with the explain argument",
  "code": 9,
  "codeName": "FailedToParse"
}
cw00dw0rd-desktop(mongod-3.6.3) m201> db.restaurants.aggregate([ { $match: { stars: { $gt: 2 } } }, { $sort: { stars: 1 } } ])
printStackTrace@src/mongo/shell/utils.js:112:15
DBCollection.prototype.aggregate@/home/cw00dw0rd/.mongorc.js:263:13
@(shell):1:1

2018-10-29T11:09:52.330-0400 E QUERY    [js] uncaught exception: aggregate failed: {
  "ok": 0,
  "errmsg": "The 'cursor' option is required, except for aggregate with the explain argument",
  "code": 9,
  "codeName": "FailedToParse"
}
cw00dw0rd-desktop(mongod-3.6.3) m201> db.restaurants.aggregate([ { $match: { stars: { $gt: 2 } } }, { $sort: { stars: 1 } } ], {cursor: { } } )
printStackTrace@src/mongo/shell/utils.js:112:15
DBCollection.prototype.aggregate@/home/cw00dw0rd/.mongorc.js:263:13
@(shell):1:1

2018-10-29T11:10:21.437-0400 E QUERY    [js] uncaught exception: aggregate failed: {
  "ok": 0,
  "errmsg": "Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.",
  "code": 16819,
  "codeName": "Location16819"
}
cw00dw0rd-desktop(mongod-3.6.3) m201> db.restaurants.aggregate([{ $match: {stars: {$gt: 2}}}], { cursor: {}})
{
  "cursor": {
    "firstBatch": [
      {
        "_id": ObjectId("5bd2245959e3d5adc7da35df"),
        "name": "Daniel",
        "cuisine": "Moroccan",

@ Christopher_79126

Well, first of all, I’m glad you found an answer and can move forward. That’s the most important issue. However, it does appear that you’re using the correct command – that is, the shell helper function db.collection.aggregate() and although the database command requires the cursor option, the shell helper should not. The documentation says

Rather than run the aggregate command directly, most users should use the db.collection.aggregate() helper provided in the mongo shell or the equivalent helper in their driver. In 2.6 and later, the db.collection.aggregate() helper always returns a cursor.

So it’s an interesting question why you are not getting the shell helper, but are just running the command directly in some way.

In any case, good luck with the rest of the course. And thanks for working through this. Very interesting!