Advanced Mongo Queries Class 1

Sample Data

[
{
"name": "IronMan",
"powers": [
"robot",
"money"
],
"health": 33,
"villains": [
{
"name": "Mandarin",
"health": 50
},
{
"name": "Titanium Man",
"health": 54
}
],
"metadata": {
"favouriteColor": "red",
"age": 13
}
},
{
"name": "Batman",
"powers": [
"intelligence",
"money"
],
"health": 40,
"villains": [
{
"name": "TwoFace",
"health": 20
},
{
"name": "Redhood",
"health": 65
}
],
"metadata": {
"favouriteColor": "blue",
"age": 44
}
},
{
"name": "Spider-Man",
"powers": [
"intelligence"
],
"health": 60,
"villains": [
{
"name": "Venom",
"health": 65
},
{
"name": "Dr. Octavio",
"health": 76
}
],
"metadata": {
"favouriteColor": "red",
"age": 44
}
},
{
"name": "Thor",
"powers": [
"god",
"magic"
],
"health": 57,
"villains": [
{
"name": "Surtur",
"health": 50
},
{
"name": "Hela",
"health": 87
}
],
"metadata": {
"favouriteColor": "yello",
"age": 313
}
},
{
"name": "Dr. Strange",
"powers": [
"magic"
],
"health": 86,
"villains": [
{
"name": "Hela",
"health": 87
},
{
"name": "Dormamu",
"health": 100
}
],
"metadata": {
"favouriteColor": "orange",
"age": 44
}
}
]

create a collection name as `heros`

Create a database with this command use nxm201repeat

nxm201 is database name

command to create a collection with the heros name
db.createCollection("heros")

Insert all the data using this command db.heros.insertMany()

command to show all the data in heros collection db.heros.find().pretty()

Q1) Query Find a hero whose health is 40.

query db.heros.find({health:40});

response

[ { _id: ObjectId("662005262ce7c8fdc90a8c3e"), name: 'Batman', powers: [ 'intelligence', 'money' ], health: 40, villains: [ { name: 'TwoFace', health: 20 }, { name: 'Redhood', health: 65 } ], metadata: { favouriteColor: 'blue', age: 44 } } ]

Q2) Find a hero whose favourite color is red and age is 44

query
db.heros.find({metadata: {favouriteColor:"red", age:44} })

response

[
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3f"),
    name: 'Spider-Man',
    powers: [ 'intelligence' ],
    health: 60,
    villains: [
      { name: 'Venom', health: 65 },
      { name: 'Dr. Octavio', health: 76 }
    ],
    metadata: { favouriteColor: 'red', age: 44 }
  }
]

Question 3 . write a query to find a hero whose age is 44 and favouriteColor is red
db.heros.find({metadata: { age:44,favouriteColor:"red"} })

but did not find anything.

learning number 1
In mongodb ORDER MATTERS in database

in normal object js order doesn't matters

Q4) Find a hero whose favoriteColor is red

db.heros.find({metadata:{favouriteColor:"red"}})

here nothing we are getting.

learning number 2
so not just orders matter but exact key should also match
EXACT ORDER MATTERS

Q4) Question -> Find those heros whose favoriteColor id red

Query ->  db.heros.find({"metadata.favouriteColor":"red"})response

response

[
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3d"),
    name: 'IronMan',
    powers: [ 'robot', 'money' ],
    health: 33,
    villains: [
      { name: 'Mandarin', health: 50 },
      { name: 'Titanium Man', health: 54 }
    ],
    metadata: { favouriteColor: 'red', age: 13 }
  },
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3f"),
    name: 'Spider-Man',
    powers: [ 'intelligence' ],
    health: 60,
    villains: [
      { name: 'Venom', health: 65 },
      { name: 'Dr. Octavio', health: 76 }
    ],
    metadata: { favouriteColor: 'red', age: 44 }
  }
]

keep "metadata.favouriteColor" in double quotes

Q5. Find all the heros whose age is less than 50

query - db.heros.find({"metadata.age":{$lt:50}})

response  

[
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3d"),
    name: 'IronMan',
    powers: [ 'robot', 'money' ],
    health: 33,
    villains: [
      { name: 'Mandarin', health: 50 },
      { name: 'Titanium Man', health: 54 }
    ],
    metadata: { favouriteColor: 'red', age: 13 }
  },
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3e"),
    name: 'Batman',
    powers: [ 'intelligence', 'money' ],
    health: 40,
    villains: [
      { name: 'TwoFace', health: 20 },
      { name: 'Redhood', health: 65 }
    ],
    metadata: { favouriteColor: 'blue', age: 44 }
  },
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3f"),
    name: 'Spider-Man',
    powers: [ 'intelligence' ],
    health: 60,
    villains: [
      { name: 'Venom', health: 65 },
      { name: 'Dr. Octavio', health: 76 }
    ],
    metadata: { favouriteColor: 'red', age: 44 }
  },
  {
    _id: ObjectId("662005262ce7c8fdc90a8c41"),
    name: 'Dr. Strange',
    powers: [ 'magic' ],
    health: 86,
    villains: [ { name: 'Hela', health: 87 }, { name: 'Dormamu', health: 100 } ],
    metadata: { favouriteColor: 'orange', age: 44 }
  }
]

Q6 . Find all heroes whose powers are intelligence and money

query
db.heros.find({powers: ["intelligence","money"]})


response

[
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3e"),
    name: 'Batman',
    powers: [ 'intelligence', 'money' ],
    health: 40,
    villains: [
      { name: 'TwoFace', health: 20 },
      { name: 'Redhood', health: 65 }
    ],
    metadata: { favouriteColor: 'blue', age: 44 }
  }
]

this query db.heros.find({powers: ["intelligence","money"]}) will not give any result.

because order does matter in mongodb queries

Q 7 ) Find heros whose powers is intelligence.

(write a twitter post on this) [video timestamp: 1hr 20min to 1hr 30 min ]

 db.heros.find({powers:["intelligence"]})  

[
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3f"),
    name: 'Spider-Man',
    powers: [ 'intelligence' ],
    health: 60,
    villains: [
      { name: 'Venom', health: 65 },
      { name: 'Dr. Octavio', health: 76 }
    ],
    metadata: { favouriteColor: 'red', age: 44 }
  }
]

when the value is an array and its an array of only one length like here we have Spiderman and Spiderman has only one power intelligence

nxm201repeat> db.heros.find({powers:["intelligence"]})

[
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3f"),
    name: 'Spider-Man',
    powers: [ 'intelligence' ],
    health: 60,
    villains: [
      { name: 'Venom', health: 65 },
      { name: 'Dr. Octavio', health: 76 }
    ],
    metadata: { favouriteColor: 'red', age: 44 }
  }
]

when the value is an array of 1 string.

it tries to assume that as a string

mongo db will assume our query as powers: ["intelligence"] ===> powers:"intelligence"

we will cross check this.

the same query we will write without the array.

The second thing is when you are looking for a string it looks for in the array.
powers: "intelligence" ===> powers:["money","xyz","intelligence"]

nxm201repeat> db.heros.find({"powers":"intelligence"})
[
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3e"),
    name: 'Batman',
    powers: [ 'intelligence', 'money' ],
    health: 40,
    villains: [
      { name: 'TwoFace', health: 20 },
      { name: 'Redhood', health: 65 }
    ],
    metadata: { favouriteColor: 'blue', age: 44 }
  },
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3f"),
    name: 'Spider-Man',
    powers: [ 'intelligence' ],
    health: 60,
    villains: [
      { name: 'Venom', health: 65 },
      { name: 'Dr. Octavio', health: 76 }
    ],
    metadata: { favouriteColor: 'red', age: 44 }
  }
]

research more about this phenomenon I didn't get it properly from class. Why It Happens

Q 8) Find all the heroes whose power is intelligence and money, irrespective of the order

refer docs

nxm201repeat> db.heros.find({"powers":{$all :["money","intelligence"]}})

or

nxm201repeat> db.heros.find({"powers":{$all :["money","intelligence"]}})

[
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3e"),
    name: 'Batman',
    powers: [ 'intelligence', 'money' ],
    health: 40,
    villains: [
      { name: 'TwoFace', health: 20 },
      { name: 'Redhood', health: 65 }
    ],
    metadata: { favouriteColor: 'blue', age: 44 }

Q 9) find all the heros who is having any of the powers- intelligence, robot

nxm201repeat> db.heros.find({powers:{$in:["intelligence","robot"]}})
[
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3d"),
    name: 'IronMan',
    powers: [ 'robot', 'money' ],
    health: 33,
    villains: [
      { name: 'Mandarin', health: 50 },
      { name: 'Titanium Man', health: 54 }
    ],
    metadata: { favouriteColor: 'red', age: 13 }
  },
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3e"),
    name: 'Batman',
    powers: [ 'intelligence', 'money' ],
    health: 40,
    villains: [
      { name: 'TwoFace', health: 20 },
      { name: 'Redhood', health: 65 }
    ],
    metadata: { favouriteColor: 'blue', age: 44 }
  },
  {
    _id: ObjectId("662005262ce7c8fdc90a8c3f"),
    name: 'Spider-Man',
    powers: [ 'intelligence' ],
    health: 60,
    villains: [
      { name: 'Venom', health: 65 },
      { name: 'Dr. Octavio', health: 76 }
    ],
    metadata: { favouriteColor: 'red', age: 44 }
  }
]

Q 10) Is $all and $and same.

No, $all and $and are not the same in MongoDB.

$all is an operator that selects documents where the value of a field is an array that contains all the specified elements. It is used to match documents that have an array field containing all the specified values.

On the other hand, $and is a logical operator that performs a logical AND operation on an array of expressions and selects the documents that satisfy all the expressions. It is used to join query clauses with a logical AND and returns all documents that match the conditions of both clauses.

In terms of behavior, $all is equivalent to an $and operation of the specified values. For example, the statement { tags: { $all: [ "ssl" , "security" ] } } is equivalent to { $and: [ { tags: "ssl" }, { tags: "security" } ] }.

So, while both operators involve logical operations, they serve different purposes in MongoDB queries.

Related resources:

Q 11) find all heros who have a villan of name Hella and age

before that learn an important concept here

consider this sample JSON data

// lets suppose its a colors collection

{
  id:1,
  "colors":["red","blue","green"]
},
{
  id:2,
  "colors":["green"]
},
{
  id:3,
  "colors":["red"]
},

if i query using db. colors.find({colors:["red"]})

which one will I find `I will get ID 3 data only`

we won't get 1 and 3 ID

The reason being that it is an array of string and we are only looking for red in the array so you won't get 3, you will get 1 because it is matching

and now if we remove array here and modify our query like this
db. colors.find({colors:"red"})

nxm201repeat> db.heros.find({villains:{name:"Hela",health:87}})

    [
      {
        _id: ObjectId("662005262ce7c8fdc90a8c40"),
        name: 'Thor',
        powers: [ 'god', 'magic' ],
        health: 57,
        villains: [ { name: 'Surtur', health: 50 }, { name: 'Hela', health: 87 } ],
        metadata: { favouriteColor: 'yellow', age: 313 }
      },
      {
        _id: ObjectId("662005262ce7c8fdc90a8c41"),
        name: 'Dr. Strange',
        powers: [ 'magic' ],
        health: 86,
        villains: [ { name: 'Hela', health: 87 }, { name: 'Dormamu', health: 100 } ],
        metadata: { favouriteColor: 'orange', age: 44 }
      }
    ]

so the same thing we are doing here to
this query will look into the array `db.heros.find({villains:{name:"Hela",health:87}})and will find those object whose data matches with

{name:"Hela",health:87}

and will return those data

Q12) find all heroes who have a villain named Hella irrespective of health, I am not worried about health

nxm201repeat> db.heros.find({"villains.name":"Hela"})
[
  {
    _id: ObjectId("662005262ce7c8fdc90a8c40"),
    name: 'Thor',
    powers: [ 'god', 'magic' ],
    health: 57,
    villains: [ { name: 'Surtur', health: 50 }, { name: 'Hela', health: 87 } ],
    metadata: { favouriteColor: 'yellow', age: 313 }
  },
  {
    _id: ObjectId("662005262ce7c8fdc90a8c41"),
    name: 'Dr. Strange',
    powers: [ 'magic' ],
    health: 86,
    villains: [ { name: 'Hela', health: 87 }, { name: 'Dormamu', health: 100 } ],
    metadata: { favouriteColor: 'orange', age: 44 }
  }
]

Q 13. find all the heros who's health is less than 50 and we want only names and health of them.

b25nxm201> db.heros.find({"health":{$lt: 50}},{name: 1, health:1})

[ { id: ObjectId("6481c6044fd1b71717086774"), name: 'IronMan', health: 33 }, { id: ObjectId("6481c6044fd1b71717086775"), name: 'Batman', health: 40 } ]

To Stop getting id use this

b25nxm201> db.heros.find({"health":{$lt: 50}},{name: 1, health:1,_id:0}) [ { name: 'IronMan', health: 33 }, { name: 'Batman', health: 40 } ]

b25nxm201> db.heros.find({"health":{$lt: 50}}) -> Query to find all villians whose health is less than 50

Assignment 1

Checkout the sample database Here

Import this json into your mongodb collection
Write following queries:
Find all the countries in region "Asia"
Find all the countries with currency: "EUR"
Find all the countries whose timezone's gmtOffset is 3600
Find all the countries whose timezone's gmtOffset is 3600 AND timezone name is Central European Time (or CET)
Find All the countries with more than 1 time zone (hint: size of array)
Find All the countries with "Korean" translation (KR)
Work with the data, use rest of the operators to find any other data for practice you want

// sample data, there are lot more data here 
// https://pastebin.com/raw/W1NgY8XQ
[
  {
    "id": 1,
    "name": "Afghanistan",
    "iso3": "AFG",
    "iso2": "AF",
    "numeric_code": "004",
    "phone_code": {},
    "capital": "Kabul",
    "currency": "AFN",
    "currency_name": "Afghan afghani",
    "currency_symbol": "؋",
    "tld": ".af",
    "native": "افغانستان",
    "region": "Asia",
    "subregion": "Southern Asia",
    "timezones": [
      {
        "zoneName": "Asia/Kabul",
        "gmtOffset": 16200,
        "gmtOffsetName": "UTC+04:30",
        "abbreviation": "AFT",
        "tzName": "Afghanistan Time"
      }
    ],
    "translations": {
      "kr": "아프가니스탄",
      "pt-BR": "Afeganistão",
      "pt": "Afeganistão",
      "nl": "Afghanistan",
      "hr": "Afganistan",
      "fa": "افغانستان",
      "de": "Afghanistan",
      "es": "Afganistán",
      "fr": "Afghanistan",
      "ja": "アフガニスタン",
      "it": "Afghanistan",
      "cn": "阿富汗",
      "tr": "Afganistan"
    },
    "latitude": {},
    "longitude": {},
    "emoji": "🇦🇫",
    "emojiU": "U+1F1E6 U+1F1EB"
  },
  {
    "id": 2,
    "name": "Aland Islands",
    "iso3": "ALA",
    "iso2": "AX",
    "numeric_code": {},
    "phone_code": "+358-18",
    "capital": "Mariehamn",
    "currency": "EUR",
    "currency_name": "Euro",
    "currency_symbol": "€",
    "tld": ".ax",
    "native": "Åland",
    "region": "Europe",
    "subregion": "Northern Europe",
    "timezones": [
      {
        "zoneName": "Europe/Mariehamn",
        "gmtOffset": 7200,
        "gmtOffsetName": "UTC+02:00",
        "abbreviation": "EET",
        "tzName": "Eastern European Time"
      }
    ],
    "translations": {
      "kr": "올란드 제도",
      "pt-BR": "Ilhas de Aland",
      "pt": "Ilhas de Aland",
      "nl": "Ålandeilanden",
      "hr": "Ålandski otoci",
      "fa": "جزایر الند",
      "de": "Åland",
      "es": "Alandia",
      "fr": "Åland",
      "ja": "オーランド諸島",
      "it": "Isole Aland",
      "cn": "奥兰群岛",
      "tr": "Åland Adalari"
    },
    "latitude": {},
    "longitude": {},
    "emoji": "🇦🇽",
    "emojiU": "U+1F1E6 U+1F1FD"
  },
  {
    "id": 3,
    "name": "Albania",
    "iso3": "ALB",
    "iso2": "AL",
    "numeric_code": "008",
    "phone_code": {},
    "capital": "Tirana",
    "currency": "ALL",
    "currency_name": "Albanian lek",
    "currency_symbol": "Lek",
    "tld": ".al",
    "native": "Shqipëria",
    "region": "Europe",
    "subregion": "Southern Europe",
    "timezones": [
      {
        "zoneName": "Europe/Tirane",
        "gmtOffset": 3600,
        "gmtOffsetName": "UTC+01:00",
        "abbreviation": "CET",
        "tzName": "Central European Time"
      }
    ],
    "translations": {
      "kr": "알바니아",
      "pt-BR": "Albânia",
      "pt": "Albânia",
      "nl": "Albanië",
      "hr": "Albanija",
      "fa": "آلبانی",
      "de": "Albanien",
      "es": "Albania",
      "fr": "Albanie",
      "ja": "アルバニア",
      "it": "Albania",
      "cn": "阿尔巴尼亚",
      "tr": "Arnavutluk"
    },
    "latitude": {},
    "longitude": {},
    "emoji": "🇦🇱",
    "emojiU": "U+1F1E6 U+1F1F1"
  },
  {
    "id": 4,
    "name": "Algeria",
    "iso3": "DZA",
    "iso2": "DZ",
    "numeric_code": "012",
    "phone_code": {},
    "capital": "Algiers",
    "currency": "DZD",
    "currency_name": "Algerian dinar",
    "currency_symbol": "دج",
    "tld": ".dz",
    "native": "الجزائر",
    "region": "Africa",
    "subregion": "Northern Africa",
    "timezones": [
      {
        "zoneName": "Africa/Algiers",
        "gmtOffset": 3600,
        "gmtOffsetName": "UTC+01:00",
        "abbreviation": "CET",
        "tzName": "Central European Time"
      }
    ],
    "translations": {
      "kr": "알제리",
      "pt-BR": "Argélia",
      "pt": "Argélia",
      "nl": "Algerije",
      "hr": "Alžir",
      "fa": "الجزایر",
      "de": "Algerien",
      "es": "Argelia",
      "fr": "Algérie",
      "ja": "アルジェリア",
      "it": "Algeria",
      "cn": "阿尔及利亚",
      "tr": "Cezayir"
    },
    "latitude": {},
    "longitude": {},
    "emoji": "🇩🇿",
    "emojiU": "U+1F1E9 U+1F1FF"
  }
]

Q1 Find all the countries in region "Asia"

nxm201repeat> db.assignment1.find({"region":"Asia"})

Q2 ) Find all the countries with currency: "EUR"

nxm201repeat> db.assignment1.find({"currency":"EUR"})

Q3) Find all the countries whose timezone's gmtOffset is 3600

Advanced Mongo Queries Class 1

Assignment 1

About the author

Comments

More from this blog

Primary Key vs Secondary key - What do you probably didn't know

database engineering

Design Microservices Architecture with Patterns & Principles

usereducer and useReducer vs useState

React Native Udemy Course Learnings