Stuck on Chapter 2 Lab1

Hi,

I’m working on the first lab assignment on Chapter 2 and been stuck here… I’m trying to use $setIntersection to compare both the “cast” array (calling it using “$cast”) and the “favorites” (assgined it to a var) array to get a new array with the matching elements, then use $size to get the size of this resulting new array. However, I get the following error which is where I find my self stuck.

E QUERY [js] Error: command failed: {
“operationTime” : Timestamp(1535216717, 1),
“ok” : 0,
“errmsg” : “The argument to $size must be an array, but was of type: null”,
“code” : 17124,
“codeName” : “Location17124”,
“$clusterTime” : {
“clusterTime” : Timestamp(1535216717, 1),
“signature” : {
“hash” : BinData(0,“UsbF/d+BU7qNrTG/ieAoHROSgAQ=”),
“keyId” : NumberLong(“6559514413705986049”)
}
}
}

Has anyone encounter this? How did you go about debugging?

What I did is to code step by step, yes, the aggregate query becomes very long, but this way I can assert trial-and-error whether what I am doing is ok or not, so what I did was to make a lot of addFields, first addFields the simepl favorites array, then make another addFields using the setIntersection operator, then I did yet another addFields to compute the size, now there is a catch here as there will be some null values which mongo cannot compute its $size, so therefore I did some researched and discovered I can use $ifNull operator, basically means to compute the $size of the array only if it is not null, lastly I sorted as per the lab requirement, yes ultra long query!
Hope this helps!

1 Like

Thx Maulberto3! Sounds daunting to do this in the mongo shell, but will give it a shot and report back once I get through.

Hi, also a good idea is using and ide so you can check no curly braces are missing, and you can also structure each line putting step by step! Once you have the pipeline finish you just copy and paste in the shell! Also I use same approach as maulberto3, using $ifNull!
Bye!

Thx Maximiliano_19474. Question, how do you connect an IDE to the dataset in Atlas? I agree this would make structuring the query much easier, but not sure how to get access to the dataset to test the queries.

I did not connected! I just open my IDE and for example did something like
var pipeline = [
{
$match: {…}
},
{
$addFields: {…}
}
]

Once I was finished, I just open the terminal, connect to the Mongo DB shell using URL, and once inside I paste it there, so I can get access to pipeline variable. And then I run db.movies.aggregate(pipeline) and that’s all. If it did not work, again I make changes in IDE and copy and paste again. This also helps me to test each stage or part of the stage at a time, as for example I first make some match, and check if results where as expected, so I construct may pipeline like one step at a time. Hope it was helpful.

2 Likes

Thx to both maulberto3 and Maximiliano_19474! I finally solved it, and, yes, ultra long query! I ended up using 8 stages in my query.

Well done! It can be solved using five stages, I think… The setIntersection approach that @easyfastexcel suggested is correct, it would be something like this:

     totalFavorites: {
        $size: {
          $setIntersection: [
            "$cast",
            array_of_favorites
          ]
        }
      } 

Hope this helps.

José Carlos

5 Likes

Thanks for the $setIntersection idea. I was able to solve this with 6 stages (3 matches, 1 project, 1 sort, 1 limit) :slight_smile:
I’d like to see if there’s a simpler version of the solution. I used cast:{$exists:true} to avoid the array does not exists case.

Thanks,
Pavan

2 Likes

Hi @pawons,

Please note that your three $match stages are likely rearranged in a single $match by the query optimizer. Please see this doc page:

https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/#match-match-coalescence

José Carlos

Hi all ! personally find it rather challenging task to compose those huge aggregation queries in the shell . We had a JS files to check our work in chapter 1 .For chapter 2 we have … nothing . I need to shift the focus from aggregation framework to something else in order to check my self which is not cool and huge time waste
here what i’ve got
2018-09-24T22:47:29.880+0300 E QUERY [js] Error: command failed: {
“operationTime” : Timestamp(1537818443, 1),
“ok” : 0,
“errmsg” : “not authorized on aggregation to execute command { aggregate: “movies”, pipeline: [ { $match: { tomatoes.viewer.rating: { $gte: 3.0 }, countries: “USA” } }, { $project: { _id: 0.0, num_favs: { $size: { $ifNull: [ { $filter: { input: “$cast”, as: “cast”, cond: { $in: [ “$$cast”, [ “Sandra Bullock”, “Tom Hanks”, “Julia Roberts”, “Kevin Spacey”, “George Clooney” ] ] } } }, [] ] } }, title: 1.0, rating: “$tomatoes.viewer.rating” } }, { $sort: { num_favs: -1.0, rating: -1.0 } }, { $limit: 25.0 } ], cursor: {}, lsid: { id: UUID(“0c08eff7-45d7-46ab-96d7-69ce6a24f8d6”) }, $clusterTime: { clusterTime: Timestamp(1537818373, 1), signature: { hash: BinData(0, 4B0005AEFC8AD54A98A2196EA254AEE47C0D4D81), keyId: 6559514413705986049 } }, $db: “aggregation” }”,
“code” : 13,
“codeName” : “Unauthorized”,
“$clusterTime” : {
“clusterTime” : Timestamp(1537818443, 1),
“signature” : {
“hash” : BinData(0,“GHfm8W2J4kxzJbQphtwdwcRRNYA=”),
“keyId” : NumberLong(“6559514413705986049”)
}
}
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13

and yet another trial
MongoDB Enterprise Cluster0-shard-0:PRIMARY> db.movies.aggregate(pipeline)2018-09-24T23:00:41.094+0300 E QUERY [js] Error: command failed: { “operationTime” : Timestamp(1537819233, 1), “ok” : 0, “errmsg” : “not authorized on aggregation to execute command { aggregate: “movies”, pipeline: [ { $addFields: { commonToBoth: { $setIntersection: [ “$cast”, [ “Sandra Bullock”, “Tom Hanks”, “Julia Roberts”, “Kevin Spacey”, “George Clooney” ] ] } } }, { $match: { countries: { $in: [ “USA” ] }, tomatoes.viewer.rating: { $gte: 3.0 } } }, { $project: { tomatoes.viewer.rating: 1.0, title: 1.0, num_favs: { $size: { $ifNull: [ “$commonToBoth”, [] ] } }, _id: 0.0 } }, { $sort: { num_favs: -1.0, tomatoes.viewer.rating: -1.0, title: -1.0 } }, { $skip: 24.0 }, { $limit: 1.0 } ], cursor: {}, lsid: { id: UUID(“0c08eff7-45d7-46ab-96d7-69ce6a24f8d6”) }, $clusterTime: { clusterTime: Timestamp(1537819138, 28), signature: { hash: BinData(0, 2DEA21C46612AE8299F69822E21A3A3A06ACD81B), keyId: 6559514413705986049 } }, $db: “aggregation” }”,
“code” : 13,
“codeName” : “Unauthorized”,
“$clusterTime” : {
“clusterTime” : Timestamp(1537819233, 1),
“signature” : {
“hash” : BinData(0,“PckZZxbFrs48wNsfJenJo5ZjcCg=”),
“keyId” : NumberLong(“6559514413705986049”)
}
}
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:534:17
assert.commandWorked@src/mongo/shell/assert.js:618:16
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1056:12
@(shell):1:1
MongoDB Enterprise Cluster0-shard-0:PRIMARY>

Hi.
i’ve tried this approach too with got bunch of errors ,posted them below
any suggestions?

Thanks for the hint. I realized I could look at the detailed answer and see how the actual query is.

Best Regards,
Pavan

Hi @BaZeLs,

Please make sure you are on the aggregations database, it looks like you are trying to query a collection in the aggregation (in singular) database, which is not the correct db.

José Carlos

Hi Carlos,

I am also stuck on this lab. I got confused.
When I use the match stage alone, it works.
When I use the project stage alone, it works as well.
However, when I put the match stage and project stage altogether in the aggregate, it failed.

I will put my pseudo code below, can you please take a look?

db.movies.aggregate([{$match: {$and: [ { countries: }, { “tomatoes.viewer.rating”: {} } ]}}],[{ $project: { commonToBoth: { $setIntersection: [ “$cast”, favorites ] } }} ])

Hi i’m just failed in this lab, because in the lecture didn’t mention the $setIntersection before. So i don’t even know the function it self. i felt regret… :frowning: