FAQ: CRUD I: Querying on Array Fields - Review

[discourse-admin]

This community-built FAQ covers the “Review” exercise from the lesson “CRUD I: Querying on Array Fields”.

Paths and Courses
This exercise can be found in the following Codecademy content:

[Beta] Learn MongoDB

FAQs on the exercise Review

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply () below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply () below!
You can also find further discussion and get answers to your questions over in Language Help.

Agree with a comment or answer? Like () to up-vote the contribution!

Need broader help or resources? Head to Language Help and Tips and Resources. If you are wanting feedback or inspiration for a project, check out Projects.

Looking for motivation to keep learning? Join our wider discussions in Community

Learn more about how to use this guide.

Found a bug? Report it online, or post in Bug Reporting

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

[john_weathers]

My question is in regard to querying embedded documents in an array field. The final optional task in this review is what I’m trying to better understand: Find all the restaurants that have received only grades above a "B".

The example data in this exercise (this is a simplified version, and bit of a crude representation, of the collection called listingsAndReviews from the exercise) is structured like this:

{ name: 'example restaurant' location: 'bronx' grades: [ { date: 'april 5 2022', grade: 'A' }, { date: 'april 3 2021', grade: 'B' } ] }, { name: 'example restaurant 2' location: 'manhattan' grades: [ { date: 'march 5 2022', grade: 'C' }, { date: 'april 7 2021', grade: 'B' } ] }, { name: 'example restaurant 3' location: 'downtown' grades: [ { date: 'april 8 2022', grade: 'A' }, { date: 'april 10 2021', grade: 'A' }, { date: 'april 2 2020', grade: 'A' } ] }

I might be missing something obvious, but I’m not seeing any clear cut ways to isolate documents that have ONLY grades above B (so in other words all As). I think the closest I found was db.listingsAndReviews.find({“grades.grade”: {$nin: [“B”, “C”]}}) but there are additional grade strings like “Not Yet Graded” and other random letters like P and Z, which , sure I could just add them to the $nin array but I’m wondering if this is a roundabout way. The only other way I thought might also work was similarly using the $ne operator.

Am I missing something obvious here in dealing with these array embedded documents? I tried different versions of things like using $gt, $lt, and other operators, different techniques suggested in the exercises, etc. but they mostly seem to work on matching ONE value and then returning the entire document rather than a way of ensuring all nested documents have a particular matching value for a field before returning the entire document.

In my Googling I came across something called aggregation that I haven’t learned yet…I’m wondering if this is how you deal with this type of thing but wanted to double check I wasn’t missing the obvious.

Thanks.

[mtrtmk]

Find all the restaurants that have received only grades above a “B”.

Yes, it is a tricky optional task. I tried different queries with combinations of operators on the "grades.grade" field. But, the matched documents weren’t as desired for the task.

Played around and browsed the documentation for querying array of embedded documents. Eventually, this query worked for me:

db.listingsAndReviews.find({ grades: { $not: { $elemMatch: { grade: { $ne: "A" } } } } })

This returns all restaurants that have only grades of "A".

grades is an array of embedded documents. Within each embedded document, there are date/grade/score fields.
Since querying on grades.grade wasn’t proving fruitful in generating desired outcome, so I decided to query only on the grades array making use of $elemMatch operator.

...{ grades: { $elemMatch: { grade: { $ne: "A" } } } } 

selects those documents whose grades array has at least one embedded document (could be one or more than one embedded document) with a grade other than "A".

Negating the above by using $not,

...{ grades: { $not: { $elemMatch: { grade: { $ne: "A" } } } } } 

is equivalent to selecting those documents whose grades array has no embedded document with a grade other than "A".
(The original query was 1 or more embedded documents with a grade other than "A". The negation of that query will be less then 1 (i.e. 0) embedded documents with a grade other than "A").

For the collection given in the exercise, the query matches the correct documents. For a collection outside the exercise, I can think of certain edge cases which may necessitate some modifications to the query. But those changes would be dependent on how we wish to decide the edge cases.

EDIT:
If I am not mistaken (though I may be wrong):

db.listingsAndReviews.find({ grades: { $not: { $elemMatch: { grade: { $ne: "A" } } } } })
// will give same result as:
db.listingsAndReviews.find({ $nor: [{grades: { $elemMatch: { grade: { $ne: "A" } } } }]})

Related link: How to match documents where all array elements match predicate. - http://asya999.github.io/

[john_weathers]

Thank you! This is very helpful. Glad to know I was more or less on the right track and that I wasn’t missing some other obvious strategy. The link is also a big help. Seems this negation strategy is fundamental for these types of queries. Appreciate it big time!

[text9062315027]

Here are my solutions to the final exercise (with a few additions that I found useful when I tested the commands).

Find all the restaurants that have received a Michelin star:
db.listingsAndReviews.find({ michelin_stars: { $exists : true } })

Find all the restaurants that have received a Michelin star exactly 14 times:
db.listingsAndReviews.find({ michelin_stars: {$size: 14} })

Find the number of restaurants that match this criterium:
db.listingsAndReviews.find({ michelin_stars: {$size: 14} }).count()

Find all the restaurants that have received a Michelin star every year from 2006 to 2016:
db.listingsAndReviews.find({ 'michelin_stars': {$all: [2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016] } } )
Note: There must a simpler way to define the range from 2006 to 2016. If anyone has the solution to this please add it

Find the number of restaurants that have received a Michelin star every year from 2006 to 2016:
db.listingsAndReviews.find({ 'michelin_stars': {$all: [2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016] } } ).count()

Find all the restaurants that have received only grades above a “B”.

This one I struggled with a lot. A big THANKS to mtrtmk for providing a solution. It is difficult for me to understand that there is a simple way to make this query.

I first tried with this query:
db.listingsAndReviews.find({"grades.grade": {$gt:"B"}})

It turned out that it gave me all documents where there is at least one embedded document with a grade “greater than” B (=A).

Then I looked at mtrtmk’s solution:
db.listingsAndReviews.find({ grades: { $not: { $elemMatch: { grade: { $ne: "A" } } } } })

It worked.

For the sake of context here is his expiation on why it works:

I decided to query only on the grades array making use of $elemMatch operator.

db.listingsAndReviews.find({ grades: { $elemMatch: { grade: { $ne: "A" } } } } )
selects those documents whose grades array has at least one embedded document (could be one
or more than one embedded document) with a grade other than “A”.

Negating the above by using $not,
db.listingsAndReviews.find({ grades: { $not: { $elemMatch: { grade: { $ne: "A" } } } } } )
is equivalent to selecting those documents whose grades array has no embedded document with a
grade other than “A”.

I had to read this several times and also run the two queries in the command line to fully get it

[nop_lop]

For the first question:

Find all the restaurants that have received a Michelin star exactly 14 times

the suggestion given by @text9062315027 works well for exact numbers. On trying to evaluate a range, say winning Michelin stars 14 times or more , or less than 5 time winners, this becomes a bit challenging.

I tried this with 14 times or more and got 0 , despite knowing that at exactly 14 Michelin starts theres one restaurant.

restaurants> db.listingsAndReviews.find({michelin_stars: {$size:14}}).count()
1
# for 14 times or more
restaurants> db.listingsAndReviews.find({michelin_stars: {$gte : {$size:14}}}).count()
0

I don’t know how to resolve this

[mtrtmk]

As the documentation for ( $size — MongoDB Manual) mentions:

$size does not accept ranges of values.

Have a look at: How to find a document with array size within a range in MongoDB? - Stack Overflow

Using the approach from the above link, the following query will select all restaurants with 14 or more michelin stars:

db.listingsAndReviews.find({ "michelin_stars.13": {$exists: true}  })