# Advanced Mongo Queries Class 1

Sample Data

```json
[
{
"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

```json
[ { _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

```json
[
  {
    _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.

<mark>learning number 1</mark>  
<mark>In mongodb </mark> `ORDER MATTERS` <mark> in database</mark>

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.

*<mark>learning number 2</mark>*  
**<mark>so not just orders matter but exact key should also match</mark>**  
<mark>EXACT ORDER MATTERS</mark>

Q4) Question -&gt; Find those heros whose favoriteColor id red

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

response

```json
[
  {
    _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

```bash
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

```bash
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 \]

```bash
 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

```bash
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 }
  }
]
```

*<mark>when the value is an array of 1 string.<br><br>it tries to assume that as a string</mark>*

*<mark>mongo db will assume our query as powers: ["intelligence"] ===&gt; powers:"intelligence"</mark>*

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" ===&gt; powers:\["money","xyz","intelligence"\]

```bash
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](https://www.mongodb.com/docs/manual/tutorial/query-arrays/)

```bash
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

```bash
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:**

* [https://mongodb.com/docs/manual/reference/operator/query/all/](https://mongodb.com/docs/manual/reference/operator/query/all/)
    
* [https://mongodb.com/docs/manual/reference/operator/query/](https://mongodb.com/docs/manual/reference/operator/query/)
    
* [https://mongodb.com/docs/manual/reference/operator/query/and/](https://mongodb.com/docs/manual/reference/operator/query/and/)
    

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

```json
// 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"})`

```bash
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

```bash
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.

```bash
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**](https://pastebin.com/raw/W1NgY8XQ)

* Import this json into your mongod[b co](https://pastebin.com/raw/W1NgY8XQ)llection
    
* Write following queries:
    
* Find [all](https://pastebin.com/raw/W1NgY8XQ) the countries in region ["Asi](https://pastebin.com/raw/W1NgY8XQ)a"
    
* Find all the countries with curre[ncy:](https://pastebin.com/raw/W1NgY8XQ) "EUR"
    
* Find all the countries whose time[zone](https://pastebin.com/raw/W1NgY8XQ)'s gmtOffset is 3600
    
* Find all the countries whose time[zone](https://pastebin.com/raw/W1NgY8XQ)'s gmtOffset is 3600 AND timezone name is Central European Time (or CET)
    
* Find All the countries with more [than](https://pastebin.com/raw/W1NgY8XQ) 1 time zone (hint: size of array)
    
* Find All the countries with "Kore[an"](https://pastebin.com/raw/W1NgY8XQ) translation (KR)
    
* Work with the data, use rest of t[he o](https://pastebin.com/raw/W1NgY8XQ)perators to find any other data for practice you want
    

```bash
// 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](https://pastebin.com/raw/W1NgY8XQ) the countries in region ["Asi](https://pastebin.com/raw/W1NgY8XQ)a"

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

Q2 ) Find all the countries with curre[ncy:](https://pastebin.com/raw/W1NgY8XQ) "EUR"

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

Q3) Find all the countries whose time[zone](https://pastebin.com/raw/W1NgY8XQ)'s gmtOffset is 3600