Chapter 4 Advanced CRUD Operations Lab 2

db.companies.find(
{“$and”: [{“stoned_year”: 2004},
		{“$or”: [{“category_code”:”social”},{“category_code”:”web”},{“stoned_month”:”October”}]},
		{“$or”: [{“category_code”:”social”}, {“category_code”:”web”}]}
		]
}) .count()

Spent most of the day trying to figure out why I’m getting an error that the first { is an illegal character? To some of you out there I’m sure it’s obvious? Please help :slight_smile:

First, your quotes are all wrong. Most likely a cut-n-paste from html not formatted correctly.

Then I reformatted and indented your query to:

{
    “$and”:
    [
        { “stoned_year”: 2004 },
        { “$or”:
            [
                {“category_code”:”social”},
                {“category_code”:”web”},
                {“stoned_month”:”October”}
            ]
        },
        { “$or”:
            [
                {“category_code”:”social”},
                {“category_code”:”web”}
            ]
        }
    ]
}

And I see nothing wrong with braces and brackets. May the only issue is the quotes.

But a few things.

  • I do not think that the field is named stoned_year unless they changed it since I took the course.

  • I think your logic is wrong for the year part.

  • Verify the value for stoned_month.

  • For {“$or”: [{“category_code”:”social”}, {“category_code”:”web”}]} take a look at https://docs.mongodb.com/manual/reference/operator/query/in/

  • Using variables makes complex query easier to read. For example, I would write your query as:

year = { "stoned_year" : 2004 }
month = { "stoned_month" : "October" }
social = { "category_code" : "social" }
web = { "category_code" : "web" }
query = { "$and" : 
  [ year ,
    { "$or" : [ social , web , month ] } ,
    { "$or" : [ social , web ] }
  ]
}

Hi, Steeve,

the query I applied is:

{
  '$or': [
    {
      '$and': [
        { founded_year: 2004 },
        {
          '$or': [ { category_code: 'social' }, { category_code: 'web' } ]
        }
      ]
    },
    {
      '$and': [
        { founded_month: 'October' },
        {
          '$or': [ { category_code: 'social' }, { category_code: 'web' } ]
        }
      ]
    }
  ]
}

But it’s wrong. I can not find the issue. Could you check the logic for me? Thank you~

Hi @ZIZI_Zhan and welcome to the MongoDB community forums. :wave:

You will want to take a look at the data and how it’s stored. If we run the following:

db.companies.find({}, {"founded_month": 1, "_id": 0})

We see the data returned as:

[
  { founded_month: 2 },    { founded_month: 11 },
  { founded_month: 10 },   { founded_month: 3 },
  { founded_month: null }, { founded_month: null },
  { founded_month: 11 },   { founded_month: 2 },
  { founded_month: null }, { founded_month: null },
  { founded_month: 3 },    { founded_month: 1 },
  { founded_month: 12 },   { founded_month: null },
  { founded_month: null }, { founded_month: null },
  { founded_month: 9 },    { founded_month: null },
  { founded_month: 5 },    { founded_month: null }
]

Here we can see that it appears the data is stored by month number only, but we can verify that further by running a .distinct() on the collection for the founded_month field with db.companies.distinct("founded_month") and we get the following:

[
  null, 1,  2,  3, 4,
  5,    6,  7,  8, 9,
  10,   11, 12
]

This means, that you are searching for a value for founded_month that does not exist and you are getting less records back than you should be.

Note that .distinct() will read all your documents into memory which could impact your server, so you don’t want to do that on large production collections during high peak times.

1 Like

this for for me:

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()

to ease the process of writing queries, start simple and increment the complexity:

for first part,

  • query for year 2004
  • query for category social
  • query for category web
  • query for social or web
  • query for social or web for year 2004

if you write it correct then make note of the total.

do the same for month 10, write the total and sum both to find a number to verify yourself.

  • query for month 10
  • query for category social
  • query for category web
  • query for social or web
  • query for social or web for month 10

then try combining both. also try to extract the same parts out of parenthesis just like we do in math. this will enhance both your physical muscle memory and mental thinking of writing queries.

2 Likes