Help with nested $or

Hello mongoDB community. Can anyone help me with a query for mongoDB University? The objective is to get a count based on the following requirements.

How many companies in the sample_training.companies dataset were

either founded in 2004

> * [and] either have the social category_code [or] web category_code,

[or] were founded in the month of October

> * [and] also either have the social category_code [or] web category_code?

My query is as follows:
db.companies.find({$or:[{$and:[{"founded_year":2004},{$or:[{"category_code":"social"},{"category_code":"web"}]}]},{$and:[{"founded_month":10},{$or: [{"category_code":"social"},{"category_code":"web"}]}]}]}).count()

I get a count of 146, but the quiz tells me I’m wrong. I have already used up 1 of my 3 attempts. Can anyone help?

One thing that helps a lot with queries is to format and indent. That’s what I did to get:

{ $or :
  [
    { $and:
      [
        { "founded_year":2004} ,
        { $or :  [ {"category_code":"social"} , {"category_code":"web"} ] }
      ]
    } ,
    { $and:
      [
        { "founded_month":10},
        { $or: [ {"category_code":"social"} , {"category_code":"web"} ] }
      ]
    }
  ]
}

And found nothing obviously wrong. So I ran you query on a my version of the data set and got a bigger number. So may be some field values were altered or some documents deleted. I suggest that you take a fresh copy of the data set. You may do that by creating a new cluster.

I also recommend that you take a look at https://docs.mongodb.com/manual/reference/operator/query/in/

A trick of mine is to use variables when working with queries to make them more readable. For this query, I would use something like

web = {"category_code":"web"}
social = {"category_code":"social"}
year = { "founded_year":2004 }
month = { "founded_month":10 }
codes = { "$or" : [ web , social ] }
year_and_codes = { "$and" : [ year , codes ] }
month_and_codes = { "$and" : [ month , codes ] }
final_query = { "$or" : [ year_and_codes , month_and_codes ] }

I find it easier to match braces and brackets. It make editing easier as you edit smaller lines.

3 Likes

Hi Steve. Thank you for the thoughtful reply. I come from SQL-land. Please excuse my lack of experience in this domain. :grin:

After reading your reply, I ended up refreshing the mongoDB sample data. Now it works.

I’m using the windows cmd prompt as my editor when using mongoDB Shell. What editor are you using that allows you to “prettify” your queries?

I love the idea of using variables for this. Thanks for that tip.

No worries. There is nothing to excuse. We all started there.

You may then mark my previous post as the solution.

For very simple queries I use the shell too. For very complex stuff, I either use Compass pipeline editor, or vi with a js file that I load() and in the shell.

Divide and conquer !!!

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.