practice3.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing pymongo library for connecting to the mongod server\n",
    "import pymongo\n",
    "import pandas as pd\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# connecting to the mongodb server installed locally on port number 27017\n",
    "client= pymongo.MongoClient('mongodb://localhost:27017/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating a database\n",
    "db = client.students_details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Collection(Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'students_details'), 'students')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating a collection\n",
    "db['students'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['students']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# lisiting all the collections within the database\n",
    "\n",
    "db.list_collection_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reading the data from csv file into dataframe\n",
    "df=pd.read_csv('./data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>gender</th>\n",
       "      <th>state</th>\n",
       "      <th>phy</th>\n",
       "      <th>maths</th>\n",
       "      <th>bio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rohan</td>\n",
       "      <td>male</td>\n",
       "      <td>mh</td>\n",
       "      <td>60</td>\n",
       "      <td>90</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>aniket</td>\n",
       "      <td>male</td>\n",
       "      <td>up</td>\n",
       "      <td>52</td>\n",
       "      <td>85</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ganesh</td>\n",
       "      <td>male</td>\n",
       "      <td>up</td>\n",
       "      <td>45</td>\n",
       "      <td>82</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>meena</td>\n",
       "      <td>female</td>\n",
       "      <td>mp</td>\n",
       "      <td>72</td>\n",
       "      <td>69</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>seema</td>\n",
       "      <td>female</td>\n",
       "      <td>mh</td>\n",
       "      <td>68</td>\n",
       "      <td>90</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name  gender state  phy  maths  bio\n",
       "0   rohan    male    mh   60     90   68\n",
       "1  aniket    male    up   52     85   72\n",
       "2  ganesh    male    up   45     82   78\n",
       "3   meena  female    mp   72     69   80\n",
       "4   seema  female    mh   68     90   78"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# top 10 rows to check the data \n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# converting the dataframe into json for insertion into the mongodb database\n",
    "list_of_documents=df.to_json(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[{\"name\":\"rohan\",\"gender\":\"male\",\"state\":\"mh\",\"phy\":60,\"maths\":90,\"bio\":68},{\"name\":\"aniket\",\"gender\":\"male\",\"state\":\"up\",\"phy\":52,\"maths\":85,\"bio\":72},{\"name\":\"ganesh\",\"gender\":\"male\",\"state\":\"up\",\"phy\":45,\"maths\":82,\"bio\":78},{\"name\":\"meena\",\"gender\":\"female\",\"state\":\"mp\",\"phy\":72,\"maths\":69,\"bio\":80},{\"name\":\"seema\",\"gender\":\"female\",\"state\":\"mh\",\"phy\":68,\"maths\":90,\"bio\":78},{\"name\":\"dinesh\",\"gender\":\"male\",\"state\":\"mp\",\"phy\":48,\"maths\":34,\"bio\":67},{\"name\":\"yogita\",\"gender\":\"female\",\"state\":\"mp\",\"phy\":39,\"maths\":65,\"bio\":90},{\"name\":\"karan\",\"gender\":\"male\",\"state\":\"mh\",\"phy\":82,\"maths\":78,\"bio\":55},{\"name\":\"jayesh\",\"gender\":\"male\",\"state\":\"up\",\"phy\":87,\"maths\":88,\"bio\":32},{\"name\":\"mangesh\",\"gender\":\"male\",\"state\":\"mp\",\"phy\":91,\"maths\":78,\"bio\":82},{\"name\":\"geeta\",\"gender\":\"female\",\"state\":\"mh\",\"phy\":31,\"maths\":58,\"bio\":78},{\"name\":\"runali\",\"gender\":\"female\",\"state\":\"up\",\"phy\":85,\"maths\":92,\"bio\":66},{\"name\":\"tushar\",\"gender\":\"male\",\"state\":\"up\",\"phy\":32,\"maths\":92,\"bio\":77},{\"name\":\"binod\",\"gender\":\"male\",\"state\":\"mh\",\"phy\":95,\"maths\":78,\"bio\":88},{\"name\":\"sanjay\",\"gender\":\"male\",\"state\":\"mh\",\"phy\":63,\"maths\":90,\"bio\":80}]'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# String of list of documents\n",
    "list_of_documents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'name': 'rohan',\n",
       "  'gender': 'male',\n",
       "  'state': 'mh',\n",
       "  'phy': 60,\n",
       "  'maths': 90,\n",
       "  'bio': 68},\n",
       " {'name': 'aniket',\n",
       "  'gender': 'male',\n",
       "  'state': 'up',\n",
       "  'phy': 52,\n",
       "  'maths': 85,\n",
       "  'bio': 72},\n",
       " {'name': 'ganesh',\n",
       "  'gender': 'male',\n",
       "  'state': 'up',\n",
       "  'phy': 45,\n",
       "  'maths': 82,\n",
       "  'bio': 78},\n",
       " {'name': 'meena',\n",
       "  'gender': 'female',\n",
       "  'state': 'mp',\n",
       "  'phy': 72,\n",
       "  'maths': 69,\n",
       "  'bio': 80},\n",
       " {'name': 'seema',\n",
       "  'gender': 'female',\n",
       "  'state': 'mh',\n",
       "  'phy': 68,\n",
       "  'maths': 90,\n",
       "  'bio': 78},\n",
       " {'name': 'dinesh',\n",
       "  'gender': 'male',\n",
       "  'state': 'mp',\n",
       "  'phy': 48,\n",
       "  'maths': 34,\n",
       "  'bio': 67},\n",
       " {'name': 'yogita',\n",
       "  'gender': 'female',\n",
       "  'state': 'mp',\n",
       "  'phy': 39,\n",
       "  'maths': 65,\n",
       "  'bio': 90},\n",
       " {'name': 'karan',\n",
       "  'gender': 'male',\n",
       "  'state': 'mh',\n",
       "  'phy': 82,\n",
       "  'maths': 78,\n",
       "  'bio': 55},\n",
       " {'name': 'jayesh',\n",
       "  'gender': 'male',\n",
       "  'state': 'up',\n",
       "  'phy': 87,\n",
       "  'maths': 88,\n",
       "  'bio': 32},\n",
       " {'name': 'mangesh',\n",
       "  'gender': 'male',\n",
       "  'state': 'mp',\n",
       "  'phy': 91,\n",
       "  'maths': 78,\n",
       "  'bio': 82},\n",
       " {'name': 'geeta',\n",
       "  'gender': 'female',\n",
       "  'state': 'mh',\n",
       "  'phy': 31,\n",
       "  'maths': 58,\n",
       "  'bio': 78},\n",
       " {'name': 'runali',\n",
       "  'gender': 'female',\n",
       "  'state': 'up',\n",
       "  'phy': 85,\n",
       "  'maths': 92,\n",
       "  'bio': 66},\n",
       " {'name': 'tushar',\n",
       "  'gender': 'male',\n",
       "  'state': 'up',\n",
       "  'phy': 32,\n",
       "  'maths': 92,\n",
       "  'bio': 77},\n",
       " {'name': 'binod',\n",
       "  'gender': 'male',\n",
       "  'state': 'mh',\n",
       "  'phy': 95,\n",
       "  'maths': 78,\n",
       "  'bio': 88},\n",
       " {'name': 'sanjay',\n",
       "  'gender': 'male',\n",
       "  'state': 'mh',\n",
       "  'phy': 63,\n",
       "  'maths': 90,\n",
       "  'bio': 80}]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# converting string of dictionary into dictionary\n",
    "\n",
    "res = json.loads(list_of_documents)\n",
    "res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Inserting data into the mongodb\n",
    "for i in range(len(res)):\n",
    "    db.students.insert_one(res[i])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecting all the documents from the collection\n",
    "result=db.students.find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9c7fa079557446b6945b'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647d9ca2a079557446b6945c'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9cb6a079557446b6945d'), 'name': 'naman'}\n",
      "{'_id': ObjectId('647d9cb9a079557446b6945e'), 'name': 'preeti', 'gender': 'female'}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f312f'), 'name': 'rohan', 'gender': 'male', 'state': 'mh', 'phy': 60, 'maths': 90, 'bio': 68}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3130'), 'name': 'aniket', 'gender': 'male', 'state': 'up', 'phy': 52, 'maths': 85, 'bio': 72}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3131'), 'name': 'ganesh', 'gender': 'male', 'state': 'up', 'phy': 45, 'maths': 82, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3132'), 'name': 'meena', 'gender': 'female', 'state': 'mp', 'phy': 72, 'maths': 69, 'bio': 80}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3133'), 'name': 'seema', 'gender': 'female', 'state': 'mh', 'phy': 68, 'maths': 90, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3134'), 'name': 'dinesh', 'gender': 'male', 'state': 'mp', 'phy': 48, 'maths': 34, 'bio': 67}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3135'), 'name': 'yogita', 'gender': 'female', 'state': 'mp', 'phy': 39, 'maths': 65, 'bio': 90}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3136'), 'name': 'karan', 'gender': 'male', 'state': 'mh', 'phy': 82, 'maths': 78, 'bio': 55}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3137'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3138'), 'name': 'mangesh', 'gender': 'male', 'state': 'mp', 'phy': 91, 'maths': 78, 'bio': 82}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3139'), 'name': 'geeta', 'gender': 'female', 'state': 'mh', 'phy': 31, 'maths': 58, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313a'), 'name': 'runali', 'gender': 'female', 'state': 'up', 'phy': 85, 'maths': 92, 'bio': 66}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313b'), 'name': 'tushar', 'gender': 'male', 'state': 'up', 'phy': 32, 'maths': 92, 'bio': 77}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313c'), 'name': 'binod', 'gender': 'male', 'state': 'mh', 'phy': 95, 'maths': 78, 'bio': 88}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313d'), 'name': 'sanjay', 'gender': 'male', 'state': 'mh', 'phy': 63, 'maths': 90, 'bio': 80}\n"
     ]
    }
   ],
   "source": [
    "# Dsiplaying all the records\n",
    "for i in result:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'name': 'students',\n",
       "  'type': 'collection',\n",
       "  'options': {},\n",
       "  'info': {'readOnly': False,\n",
       "   'uuid': Binary(b'\\xe71\\xe2wr\\xa8G\\x01\\xa9\\r\\xca\\x19\\x90\\x88QD', 4)},\n",
       "  'idIndex': {'v': 2,\n",
       "   'key': {'_id': 1},\n",
       "   'name': '_id_',\n",
       "   'ns': 'students_details.students'}}]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.list_collections())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Searching/Filtering Documents on specific fields \n",
    "# Filtering by name\n",
    "\n",
    "result = db.students.find_one({'name':'sanjay'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('647d9d159917a7edc19f313d'),\n",
       " 'name': 'sanjay',\n",
       " 'gender': 'male',\n",
       " 'state': 'mh',\n",
       " 'phy': 63,\n",
       " 'maths': 90,\n",
       " 'bio': 80}"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('647d9c7fa079557446b6945b'),\n",
       " 'name': 'sanjay',\n",
       " 'gender': 'male',\n",
       " 'state': 'mp',\n",
       " 'phy': 34,\n",
       " 'maths': 56,\n",
       " 'bio': 89}"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filtering by gender\n",
    "res = db.students.find_one({'gender':'male'})\n",
    "res\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Filtering but with find\n",
    "# It returns a cursor, which points to all the records and an iterator can be used to iterate over all the documents returned by the filteration\n",
    "res = db.students.find({'gender':'female'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'_id': ObjectId('647d9cb9a079557446b6945e'),\n",
       "  'name': 'preeti',\n",
       "  'gender': 'female'},\n",
       " {'_id': ObjectId('647d9d159917a7edc19f3132'),\n",
       "  'name': 'meena',\n",
       "  'gender': 'female',\n",
       "  'state': 'mp',\n",
       "  'phy': 72,\n",
       "  'maths': 69,\n",
       "  'bio': 80},\n",
       " {'_id': ObjectId('647d9d159917a7edc19f3133'),\n",
       "  'name': 'seema',\n",
       "  'gender': 'female',\n",
       "  'state': 'mh',\n",
       "  'phy': 68,\n",
       "  'maths': 90,\n",
       "  'bio': 78},\n",
       " {'_id': ObjectId('647d9d159917a7edc19f3135'),\n",
       "  'name': 'yogita',\n",
       "  'gender': 'female',\n",
       "  'state': 'mp',\n",
       "  'phy': 39,\n",
       "  'maths': 65,\n",
       "  'bio': 90},\n",
       " {'_id': ObjectId('647d9d159917a7edc19f3139'),\n",
       "  'name': 'geeta',\n",
       "  'gender': 'female',\n",
       "  'state': 'mh',\n",
       "  'phy': 31,\n",
       "  'maths': 58,\n",
       "  'bio': 78},\n",
       " {'_id': ObjectId('647d9d159917a7edc19f313a'),\n",
       "  'name': 'runali',\n",
       "  'gender': 'female',\n",
       "  'state': 'up',\n",
       "  'phy': 85,\n",
       "  'maths': 92,\n",
       "  'bio': 66}]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter on 2/more fields\n",
    "res = db.students.find_one({'name':'sanjay','gender':'male'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('647d9d159917a7edc19f313d'),\n",
       " 'name': 'sanjay',\n",
       " 'gender': 'male',\n",
       " 'state': 'mh',\n",
       " 'phy': 63,\n",
       " 'maths': 90,\n",
       " 'bio': 80}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter on 2/more fields\n",
    "res = db.students.find({'name':'sanjay','gender':'male'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d159917a7edc19f313d'), 'name': 'sanjay', 'gender': 'male', 'state': 'mh', 'phy': 63, 'maths': 90, 'bio': 80}\n",
      "{'_id': ObjectId('647d9c7fa079557446b6945b'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# limiting the number of documents \n",
    "res = db.students.find({'state':'mp'}).limit(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9c7fa079557446b6945b'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3132'), 'name': 'meena', 'gender': 'female', 'state': 'mp', 'phy': 72, 'maths': 69, 'bio': 80}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3134'), 'name': 'dinesh', 'gender': 'male', 'state': 'mp', 'phy': 48, 'maths': 34, 'bio': 67}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Projection of objectID\n",
    "res=db.students.find_one({'name':'sanjay'},{'_id'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('647d9d159917a7edc19f313d')}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Projection of name and state\n",
    "\n",
    "res = db.students.find({},{'_id':0,'name':1,'state':1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'name': 'sanjay', 'state': 'mp'}\n",
      "{'name': 'jayesh', 'state': 'up'}\n",
      "{'name': 'naman'}\n",
      "{'name': 'preeti'}\n",
      "{'name': 'rohan', 'state': 'mh'}\n",
      "{'name': 'aniket', 'state': 'up'}\n",
      "{'name': 'ganesh', 'state': 'up'}\n",
      "{'name': 'meena', 'state': 'mp'}\n",
      "{'name': 'seema', 'state': 'mh'}\n",
      "{'name': 'dinesh', 'state': 'mp'}\n",
      "{'name': 'yogita', 'state': 'mp'}\n",
      "{'name': 'karan', 'state': 'mh'}\n",
      "{'name': 'jayesh', 'state': 'up'}\n",
      "{'name': 'mangesh', 'state': 'mp'}\n",
      "{'name': 'geeta', 'state': 'mh'}\n",
      "{'name': 'runali', 'state': 'up'}\n",
      "{'name': 'tushar', 'state': 'up'}\n",
      "{'name': 'binod', 'state': 'mh'}\n",
      "{'name': 'sanjay', 'state': 'mh'}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filtering on comparision \n",
    "# Greater than $gt operator\n",
    "res = db.students.find({'phy':{'$gt':85}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9ca2a079557446b6945c'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3137'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3138'), 'name': 'mangesh', 'gender': 'male', 'state': 'mp', 'phy': 91, 'maths': 78, 'bio': 82}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313c'), 'name': 'binod', 'gender': 'male', 'state': 'mh', 'phy': 95, 'maths': 78, 'bio': 88}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# less than $lt and projection\n",
    "\n",
    "res = db.students.find({'maths':{'$lt':60}},{'name':1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9c7fa079557446b6945b'), 'name': 'sanjay'}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3134'), 'name': 'dinesh'}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3139'), 'name': 'geeta'}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# greater than equal to \n",
    "res = db.students.find({'bio':{'$gte':85}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9c7fa079557446b6945b'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3135'), 'name': 'yogita', 'gender': 'female', 'state': 'mp', 'phy': 39, 'maths': 65, 'bio': 90}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313c'), 'name': 'binod', 'gender': 'male', 'state': 'mh', 'phy': 95, 'maths': 78, 'bio': 88}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# lesser than equal to $lte operator\n",
    "\n",
    "res = db.students.find({'maths':{'$lte':39}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d159917a7edc19f3134'), 'name': 'dinesh', 'gender': 'male', 'state': 'mp', 'phy': 48, 'maths': 34, 'bio': 67}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# equal operator $eq\n",
    "\n",
    "res = db.students.find({'phy':{'$eq':39}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d159917a7edc19f3135'), 'name': 'yogita', 'gender': 'female', 'state': 'mp', 'phy': 39, 'maths': 65, 'bio': 90}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# not equal to $neg\n",
    "res = db.students.find({'name':{'$ne':'jayesh'}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d159917a7edc19f3130'), 'name': 'aniket', 'gender': 'male', 'state': 'up', 'phy': 52, 'maths': 85, 'bio': 72}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313c'), 'name': 'binod', 'gender': 'male', 'state': 'mh', 'phy': 95, 'maths': 78, 'bio': 88}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3134'), 'name': 'dinesh', 'gender': 'male', 'state': 'mp', 'phy': 48, 'maths': 34, 'bio': 67}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3131'), 'name': 'ganesh', 'gender': 'male', 'state': 'up', 'phy': 45, 'maths': 82, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3139'), 'name': 'geeta', 'gender': 'female', 'state': 'mh', 'phy': 31, 'maths': 58, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3136'), 'name': 'karan', 'gender': 'male', 'state': 'mh', 'phy': 82, 'maths': 78, 'bio': 55}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3138'), 'name': 'mangesh', 'gender': 'male', 'state': 'mp', 'phy': 91, 'maths': 78, 'bio': 82}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3132'), 'name': 'meena', 'gender': 'female', 'state': 'mp', 'phy': 72, 'maths': 69, 'bio': 80}\n",
      "{'_id': ObjectId('647d9cb6a079557446b6945d'), 'name': 'naman'}\n",
      "{'_id': ObjectId('647d9cb9a079557446b6945e'), 'name': 'preeti', 'gender': 'female'}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f312f'), 'name': 'rohan', 'gender': 'male', 'state': 'mh', 'phy': 60, 'maths': 90, 'bio': 68}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313a'), 'name': 'runali', 'gender': 'female', 'state': 'up', 'phy': 85, 'maths': 92, 'bio': 66}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313d'), 'name': 'sanjay', 'gender': 'male', 'state': 'mh', 'phy': 63, 'maths': 90, 'bio': 80}\n",
      "{'_id': ObjectId('647d9c7fa079557446b6945b'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3133'), 'name': 'seema', 'gender': 'female', 'state': 'mh', 'phy': 68, 'maths': 90, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313b'), 'name': 'tushar', 'gender': 'male', 'state': 'up', 'phy': 32, 'maths': 92, 'bio': 77}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3135'), 'name': 'yogita', 'gender': 'female', 'state': 'mp', 'phy': 39, 'maths': 65, 'bio': 90}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# comparision on more than 1 field\n",
    "\n",
    "rse = db.students.find(\n",
    "    {'phy':{'$lte':96}, \n",
    "    'maths':{'$gte':69}}\n",
    "    \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9ca2a079557446b6945c'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f312f'), 'name': 'rohan', 'gender': 'male', 'state': 'mh', 'phy': 60, 'maths': 90, 'bio': 68}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3130'), 'name': 'aniket', 'gender': 'male', 'state': 'up', 'phy': 52, 'maths': 85, 'bio': 72}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3131'), 'name': 'ganesh', 'gender': 'male', 'state': 'up', 'phy': 45, 'maths': 82, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3132'), 'name': 'meena', 'gender': 'female', 'state': 'mp', 'phy': 72, 'maths': 69, 'bio': 80}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3133'), 'name': 'seema', 'gender': 'female', 'state': 'mh', 'phy': 68, 'maths': 90, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3136'), 'name': 'karan', 'gender': 'male', 'state': 'mh', 'phy': 82, 'maths': 78, 'bio': 55}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3137'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3138'), 'name': 'mangesh', 'gender': 'male', 'state': 'mp', 'phy': 91, 'maths': 78, 'bio': 82}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313a'), 'name': 'runali', 'gender': 'female', 'state': 'up', 'phy': 85, 'maths': 92, 'bio': 66}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313b'), 'name': 'tushar', 'gender': 'male', 'state': 'up', 'phy': 32, 'maths': 92, 'bio': 77}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313c'), 'name': 'binod', 'gender': 'male', 'state': 'mh', 'phy': 95, 'maths': 78, 'bio': 88}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313d'), 'name': 'sanjay', 'gender': 'male', 'state': 'mh', 'phy': 63, 'maths': 90, 'bio': 80}\n"
     ]
    }
   ],
   "source": [
    "for i in rse:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sorting by fields of documents\n",
    "\n",
    "res=db.students.find().sort('maths',-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d159917a7edc19f313a'), 'name': 'runali', 'gender': 'female', 'state': 'up', 'phy': 85, 'maths': 92, 'bio': 66}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313b'), 'name': 'tushar', 'gender': 'male', 'state': 'up', 'phy': 32, 'maths': 92, 'bio': 77}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f312f'), 'name': 'rohan', 'gender': 'male', 'state': 'mh', 'phy': 60, 'maths': 90, 'bio': 68}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3133'), 'name': 'seema', 'gender': 'female', 'state': 'mh', 'phy': 68, 'maths': 90, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313d'), 'name': 'sanjay', 'gender': 'male', 'state': 'mh', 'phy': 63, 'maths': 90, 'bio': 80}\n",
      "{'_id': ObjectId('647d9ca2a079557446b6945c'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3137'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3130'), 'name': 'aniket', 'gender': 'male', 'state': 'up', 'phy': 52, 'maths': 85, 'bio': 72}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3131'), 'name': 'ganesh', 'gender': 'male', 'state': 'up', 'phy': 45, 'maths': 82, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3136'), 'name': 'karan', 'gender': 'male', 'state': 'mh', 'phy': 82, 'maths': 78, 'bio': 55}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3138'), 'name': 'mangesh', 'gender': 'male', 'state': 'mp', 'phy': 91, 'maths': 78, 'bio': 82}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313c'), 'name': 'binod', 'gender': 'male', 'state': 'mh', 'phy': 95, 'maths': 78, 'bio': 88}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3132'), 'name': 'meena', 'gender': 'female', 'state': 'mp', 'phy': 72, 'maths': 69, 'bio': 80}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3135'), 'name': 'yogita', 'gender': 'female', 'state': 'mp', 'phy': 39, 'maths': 65, 'bio': 90}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3139'), 'name': 'geeta', 'gender': 'female', 'state': 'mh', 'phy': 31, 'maths': 58, 'bio': 78}\n",
      "{'_id': ObjectId('647d9c7fa079557446b6945b'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3134'), 'name': 'dinesh', 'gender': 'male', 'state': 'mp', 'phy': 48, 'maths': 34, 'bio': 67}\n",
      "{'_id': ObjectId('647d9cb6a079557446b6945d'), 'name': 'naman'}\n",
      "{'_id': ObjectId('647d9cb9a079557446b6945e'), 'name': 'preeti', 'gender': 'female'}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sorting by multiple fields\n",
    "\n",
    "res = db.students.find({}).sort([('maths',1),('bio',-1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9cb6a079557446b6945d'), 'name': 'naman'}\n",
      "{'_id': ObjectId('647d9cb9a079557446b6945e'), 'name': 'preeti', 'gender': 'female'}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3134'), 'name': 'dinesh', 'gender': 'male', 'state': 'mp', 'phy': 48, 'maths': 34, 'bio': 67}\n",
      "{'_id': ObjectId('647d9c7fa079557446b6945b'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3139'), 'name': 'geeta', 'gender': 'female', 'state': 'mh', 'phy': 31, 'maths': 58, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3135'), 'name': 'yogita', 'gender': 'female', 'state': 'mp', 'phy': 39, 'maths': 65, 'bio': 90}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3132'), 'name': 'meena', 'gender': 'female', 'state': 'mp', 'phy': 72, 'maths': 69, 'bio': 80}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313c'), 'name': 'binod', 'gender': 'male', 'state': 'mh', 'phy': 95, 'maths': 78, 'bio': 88}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3138'), 'name': 'mangesh', 'gender': 'male', 'state': 'mp', 'phy': 91, 'maths': 78, 'bio': 82}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3136'), 'name': 'karan', 'gender': 'male', 'state': 'mh', 'phy': 82, 'maths': 78, 'bio': 55}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3131'), 'name': 'ganesh', 'gender': 'male', 'state': 'up', 'phy': 45, 'maths': 82, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3130'), 'name': 'aniket', 'gender': 'male', 'state': 'up', 'phy': 52, 'maths': 85, 'bio': 72}\n",
      "{'_id': ObjectId('647d9ca2a079557446b6945c'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3137'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313d'), 'name': 'sanjay', 'gender': 'male', 'state': 'mh', 'phy': 63, 'maths': 90, 'bio': 80}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3133'), 'name': 'seema', 'gender': 'female', 'state': 'mh', 'phy': 68, 'maths': 90, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f312f'), 'name': 'rohan', 'gender': 'male', 'state': 'mh', 'phy': 60, 'maths': 90, 'bio': 68}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313b'), 'name': 'tushar', 'gender': 'male', 'state': 'up', 'phy': 32, 'maths': 92, 'bio': 77}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313a'), 'name': 'runali', 'gender': 'female', 'state': 'up', 'phy': 85, 'maths': 92, 'bio': 66}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# delete the documents\n",
    "# one document\n",
    "\n",
    "x=db.students.delete_one({'name':'sanjay'})\n",
    "x.deleted_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "res=db.students.find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9c7fa079557446b6945b'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647d9ca2a079557446b6945c'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9cb6a079557446b6945d'), 'name': 'naman'}\n",
      "{'_id': ObjectId('647d9cb9a079557446b6945e'), 'name': 'preeti', 'gender': 'female'}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f312f'), 'name': 'rohan', 'gender': 'male', 'state': 'mh', 'phy': 60, 'maths': 90, 'bio': 68}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3130'), 'name': 'aniket', 'gender': 'male', 'state': 'up', 'phy': 52, 'maths': 85, 'bio': 72}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3131'), 'name': 'ganesh', 'gender': 'male', 'state': 'up', 'phy': 45, 'maths': 82, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3132'), 'name': 'meena', 'gender': 'female', 'state': 'mp', 'phy': 72, 'maths': 69, 'bio': 80}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3133'), 'name': 'seema', 'gender': 'female', 'state': 'mh', 'phy': 68, 'maths': 90, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3134'), 'name': 'dinesh', 'gender': 'male', 'state': 'mp', 'phy': 48, 'maths': 34, 'bio': 67}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3135'), 'name': 'yogita', 'gender': 'female', 'state': 'mp', 'phy': 39, 'maths': 65, 'bio': 90}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3136'), 'name': 'karan', 'gender': 'male', 'state': 'mh', 'phy': 82, 'maths': 78, 'bio': 55}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3137'), 'name': 'jayesh', 'gender': 'male', 'state': 'up', 'phy': 87, 'maths': 88, 'bio': 32}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3138'), 'name': 'mangesh', 'gender': 'male', 'state': 'mp', 'phy': 91, 'maths': 78, 'bio': 82}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f3139'), 'name': 'geeta', 'gender': 'female', 'state': 'mh', 'phy': 31, 'maths': 58, 'bio': 78}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313a'), 'name': 'runali', 'gender': 'female', 'state': 'up', 'phy': 85, 'maths': 92, 'bio': 66}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313b'), 'name': 'tushar', 'gender': 'male', 'state': 'up', 'phy': 32, 'maths': 92, 'bio': 77}\n",
      "{'_id': ObjectId('647d9d159917a7edc19f313c'), 'name': 'binod', 'gender': 'male', 'state': 'mh', 'phy': 95, 'maths': 78, 'bio': 88}\n"
     ]
    }
   ],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.DeleteResult at 0x7f9768af1c70>"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# deleting all the documents within the collection\n",
    "db.students.delete_many({})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# no documents left in the connection\n",
    "db.students.find_one()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.UpdateResult at 0x7f9768af1ac0>"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# updating the records\n",
    "query={'name':'Tejal'}\n",
    "\n",
    "db.students.update_one(query,{'$set':{'maths':'89'}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "res=db.students.find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.UpdateResult at 0x7f9768b0aeb0>"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# update can be done on single and many documents in same query\n",
    "\n",
    "# one update\n",
    "# filtering by multiplle fields\n",
    "db.students.update_one({'state':'up','phy':32},{'$set':{'maths':76, 'bio':90}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = db.students.find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in res:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'executionSuccess': True,\n",
       " 'nReturned': 0,\n",
       " 'executionTimeMillis': 0,\n",
       " 'totalKeysExamined': 0,\n",
       " 'totalDocsExamined': 0,\n",
       " 'executionStages': {'stage': 'FETCH',\n",
       "  'nReturned': 0,\n",
       "  'executionTimeMillisEstimate': 0,\n",
       "  'works': 1,\n",
       "  'advanced': 0,\n",
       "  'needTime': 0,\n",
       "  'needYield': 0,\n",
       "  'saveState': 0,\n",
       "  'restoreState': 0,\n",
       "  'isEOF': 1,\n",
       "  'invalidates': 0,\n",
       "  'docsExamined': 0,\n",
       "  'alreadyHasObj': 0,\n",
       "  'inputStage': {'stage': 'IXSCAN',\n",
       "   'nReturned': 0,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 1,\n",
       "   'advanced': 0,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'keyPattern': {'name': 1, 'state': 1},\n",
       "   'indexName': 'name_1_state_1',\n",
       "   'isMultiKey': False,\n",
       "   'multiKeyPaths': {'name': [], 'state': []},\n",
       "   'isUnique': False,\n",
       "   'isSparse': False,\n",
       "   'isPartial': False,\n",
       "   'indexVersion': 2,\n",
       "   'direction': 'forward',\n",
       "   'indexBounds': {'name': ['[\"Tejal\", \"Tejal\"]'],\n",
       "    'state': ['[MinKey, MaxKey]']},\n",
       "   'keysExamined': 0,\n",
       "   'seeks': 1,\n",
       "   'dupsTested': 0,\n",
       "   'dupsDropped': 0,\n",
       "   'seenInvalidated': 0}},\n",
       " 'allPlansExecution': []}"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Gives the execution planning for the query\n",
    "db.students.find({'name':'Tejal'}).explain()['executionStats']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# list all the indexes with table they are associate to in dict format with key being the index_name\n",
    "\n",
    "# _id is  by-default index created by mongodb to uniquely identify the documents in the collection\n",
    "i=db.students.index_information()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "_id_\n",
      "name_1_state_1\n",
      "maths_1\n"
     ]
    }
   ],
   "source": [
    "for i in i:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'maths_-1'"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating an index\n",
    "# Single field index\n",
    "# helps in reading the documents faster by not reading the unecessary documents\n",
    "db.students.create_index([('maths',-1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'name': {'$eq': 'Tejal'}},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"Tejal\", \"Tejal\"]'],\n",
       "     'state': ['[MinKey, MaxKey]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 0,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 0,\n",
       "  'totalDocsExamined': 0,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'nReturned': 0,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 1,\n",
       "   'advanced': 0,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 0,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 0,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 1,\n",
       "    'advanced': 0,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"Tejal\", \"Tejal\"]'],\n",
       "     'state': ['[MinKey, MaxKey]']},\n",
       "    'keysExamined': 0,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 0,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# explain\n",
    "db.students.find({'name':'Tejal'}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.students.drop_index('maths_-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id_': {'v': 2, 'key': [('_id', 1)], 'ns': 'students_details.students'},\n",
       " 'name_1_state_1': {'v': 2,\n",
       "  'key': [('name', 1), ('state', 1)],\n",
       "  'ns': 'students_details.students'},\n",
       " 'maths_1': {'v': 2,\n",
       "  'key': [('maths', 1)],\n",
       "  'ns': 'students_details.students',\n",
       "  'partialFilterExpression': SON([('maths', SON([('$gt', 70)]))])}}"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.index_information()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'maths_desc_indx'"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating an index with giving a custom name to it \n",
    "db.students.create_index([('maths',-1)],name='maths_desc_indx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id_': {'v': 2, 'key': [('_id', 1)], 'ns': 'students_details.students'},\n",
       " 'name_1_state_1': {'v': 2,\n",
       "  'key': [('name', 1), ('state', 1)],\n",
       "  'ns': 'students_details.students'},\n",
       " 'maths_1': {'v': 2,\n",
       "  'key': [('maths', 1)],\n",
       "  'ns': 'students_details.students',\n",
       "  'partialFilterExpression': SON([('maths', SON([('$gt', 70)]))])},\n",
       " 'maths_desc_indx': {'v': 2,\n",
       "  'key': [('maths', -1)],\n",
       "  'ns': 'students_details.students'}}"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.index_information()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.students.drop_index('maths_desc_indx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'name_1_state_1'"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# compound indexes- index created on multiple columns\n",
    "\n",
    "db.students.create_index([('name',1),('state',1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'$and': [{'name': {'$eq': 'sanjay'}},\n",
       "    {'state': {'$eq': 'mh'}}]},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "     'state': ['[\"mh\", \"mh\"]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 0,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 0,\n",
       "  'totalDocsExamined': 0,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'nReturned': 0,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 1,\n",
       "   'advanced': 0,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 0,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 0,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 1,\n",
       "    'advanced': 0,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "     'state': ['[\"mh\", \"mh\"]']},\n",
       "    'keysExamined': 0,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 0,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Both index columns are present hence indexing scan will be done\n",
    "# Index scan -- faster read\n",
    "db.students.find({'name':'sanjay','state':'mh'}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'name': {'$eq': 'sanjay'}},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "     'state': ['[MinKey, MaxKey]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 0,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 0,\n",
       "  'totalDocsExamined': 0,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'nReturned': 0,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 1,\n",
       "   'advanced': 0,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 0,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 0,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 1,\n",
       "    'advanced': 0,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "     'state': ['[MinKey, MaxKey]']},\n",
       "    'keysExamined': 0,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 0,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Firstmost column of the compound index will still result in the index scan\n",
    "# Index scan -- faster read\n",
    "db.students.find({'name':'sanjay'}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'state': {'$eq': 'mh'}},\n",
       "  'winningPlan': {'stage': 'COLLSCAN',\n",
       "   'filter': {'state': {'$eq': 'mh'}},\n",
       "   'direction': 'forward'},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 0,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 0,\n",
       "  'totalDocsExamined': 0,\n",
       "  'executionStages': {'stage': 'COLLSCAN',\n",
       "   'filter': {'state': {'$eq': 'mh'}},\n",
       "   'nReturned': 0,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 0,\n",
       "   'needTime': 1,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'direction': 'forward',\n",
       "   'docsExamined': 0},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# since only one column is used and that too the last one so no index scan is possible\n",
    "# Column scan -- means all documents will be loaded in memory\n",
    "db.students.find({'state':'mh'}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'maths_1'"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Partial filters\n",
    "# index created only on values greater than 70\n",
    "db.students.create_index([('maths',1)],partialFilterExpression={'maths':{'$gt':70}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "i0=db.students.find({'maths':88})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in i0:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'maths': {'$eq': 88}},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'maths': 1},\n",
       "    'indexName': 'maths_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'maths': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': True,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'maths': ['[88, 88]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 0,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 0,\n",
       "  'totalDocsExamined': 0,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'nReturned': 0,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 1,\n",
       "   'advanced': 0,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 0,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 0,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 1,\n",
       "    'advanced': 0,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'maths': 1},\n",
       "    'indexName': 'maths_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'maths': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': True,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'maths': ['[88, 88]']},\n",
       "    'keysExamined': 0,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 0,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# value greater than 70 hence index scan will be done\n",
    "db.students.find({'maths':88}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'maths': {'$eq': 65}},\n",
       "  'winningPlan': {'stage': 'COLLSCAN',\n",
       "   'filter': {'maths': {'$eq': 65}},\n",
       "   'direction': 'forward'},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 0,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 0,\n",
       "  'totalDocsExamined': 0,\n",
       "  'executionStages': {'stage': 'COLLSCAN',\n",
       "   'filter': {'maths': {'$eq': 65}},\n",
       "   'nReturned': 0,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 0,\n",
       "   'needTime': 1,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'direction': 'forward',\n",
       "   'docsExamined': 0},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Since value is lower than 70, than column scan will be done\n",
    "db.students.find({'maths':65}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'name_1'"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# indexing on the value which may not be present in all the documents\n",
    "\n",
    "db.students.create_index([('name',1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.students.drop_index('name_1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id_': {'v': 2, 'key': [('_id', 1)], 'ns': 'students_details.students'},\n",
       " 'name_1_state_1': {'v': 2,\n",
       "  'key': [('name', 1), ('state', 1)],\n",
       "  'ns': 'students_details.students'},\n",
       " 'maths_1': {'v': 2,\n",
       "  'key': [('maths', 1)],\n",
       "  'ns': 'students_details.students',\n",
       "  'partialFilterExpression': SON([('maths', SON([('$gt', 70)]))])}}"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.index_information()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'name_1'"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Unique Index -- Wont allow duplicate values on the indexed field\n",
    "db.students.create_index([('name',1)], unique=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'name': {'$eq': 'sanjay'}},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'name': 1},\n",
       "    'indexName': 'name_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': []},\n",
       "    'isUnique': True,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]']}}},\n",
       "  'rejectedPlans': [{'stage': 'FETCH',\n",
       "    'inputStage': {'stage': 'IXSCAN',\n",
       "     'keyPattern': {'name': 1, 'state': 1},\n",
       "     'indexName': 'name_1_state_1',\n",
       "     'isMultiKey': False,\n",
       "     'multiKeyPaths': {'name': [], 'state': []},\n",
       "     'isUnique': False,\n",
       "     'isSparse': False,\n",
       "     'isPartial': False,\n",
       "     'indexVersion': 2,\n",
       "     'direction': 'forward',\n",
       "     'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "      'state': ['[MinKey, MaxKey]']}}}]},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 0,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 0,\n",
       "  'totalDocsExamined': 0,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'nReturned': 0,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 0,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 0,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 0,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 1,\n",
       "    'advanced': 0,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'name': 1},\n",
       "    'indexName': 'name_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': []},\n",
       "    'isUnique': True,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]']},\n",
       "    'keysExamined': 0,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 0,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': [{'nReturned': 0,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'totalKeysExamined': 0,\n",
       "    'totalDocsExamined': 0,\n",
       "    'executionStages': {'stage': 'FETCH',\n",
       "     'nReturned': 0,\n",
       "     'executionTimeMillisEstimate': 0,\n",
       "     'works': 1,\n",
       "     'advanced': 0,\n",
       "     'needTime': 0,\n",
       "     'needYield': 0,\n",
       "     'saveState': 0,\n",
       "     'restoreState': 0,\n",
       "     'isEOF': 1,\n",
       "     'invalidates': 0,\n",
       "     'docsExamined': 0,\n",
       "     'alreadyHasObj': 0,\n",
       "     'inputStage': {'stage': 'IXSCAN',\n",
       "      'nReturned': 0,\n",
       "      'executionTimeMillisEstimate': 0,\n",
       "      'works': 1,\n",
       "      'advanced': 0,\n",
       "      'needTime': 0,\n",
       "      'needYield': 0,\n",
       "      'saveState': 0,\n",
       "      'restoreState': 0,\n",
       "      'isEOF': 1,\n",
       "      'invalidates': 0,\n",
       "      'keyPattern': {'name': 1, 'state': 1},\n",
       "      'indexName': 'name_1_state_1',\n",
       "      'isMultiKey': False,\n",
       "      'multiKeyPaths': {'name': [], 'state': []},\n",
       "      'isUnique': False,\n",
       "      'isSparse': False,\n",
       "      'isPartial': False,\n",
       "      'indexVersion': 2,\n",
       "      'direction': 'forward',\n",
       "      'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "       'state': ['[MinKey, MaxKey]']},\n",
       "      'keysExamined': 0,\n",
       "      'seeks': 1,\n",
       "      'dupsTested': 0,\n",
       "      'dupsDropped': 0,\n",
       "      'seenInvalidated': 0}}},\n",
       "   {'nReturned': 0,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'totalKeysExamined': 0,\n",
       "    'totalDocsExamined': 0,\n",
       "    'executionStages': {'stage': 'FETCH',\n",
       "     'nReturned': 0,\n",
       "     'executionTimeMillisEstimate': 0,\n",
       "     'works': 1,\n",
       "     'advanced': 0,\n",
       "     'needTime': 0,\n",
       "     'needYield': 0,\n",
       "     'saveState': 0,\n",
       "     'restoreState': 0,\n",
       "     'isEOF': 1,\n",
       "     'invalidates': 0,\n",
       "     'docsExamined': 0,\n",
       "     'alreadyHasObj': 0,\n",
       "     'inputStage': {'stage': 'IXSCAN',\n",
       "      'nReturned': 0,\n",
       "      'executionTimeMillisEstimate': 0,\n",
       "      'works': 1,\n",
       "      'advanced': 0,\n",
       "      'needTime': 0,\n",
       "      'needYield': 0,\n",
       "      'saveState': 0,\n",
       "      'restoreState': 0,\n",
       "      'isEOF': 1,\n",
       "      'invalidates': 0,\n",
       "      'keyPattern': {'name': 1},\n",
       "      'indexName': 'name_1',\n",
       "      'isMultiKey': False,\n",
       "      'multiKeyPaths': {'name': []},\n",
       "      'isUnique': True,\n",
       "      'isSparse': False,\n",
       "      'isPartial': False,\n",
       "      'indexVersion': 2,\n",
       "      'direction': 'forward',\n",
       "      'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]']},\n",
       "      'keysExamined': 0,\n",
       "      'seeks': 1,\n",
       "      'dupsTested': 0,\n",
       "      'dupsDropped': 0,\n",
       "      'seenInvalidated': 0}}}]},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.find({'name':'sanjay'}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertOneResult at 0x7f9768b2d2b0>"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.insert_one({\n",
    "  \"name\": \"sanjay\",\n",
    "  \"gender\": \"male\",\n",
    "  \"state\": \"mp\",\n",
    "  \"phy\": 34,\n",
    "  \"maths\": 56,\n",
    "  \"bio\": 89\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationFailure",
     "evalue": "Index with name: name_1_state_1 already exists with different options, full error: {'ok': 0.0, 'errmsg': 'Index with name: name_1_state_1 already exists with different options', 'code': 85, 'codeName': 'IndexOptionsConflict'}",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationFailure\u001b[0m                          Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[85], line 4\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;66;03m# creating an unique compound index\u001b[39;00m\n\u001b[1;32m      2\u001b[0m \u001b[38;5;66;03m#wont allow same duplicate values in both the fields\u001b[39;00m\n\u001b[0;32m----> 4\u001b[0m \u001b[43mdb\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstudents\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcreate_index\u001b[49m\u001b[43m(\u001b[49m\u001b[43m[\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mname\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[38;5;241;43m1\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mstate\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[38;5;241;43m1\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43munique\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/collection.py:2055\u001b[0m, in \u001b[0;36mCollection.create_index\u001b[0;34m(self, keys, session, comment, **kwargs)\u001b[0m\n\u001b[1;32m   2053\u001b[0m     cmd_options[\u001b[39m\"\u001b[39m\u001b[39mcomment\u001b[39m\u001b[39m\"\u001b[39m] \u001b[39m=\u001b[39m comment\n\u001b[1;32m   2054\u001b[0m index \u001b[39m=\u001b[39m IndexModel(keys, \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs)\n\u001b[0;32m-> 2055\u001b[0m \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m__create_indexes([index], session, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mcmd_options)[\u001b[39m0\u001b[39m]\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/_csot.py:105\u001b[0m, in \u001b[0;36mapply.<locals>.csot_wrapper\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m    103\u001b[0m         \u001b[39mwith\u001b[39;00m _TimeoutContext(timeout):\n\u001b[1;32m    104\u001b[0m             \u001b[39mreturn\u001b[39;00m func(\u001b[39mself\u001b[39m, \u001b[39m*\u001b[39margs, \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs)\n\u001b[0;32m--> 105\u001b[0m \u001b[39mreturn\u001b[39;00m func(\u001b[39mself\u001b[39;49m, \u001b[39m*\u001b[39;49margs, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mkwargs)\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/collection.py:1932\u001b[0m, in \u001b[0;36mCollection.__create_indexes\u001b[0;34m(self, indexes, session, **kwargs)\u001b[0m\n\u001b[1;32m   1926\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39m\"\u001b[39m\u001b[39mcommitQuorum\u001b[39m\u001b[39m\"\u001b[39m \u001b[39min\u001b[39;00m kwargs \u001b[39mand\u001b[39;00m \u001b[39mnot\u001b[39;00m supports_quorum:\n\u001b[1;32m   1927\u001b[0m         \u001b[39mraise\u001b[39;00m ConfigurationError(\n\u001b[1;32m   1928\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mMust be connected to MongoDB 4.4+ to use the \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m   1929\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mcommitQuorum option for createIndexes\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m   1930\u001b[0m         )\n\u001b[0;32m-> 1932\u001b[0m     \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_command(\n\u001b[1;32m   1933\u001b[0m         sock_info,\n\u001b[1;32m   1934\u001b[0m         cmd,\n\u001b[1;32m   1935\u001b[0m         read_preference\u001b[39m=\u001b[39;49mReadPreference\u001b[39m.\u001b[39;49mPRIMARY,\n\u001b[1;32m   1936\u001b[0m         codec_options\u001b[39m=\u001b[39;49m_UNICODE_REPLACE_CODEC_OPTIONS,\n\u001b[1;32m   1937\u001b[0m         write_concern\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_write_concern_for(session),\n\u001b[1;32m   1938\u001b[0m         session\u001b[39m=\u001b[39;49msession,\n\u001b[1;32m   1939\u001b[0m     )\n\u001b[1;32m   1940\u001b[0m \u001b[39mreturn\u001b[39;00m names\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/collection.py:292\u001b[0m, in \u001b[0;36mCollection._command\u001b[0;34m(self, sock_info, command, read_preference, codec_options, check, allowable_errors, read_concern, write_concern, collation, session, retryable_write, user_fields)\u001b[0m\n\u001b[1;32m    265\u001b[0m \u001b[39m\u001b[39m\u001b[39m\"\"\"Internal command helper.\u001b[39;00m\n\u001b[1;32m    266\u001b[0m \n\u001b[1;32m    267\u001b[0m \u001b[39m:Parameters:\u001b[39;00m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    289\u001b[0m \u001b[39m  The result document.\u001b[39;00m\n\u001b[1;32m    290\u001b[0m \u001b[39m\"\"\"\u001b[39;00m\n\u001b[1;32m    291\u001b[0m \u001b[39mwith\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m__database\u001b[39m.\u001b[39mclient\u001b[39m.\u001b[39m_tmp_session(session) \u001b[39mas\u001b[39;00m s:\n\u001b[0;32m--> 292\u001b[0m     \u001b[39mreturn\u001b[39;00m sock_info\u001b[39m.\u001b[39;49mcommand(\n\u001b[1;32m    293\u001b[0m         \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m__database\u001b[39m.\u001b[39;49mname,\n\u001b[1;32m    294\u001b[0m         command,\n\u001b[1;32m    295\u001b[0m         read_preference \u001b[39mor\u001b[39;49;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_read_preference_for(session),\n\u001b[1;32m    296\u001b[0m         codec_options \u001b[39mor\u001b[39;49;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mcodec_options,\n\u001b[1;32m    297\u001b[0m         check,\n\u001b[1;32m    298\u001b[0m         allowable_errors,\n\u001b[1;32m    299\u001b[0m         read_concern\u001b[39m=\u001b[39;49mread_concern,\n\u001b[1;32m    300\u001b[0m         write_concern\u001b[39m=\u001b[39;49mwrite_concern,\n\u001b[1;32m    301\u001b[0m         parse_write_concern_error\u001b[39m=\u001b[39;49m\u001b[39mTrue\u001b[39;49;00m,\n\u001b[1;32m    302\u001b[0m         collation\u001b[39m=\u001b[39;49mcollation,\n\u001b[1;32m    303\u001b[0m         session\u001b[39m=\u001b[39;49ms,\n\u001b[1;32m    304\u001b[0m         client\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m__database\u001b[39m.\u001b[39;49mclient,\n\u001b[1;32m    305\u001b[0m         retryable_write\u001b[39m=\u001b[39;49mretryable_write,\n\u001b[1;32m    306\u001b[0m         user_fields\u001b[39m=\u001b[39;49muser_fields,\n\u001b[1;32m    307\u001b[0m     )\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/pool.py:767\u001b[0m, in \u001b[0;36mSocketInfo.command\u001b[0;34m(self, dbname, spec, read_preference, codec_options, check, allowable_errors, read_concern, write_concern, parse_write_concern_error, collation, session, client, retryable_write, publish_events, user_fields, exhaust_allowed)\u001b[0m\n\u001b[1;32m    765\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_raise_if_not_writable(unacknowledged)\n\u001b[1;32m    766\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m--> 767\u001b[0m     \u001b[39mreturn\u001b[39;00m command(\n\u001b[1;32m    768\u001b[0m         \u001b[39mself\u001b[39;49m,\n\u001b[1;32m    769\u001b[0m         dbname,\n\u001b[1;32m    770\u001b[0m         spec,\n\u001b[1;32m    771\u001b[0m         \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mis_mongos,\n\u001b[1;32m    772\u001b[0m         read_preference,\n\u001b[1;32m    773\u001b[0m         codec_options,\n\u001b[1;32m    774\u001b[0m         session,\n\u001b[1;32m    775\u001b[0m         client,\n\u001b[1;32m    776\u001b[0m         check,\n\u001b[1;32m    777\u001b[0m         allowable_errors,\n\u001b[1;32m    778\u001b[0m         \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49maddress,\n\u001b[1;32m    779\u001b[0m         listeners,\n\u001b[1;32m    780\u001b[0m         \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mmax_bson_size,\n\u001b[1;32m    781\u001b[0m         read_concern,\n\u001b[1;32m    782\u001b[0m         parse_write_concern_error\u001b[39m=\u001b[39;49mparse_write_concern_error,\n\u001b[1;32m    783\u001b[0m         collation\u001b[39m=\u001b[39;49mcollation,\n\u001b[1;32m    784\u001b[0m         compression_ctx\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mcompression_context,\n\u001b[1;32m    785\u001b[0m         use_op_msg\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mop_msg_enabled,\n\u001b[1;32m    786\u001b[0m         unacknowledged\u001b[39m=\u001b[39;49munacknowledged,\n\u001b[1;32m    787\u001b[0m         user_fields\u001b[39m=\u001b[39;49muser_fields,\n\u001b[1;32m    788\u001b[0m         exhaust_allowed\u001b[39m=\u001b[39;49mexhaust_allowed,\n\u001b[1;32m    789\u001b[0m         write_concern\u001b[39m=\u001b[39;49mwrite_concern,\n\u001b[1;32m    790\u001b[0m     )\n\u001b[1;32m    791\u001b[0m \u001b[39mexcept\u001b[39;00m (OperationFailure, NotPrimaryError):\n\u001b[1;32m    792\u001b[0m     \u001b[39mraise\u001b[39;00m\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/network.py:166\u001b[0m, in \u001b[0;36mcommand\u001b[0;34m(sock_info, dbname, spec, is_mongos, read_preference, codec_options, session, client, check, allowable_errors, address, listeners, max_bson_size, read_concern, parse_write_concern_error, collation, compression_ctx, use_op_msg, unacknowledged, user_fields, exhaust_allowed, write_concern)\u001b[0m\n\u001b[1;32m    164\u001b[0m             client\u001b[39m.\u001b[39m_process_response(response_doc, session)\n\u001b[1;32m    165\u001b[0m         \u001b[39mif\u001b[39;00m check:\n\u001b[0;32m--> 166\u001b[0m             helpers\u001b[39m.\u001b[39;49m_check_command_response(\n\u001b[1;32m    167\u001b[0m                 response_doc,\n\u001b[1;32m    168\u001b[0m                 sock_info\u001b[39m.\u001b[39;49mmax_wire_version,\n\u001b[1;32m    169\u001b[0m                 allowable_errors,\n\u001b[1;32m    170\u001b[0m                 parse_write_concern_error\u001b[39m=\u001b[39;49mparse_write_concern_error,\n\u001b[1;32m    171\u001b[0m             )\n\u001b[1;32m    172\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mException\u001b[39;00m \u001b[39mas\u001b[39;00m exc:\n\u001b[1;32m    173\u001b[0m     \u001b[39mif\u001b[39;00m publish:\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/helpers.py:181\u001b[0m, in \u001b[0;36m_check_command_response\u001b[0;34m(response, max_wire_version, allowable_errors, parse_write_concern_error)\u001b[0m\n\u001b[1;32m    178\u001b[0m \u001b[39melif\u001b[39;00m code \u001b[39m==\u001b[39m \u001b[39m43\u001b[39m:\n\u001b[1;32m    179\u001b[0m     \u001b[39mraise\u001b[39;00m CursorNotFound(errmsg, code, response, max_wire_version)\n\u001b[0;32m--> 181\u001b[0m \u001b[39mraise\u001b[39;00m OperationFailure(errmsg, code, response, max_wire_version)\n",
      "\u001b[0;31mOperationFailure\u001b[0m: Index with name: name_1_state_1 already exists with different options, full error: {'ok': 0.0, 'errmsg': 'Index with name: name_1_state_1 already exists with different options', 'code': 85, 'codeName': 'IndexOptionsConflict'}"
     ]
    }
   ],
   "source": [
    "# creating an unique compound index\n",
    "#wont allow same duplicate values in both the fields\n",
    "\n",
    "db.students.create_index([('name',1),('state',1)], unique=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertOneResult at 0x7fc07cff8ee0>"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Throws an error due to duplicates values along the compound index\n",
    "db.students.insert_one({\"name\": \"jayesh\",\n",
    "  \"gender\": \"male\",\n",
    "  \"state\": \"up\",\n",
    "  \"phy\": 87,\n",
    "  \"maths\": 88,\n",
    "  \"bio\": 32})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationFailure",
     "evalue": "Index with name: name_1 already exists with different options, full error: {'ok': 0.0, 'errmsg': 'Index with name: name_1 already exists with different options', 'code': 85, 'codeName': 'IndexOptionsConflict'}",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationFailure\u001b[0m                          Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[86], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;66;03m# Exists operator \u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m \u001b[43mdb\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstudents\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcreate_index\u001b[49m\u001b[43m(\u001b[49m\u001b[43m[\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mname\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[38;5;241;43m1\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mpartialFilterExpression\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m{\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mstate\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m:\u001b[49m\u001b[43m{\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m$exists\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m:\u001b[49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m}\u001b[49m\u001b[43m}\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/collection.py:2055\u001b[0m, in \u001b[0;36mCollection.create_index\u001b[0;34m(self, keys, session, comment, **kwargs)\u001b[0m\n\u001b[1;32m   2053\u001b[0m     cmd_options[\u001b[39m\"\u001b[39m\u001b[39mcomment\u001b[39m\u001b[39m\"\u001b[39m] \u001b[39m=\u001b[39m comment\n\u001b[1;32m   2054\u001b[0m index \u001b[39m=\u001b[39m IndexModel(keys, \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs)\n\u001b[0;32m-> 2055\u001b[0m \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m__create_indexes([index], session, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mcmd_options)[\u001b[39m0\u001b[39m]\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/_csot.py:105\u001b[0m, in \u001b[0;36mapply.<locals>.csot_wrapper\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m    103\u001b[0m         \u001b[39mwith\u001b[39;00m _TimeoutContext(timeout):\n\u001b[1;32m    104\u001b[0m             \u001b[39mreturn\u001b[39;00m func(\u001b[39mself\u001b[39m, \u001b[39m*\u001b[39margs, \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs)\n\u001b[0;32m--> 105\u001b[0m \u001b[39mreturn\u001b[39;00m func(\u001b[39mself\u001b[39;49m, \u001b[39m*\u001b[39;49margs, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mkwargs)\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/collection.py:1932\u001b[0m, in \u001b[0;36mCollection.__create_indexes\u001b[0;34m(self, indexes, session, **kwargs)\u001b[0m\n\u001b[1;32m   1926\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39m\"\u001b[39m\u001b[39mcommitQuorum\u001b[39m\u001b[39m\"\u001b[39m \u001b[39min\u001b[39;00m kwargs \u001b[39mand\u001b[39;00m \u001b[39mnot\u001b[39;00m supports_quorum:\n\u001b[1;32m   1927\u001b[0m         \u001b[39mraise\u001b[39;00m ConfigurationError(\n\u001b[1;32m   1928\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mMust be connected to MongoDB 4.4+ to use the \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m   1929\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mcommitQuorum option for createIndexes\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m   1930\u001b[0m         )\n\u001b[0;32m-> 1932\u001b[0m     \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_command(\n\u001b[1;32m   1933\u001b[0m         sock_info,\n\u001b[1;32m   1934\u001b[0m         cmd,\n\u001b[1;32m   1935\u001b[0m         read_preference\u001b[39m=\u001b[39;49mReadPreference\u001b[39m.\u001b[39;49mPRIMARY,\n\u001b[1;32m   1936\u001b[0m         codec_options\u001b[39m=\u001b[39;49m_UNICODE_REPLACE_CODEC_OPTIONS,\n\u001b[1;32m   1937\u001b[0m         write_concern\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_write_concern_for(session),\n\u001b[1;32m   1938\u001b[0m         session\u001b[39m=\u001b[39;49msession,\n\u001b[1;32m   1939\u001b[0m     )\n\u001b[1;32m   1940\u001b[0m \u001b[39mreturn\u001b[39;00m names\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/collection.py:292\u001b[0m, in \u001b[0;36mCollection._command\u001b[0;34m(self, sock_info, command, read_preference, codec_options, check, allowable_errors, read_concern, write_concern, collation, session, retryable_write, user_fields)\u001b[0m\n\u001b[1;32m    265\u001b[0m \u001b[39m\u001b[39m\u001b[39m\"\"\"Internal command helper.\u001b[39;00m\n\u001b[1;32m    266\u001b[0m \n\u001b[1;32m    267\u001b[0m \u001b[39m:Parameters:\u001b[39;00m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    289\u001b[0m \u001b[39m  The result document.\u001b[39;00m\n\u001b[1;32m    290\u001b[0m \u001b[39m\"\"\"\u001b[39;00m\n\u001b[1;32m    291\u001b[0m \u001b[39mwith\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m__database\u001b[39m.\u001b[39mclient\u001b[39m.\u001b[39m_tmp_session(session) \u001b[39mas\u001b[39;00m s:\n\u001b[0;32m--> 292\u001b[0m     \u001b[39mreturn\u001b[39;00m sock_info\u001b[39m.\u001b[39;49mcommand(\n\u001b[1;32m    293\u001b[0m         \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m__database\u001b[39m.\u001b[39;49mname,\n\u001b[1;32m    294\u001b[0m         command,\n\u001b[1;32m    295\u001b[0m         read_preference \u001b[39mor\u001b[39;49;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_read_preference_for(session),\n\u001b[1;32m    296\u001b[0m         codec_options \u001b[39mor\u001b[39;49;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mcodec_options,\n\u001b[1;32m    297\u001b[0m         check,\n\u001b[1;32m    298\u001b[0m         allowable_errors,\n\u001b[1;32m    299\u001b[0m         read_concern\u001b[39m=\u001b[39;49mread_concern,\n\u001b[1;32m    300\u001b[0m         write_concern\u001b[39m=\u001b[39;49mwrite_concern,\n\u001b[1;32m    301\u001b[0m         parse_write_concern_error\u001b[39m=\u001b[39;49m\u001b[39mTrue\u001b[39;49;00m,\n\u001b[1;32m    302\u001b[0m         collation\u001b[39m=\u001b[39;49mcollation,\n\u001b[1;32m    303\u001b[0m         session\u001b[39m=\u001b[39;49ms,\n\u001b[1;32m    304\u001b[0m         client\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m__database\u001b[39m.\u001b[39;49mclient,\n\u001b[1;32m    305\u001b[0m         retryable_write\u001b[39m=\u001b[39;49mretryable_write,\n\u001b[1;32m    306\u001b[0m         user_fields\u001b[39m=\u001b[39;49muser_fields,\n\u001b[1;32m    307\u001b[0m     )\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/pool.py:767\u001b[0m, in \u001b[0;36mSocketInfo.command\u001b[0;34m(self, dbname, spec, read_preference, codec_options, check, allowable_errors, read_concern, write_concern, parse_write_concern_error, collation, session, client, retryable_write, publish_events, user_fields, exhaust_allowed)\u001b[0m\n\u001b[1;32m    765\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_raise_if_not_writable(unacknowledged)\n\u001b[1;32m    766\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m--> 767\u001b[0m     \u001b[39mreturn\u001b[39;00m command(\n\u001b[1;32m    768\u001b[0m         \u001b[39mself\u001b[39;49m,\n\u001b[1;32m    769\u001b[0m         dbname,\n\u001b[1;32m    770\u001b[0m         spec,\n\u001b[1;32m    771\u001b[0m         \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mis_mongos,\n\u001b[1;32m    772\u001b[0m         read_preference,\n\u001b[1;32m    773\u001b[0m         codec_options,\n\u001b[1;32m    774\u001b[0m         session,\n\u001b[1;32m    775\u001b[0m         client,\n\u001b[1;32m    776\u001b[0m         check,\n\u001b[1;32m    777\u001b[0m         allowable_errors,\n\u001b[1;32m    778\u001b[0m         \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49maddress,\n\u001b[1;32m    779\u001b[0m         listeners,\n\u001b[1;32m    780\u001b[0m         \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mmax_bson_size,\n\u001b[1;32m    781\u001b[0m         read_concern,\n\u001b[1;32m    782\u001b[0m         parse_write_concern_error\u001b[39m=\u001b[39;49mparse_write_concern_error,\n\u001b[1;32m    783\u001b[0m         collation\u001b[39m=\u001b[39;49mcollation,\n\u001b[1;32m    784\u001b[0m         compression_ctx\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mcompression_context,\n\u001b[1;32m    785\u001b[0m         use_op_msg\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mop_msg_enabled,\n\u001b[1;32m    786\u001b[0m         unacknowledged\u001b[39m=\u001b[39;49munacknowledged,\n\u001b[1;32m    787\u001b[0m         user_fields\u001b[39m=\u001b[39;49muser_fields,\n\u001b[1;32m    788\u001b[0m         exhaust_allowed\u001b[39m=\u001b[39;49mexhaust_allowed,\n\u001b[1;32m    789\u001b[0m         write_concern\u001b[39m=\u001b[39;49mwrite_concern,\n\u001b[1;32m    790\u001b[0m     )\n\u001b[1;32m    791\u001b[0m \u001b[39mexcept\u001b[39;00m (OperationFailure, NotPrimaryError):\n\u001b[1;32m    792\u001b[0m     \u001b[39mraise\u001b[39;00m\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/network.py:166\u001b[0m, in \u001b[0;36mcommand\u001b[0;34m(sock_info, dbname, spec, is_mongos, read_preference, codec_options, session, client, check, allowable_errors, address, listeners, max_bson_size, read_concern, parse_write_concern_error, collation, compression_ctx, use_op_msg, unacknowledged, user_fields, exhaust_allowed, write_concern)\u001b[0m\n\u001b[1;32m    164\u001b[0m             client\u001b[39m.\u001b[39m_process_response(response_doc, session)\n\u001b[1;32m    165\u001b[0m         \u001b[39mif\u001b[39;00m check:\n\u001b[0;32m--> 166\u001b[0m             helpers\u001b[39m.\u001b[39;49m_check_command_response(\n\u001b[1;32m    167\u001b[0m                 response_doc,\n\u001b[1;32m    168\u001b[0m                 sock_info\u001b[39m.\u001b[39;49mmax_wire_version,\n\u001b[1;32m    169\u001b[0m                 allowable_errors,\n\u001b[1;32m    170\u001b[0m                 parse_write_concern_error\u001b[39m=\u001b[39;49mparse_write_concern_error,\n\u001b[1;32m    171\u001b[0m             )\n\u001b[1;32m    172\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mException\u001b[39;00m \u001b[39mas\u001b[39;00m exc:\n\u001b[1;32m    173\u001b[0m     \u001b[39mif\u001b[39;00m publish:\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/pymongo/helpers.py:181\u001b[0m, in \u001b[0;36m_check_command_response\u001b[0;34m(response, max_wire_version, allowable_errors, parse_write_concern_error)\u001b[0m\n\u001b[1;32m    178\u001b[0m \u001b[39melif\u001b[39;00m code \u001b[39m==\u001b[39m \u001b[39m43\u001b[39m:\n\u001b[1;32m    179\u001b[0m     \u001b[39mraise\u001b[39;00m CursorNotFound(errmsg, code, response, max_wire_version)\n\u001b[0;32m--> 181\u001b[0m \u001b[39mraise\u001b[39;00m OperationFailure(errmsg, code, response, max_wire_version)\n",
      "\u001b[0;31mOperationFailure\u001b[0m: Index with name: name_1 already exists with different options, full error: {'ok': 0.0, 'errmsg': 'Index with name: name_1 already exists with different options', 'code': 85, 'codeName': 'IndexOptionsConflict'}"
     ]
    }
   ],
   "source": [
    "# Exists operator \n",
    "db.students.create_index([('name',1)], partialFilterExpression={'state':{'$exists':True}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "ip=db.students.find({'name': 'rohan'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in ip:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'name': {'$eq': 'rohan'}},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'name': 1},\n",
       "    'indexName': 'name_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': []},\n",
       "    'isUnique': True,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"rohan\", \"rohan\"]']}}},\n",
       "  'rejectedPlans': [{'stage': 'FETCH',\n",
       "    'inputStage': {'stage': 'IXSCAN',\n",
       "     'keyPattern': {'name': 1, 'state': 1},\n",
       "     'indexName': 'name_1_state_1',\n",
       "     'isMultiKey': False,\n",
       "     'multiKeyPaths': {'name': [], 'state': []},\n",
       "     'isUnique': False,\n",
       "     'isSparse': False,\n",
       "     'isPartial': False,\n",
       "     'indexVersion': 2,\n",
       "     'direction': 'forward',\n",
       "     'indexBounds': {'name': ['[\"rohan\", \"rohan\"]'],\n",
       "      'state': ['[MinKey, MaxKey]']}}}]},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 0,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 0,\n",
       "  'totalDocsExamined': 0,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'nReturned': 0,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 0,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 0,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 0,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 1,\n",
       "    'advanced': 0,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'name': 1},\n",
       "    'indexName': 'name_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': []},\n",
       "    'isUnique': True,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"rohan\", \"rohan\"]']},\n",
       "    'keysExamined': 0,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 0,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': [{'nReturned': 0,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'totalKeysExamined': 0,\n",
       "    'totalDocsExamined': 0,\n",
       "    'executionStages': {'stage': 'FETCH',\n",
       "     'nReturned': 0,\n",
       "     'executionTimeMillisEstimate': 0,\n",
       "     'works': 1,\n",
       "     'advanced': 0,\n",
       "     'needTime': 0,\n",
       "     'needYield': 0,\n",
       "     'saveState': 0,\n",
       "     'restoreState': 0,\n",
       "     'isEOF': 1,\n",
       "     'invalidates': 0,\n",
       "     'docsExamined': 0,\n",
       "     'alreadyHasObj': 0,\n",
       "     'inputStage': {'stage': 'IXSCAN',\n",
       "      'nReturned': 0,\n",
       "      'executionTimeMillisEstimate': 0,\n",
       "      'works': 1,\n",
       "      'advanced': 0,\n",
       "      'needTime': 0,\n",
       "      'needYield': 0,\n",
       "      'saveState': 0,\n",
       "      'restoreState': 0,\n",
       "      'isEOF': 1,\n",
       "      'invalidates': 0,\n",
       "      'keyPattern': {'name': 1, 'state': 1},\n",
       "      'indexName': 'name_1_state_1',\n",
       "      'isMultiKey': False,\n",
       "      'multiKeyPaths': {'name': [], 'state': []},\n",
       "      'isUnique': False,\n",
       "      'isSparse': False,\n",
       "      'isPartial': False,\n",
       "      'indexVersion': 2,\n",
       "      'direction': 'forward',\n",
       "      'indexBounds': {'name': ['[\"rohan\", \"rohan\"]'],\n",
       "       'state': ['[MinKey, MaxKey]']},\n",
       "      'keysExamined': 0,\n",
       "      'seeks': 1,\n",
       "      'dupsTested': 0,\n",
       "      'dupsDropped': 0,\n",
       "      'seenInvalidated': 0}}},\n",
       "   {'nReturned': 0,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'totalKeysExamined': 0,\n",
       "    'totalDocsExamined': 0,\n",
       "    'executionStages': {'stage': 'FETCH',\n",
       "     'nReturned': 0,\n",
       "     'executionTimeMillisEstimate': 0,\n",
       "     'works': 1,\n",
       "     'advanced': 0,\n",
       "     'needTime': 0,\n",
       "     'needYield': 0,\n",
       "     'saveState': 0,\n",
       "     'restoreState': 0,\n",
       "     'isEOF': 1,\n",
       "     'invalidates': 0,\n",
       "     'docsExamined': 0,\n",
       "     'alreadyHasObj': 0,\n",
       "     'inputStage': {'stage': 'IXSCAN',\n",
       "      'nReturned': 0,\n",
       "      'executionTimeMillisEstimate': 0,\n",
       "      'works': 1,\n",
       "      'advanced': 0,\n",
       "      'needTime': 0,\n",
       "      'needYield': 0,\n",
       "      'saveState': 0,\n",
       "      'restoreState': 0,\n",
       "      'isEOF': 1,\n",
       "      'invalidates': 0,\n",
       "      'keyPattern': {'name': 1},\n",
       "      'indexName': 'name_1',\n",
       "      'isMultiKey': False,\n",
       "      'multiKeyPaths': {'name': []},\n",
       "      'isUnique': True,\n",
       "      'isSparse': False,\n",
       "      'isPartial': False,\n",
       "      'indexVersion': 2,\n",
       "      'direction': 'forward',\n",
       "      'indexBounds': {'name': ['[\"rohan\", \"rohan\"]']},\n",
       "      'keysExamined': 0,\n",
       "      'seeks': 1,\n",
       "      'dupsTested': 0,\n",
       "      'dupsDropped': 0,\n",
       "      'seenInvalidated': 0}}}]},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.find({'name': 'rohan'}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "ip=db.students.find({'gender':{'$exists':True}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d1f9917a7edc19f313e'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n"
     ]
    }
   ],
   "source": [
    "for i in ip:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertOneResult at 0x7f97683abdf0>"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.insert_one({\n",
    "    'name':'naman'\n",
    "})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertOneResult at 0x7f9769c3a8b0>"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.insert_one({\n",
    "    'name':'preeti',\n",
    "    'gender':'female'\n",
    "})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id_': {'v': 2, 'key': [('_id', 1)], 'ns': 'students_details.students'},\n",
       " 'name_1_state_1': {'v': 2,\n",
       "  'key': [('name', 1), ('state', 1)],\n",
       "  'ns': 'students_details.students'},\n",
       " 'maths_1': {'v': 2,\n",
       "  'key': [('maths', 1)],\n",
       "  'ns': 'students_details.students',\n",
       "  'partialFilterExpression': SON([('maths', SON([('$gt', 70)]))])},\n",
       " 'name_1': {'v': 2,\n",
       "  'unique': True,\n",
       "  'key': [('name', 1)],\n",
       "  'ns': 'students_details.students'}}"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.index_information()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.students.drop_index('name_1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id_': {'v': 2, 'key': [('_id', 1)], 'ns': 'students_details.students'},\n",
       " 'name_1_state_1': {'v': 2,\n",
       "  'key': [('name', 1), ('state', 1)],\n",
       "  'ns': 'students_details.students'},\n",
       " 'maths_1': {'v': 2,\n",
       "  'key': [('maths', 1)],\n",
       "  'ns': 'students_details.students',\n",
       "  'partialFilterExpression': SON([('maths', SON([('$gt', 70)]))])}}"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Covered query\n",
    "# Query in whcih all fields are a part of an index\n",
    "# We alreadt have an index on name and index\n",
    "db.students.index_information()\n",
    "i=db.students.find({'name':'sanjay'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d1f9917a7edc19f313e'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n"
     ]
    }
   ],
   "source": [
    "for i in i:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'name': {'$eq': 'sanjay'}},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "     'state': ['[MinKey, MaxKey]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 1,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 1,\n",
       "  'totalDocsExamined': 1,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'nReturned': 1,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 1,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 1,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 1,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 2,\n",
       "    'advanced': 1,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "     'state': ['[MinKey, MaxKey]']},\n",
       "    'keysExamined': 1,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 0,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Since we are projecting the whole document than it will fetch it from disk after getting the disk location from index\n",
    "db.students.find({'name':'sanjay'}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'$and': [{'name': {'$eq': 'sanjay'}},\n",
       "    {'state': {'$eq': 'mp'}}]},\n",
       "  'winningPlan': {'stage': 'PROJECTION',\n",
       "   'transformBy': {'name': 1},\n",
       "   'inputStage': {'stage': 'FETCH',\n",
       "    'inputStage': {'stage': 'IXSCAN',\n",
       "     'keyPattern': {'name': 1, 'state': 1},\n",
       "     'indexName': 'name_1_state_1',\n",
       "     'isMultiKey': False,\n",
       "     'multiKeyPaths': {'name': [], 'state': []},\n",
       "     'isUnique': False,\n",
       "     'isSparse': False,\n",
       "     'isPartial': False,\n",
       "     'indexVersion': 2,\n",
       "     'direction': 'forward',\n",
       "     'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "      'state': ['[\"mp\", \"mp\"]']}}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 1,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 1,\n",
       "  'totalDocsExamined': 1,\n",
       "  'executionStages': {'stage': 'PROJECTION',\n",
       "   'nReturned': 1,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 1,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'transformBy': {'name': 1},\n",
       "   'inputStage': {'stage': 'FETCH',\n",
       "    'nReturned': 1,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 2,\n",
       "    'advanced': 1,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'docsExamined': 1,\n",
       "    'alreadyHasObj': 0,\n",
       "    'inputStage': {'stage': 'IXSCAN',\n",
       "     'nReturned': 1,\n",
       "     'executionTimeMillisEstimate': 0,\n",
       "     'works': 2,\n",
       "     'advanced': 1,\n",
       "     'needTime': 0,\n",
       "     'needYield': 0,\n",
       "     'saveState': 0,\n",
       "     'restoreState': 0,\n",
       "     'isEOF': 1,\n",
       "     'invalidates': 0,\n",
       "     'keyPattern': {'name': 1, 'state': 1},\n",
       "     'indexName': 'name_1_state_1',\n",
       "     'isMultiKey': False,\n",
       "     'multiKeyPaths': {'name': [], 'state': []},\n",
       "     'isUnique': False,\n",
       "     'isSparse': False,\n",
       "     'isPartial': False,\n",
       "     'indexVersion': 2,\n",
       "     'direction': 'forward',\n",
       "     'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "      'state': ['[\"mp\", \"mp\"]']},\n",
       "     'keysExamined': 1,\n",
       "     'seeks': 1,\n",
       "     'dupsTested': 0,\n",
       "     'dupsDropped': 0,\n",
       "     'seenInvalidated': 0}}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# since the default unique index is not a part of the index we created hence it will again fetch the document from the disk\n",
    "db.students.find({'name':'sanjay','state':'mp'},{'name':1}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'$and': [{'name': {'$eq': 'sanjay'}},\n",
       "    {'state': {'$eq': 'mp'}}]},\n",
       "  'winningPlan': {'stage': 'PROJECTION',\n",
       "   'transformBy': {'name': 1, '_id': 0},\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "     'state': ['[\"mp\", \"mp\"]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 1,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 1,\n",
       "  'totalDocsExamined': 0,\n",
       "  'executionStages': {'stage': 'PROJECTION',\n",
       "   'nReturned': 1,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 1,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'transformBy': {'name': 1, '_id': 0},\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 1,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 2,\n",
       "    'advanced': 1,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "     'state': ['[\"mp\", \"mp\"]']},\n",
       "    'keysExamined': 1,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 0,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# It wont fetch and the result exist within the index\n",
    "db.students.find({'name':'sanjay','state':'mp'},{'name':1,'_id':0}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'$and': [{'name': {'$eq': 'sanjay'}},\n",
       "    {'state': {'$eq': 'mp'}}]},\n",
       "  'winningPlan': {'stage': 'PROJECTION',\n",
       "   'transformBy': {'name': 1, '_id': 0, 'state': 1},\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "     'state': ['[\"mp\", \"mp\"]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 1,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 1,\n",
       "  'totalDocsExamined': 0,\n",
       "  'executionStages': {'stage': 'PROJECTION',\n",
       "   'nReturned': 1,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 1,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'transformBy': {'name': 1, '_id': 0, 'state': 1},\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 1,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 2,\n",
       "    'advanced': 1,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'name': 1, 'state': 1},\n",
       "    'indexName': 'name_1_state_1',\n",
       "    'isMultiKey': False,\n",
       "    'multiKeyPaths': {'name': [], 'state': []},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "     'state': ['[\"mp\", \"mp\"]']},\n",
       "    'keysExamined': 1,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 0,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# It wont fetch any documents and hence the result will be projected from the index itself\n",
    "db.students.find({'name':'sanjay','state':'mp'},{'name':1,'_id':0,'state':1}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'$and': [{'name': {'$eq': 'sanjay'}},\n",
       "    {'state': {'$eq': 'mp'}}]},\n",
       "  'winningPlan': {'stage': 'PROJECTION',\n",
       "   'transformBy': {'name': 1, '_id': 0, 'maths': 1},\n",
       "   'inputStage': {'stage': 'FETCH',\n",
       "    'inputStage': {'stage': 'IXSCAN',\n",
       "     'keyPattern': {'name': 1, 'state': 1},\n",
       "     'indexName': 'name_1_state_1',\n",
       "     'isMultiKey': False,\n",
       "     'multiKeyPaths': {'name': [], 'state': []},\n",
       "     'isUnique': False,\n",
       "     'isSparse': False,\n",
       "     'isPartial': False,\n",
       "     'indexVersion': 2,\n",
       "     'direction': 'forward',\n",
       "     'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "      'state': ['[\"mp\", \"mp\"]']}}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 1,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 1,\n",
       "  'totalDocsExamined': 1,\n",
       "  'executionStages': {'stage': 'PROJECTION',\n",
       "   'nReturned': 1,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 1,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'transformBy': {'name': 1, '_id': 0, 'maths': 1},\n",
       "   'inputStage': {'stage': 'FETCH',\n",
       "    'nReturned': 1,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 2,\n",
       "    'advanced': 1,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'docsExamined': 1,\n",
       "    'alreadyHasObj': 0,\n",
       "    'inputStage': {'stage': 'IXSCAN',\n",
       "     'nReturned': 1,\n",
       "     'executionTimeMillisEstimate': 0,\n",
       "     'works': 2,\n",
       "     'advanced': 1,\n",
       "     'needTime': 0,\n",
       "     'needYield': 0,\n",
       "     'saveState': 0,\n",
       "     'restoreState': 0,\n",
       "     'isEOF': 1,\n",
       "     'invalidates': 0,\n",
       "     'keyPattern': {'name': 1, 'state': 1},\n",
       "     'indexName': 'name_1_state_1',\n",
       "     'isMultiKey': False,\n",
       "     'multiKeyPaths': {'name': [], 'state': []},\n",
       "     'isUnique': False,\n",
       "     'isSparse': False,\n",
       "     'isPartial': False,\n",
       "     'indexVersion': 2,\n",
       "     'direction': 'forward',\n",
       "     'indexBounds': {'name': ['[\"sanjay\", \"sanjay\"]'],\n",
       "      'state': ['[\"mp\", \"mp\"]']},\n",
       "     'keysExamined': 1,\n",
       "     'seeks': 1,\n",
       "     'dupsTested': 0,\n",
       "     'dupsDropped': 0,\n",
       "     'seenInvalidated': 0}}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fetch the documents bcz maths isnt part of index \n",
    "db.students.find({'name':'sanjay','state':'mp'},{'name':1,'_id':0,'maths':1}).explain()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "winning plan -- Its the optimised plan choosen after considering a lot of execution plan on the cost and execution time parameters having \n",
    "very few data shuffling\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertOneResult at 0x7f97688430a0>"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.insert_one({\n",
    "    'name':'naman',\n",
    "    'gender':'male',\n",
    "    'state':'Delhi',\n",
    "    'phy':89,\n",
    "    'maths':78,\n",
    "    'bio':97,\n",
    "    'hobbies':['Reading books','Football','Cricket']\n",
    "\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "i = db.students.find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d1f9917a7edc19f313e'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647d9d3a9917a7edc19f313f'), 'name': 'naman'}\n",
      "{'_id': ObjectId('647d9d3c9917a7edc19f3140'), 'name': 'preeti', 'gender': 'female'}\n",
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n"
     ]
    }
   ],
   "source": [
    "for i in i:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'hobbies_1'"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Mutli-key index\n",
    "# It's an index created on an array fields \n",
    "# Array values can be scalar or it can be nested documents\n",
    "\n",
    "db.students.create_index([('hobbies',1)])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id_': {'v': 2, 'key': [('_id', 1)], 'ns': 'students_details.students'},\n",
       " 'name_1_state_1': {'v': 2,\n",
       "  'key': [('name', 1), ('state', 1)],\n",
       "  'ns': 'students_details.students'},\n",
       " 'maths_1': {'v': 2,\n",
       "  'key': [('maths', 1)],\n",
       "  'ns': 'students_details.students',\n",
       "  'partialFilterExpression': SON([('maths', SON([('$gt', 70)]))])},\n",
       " 'hobbies_1': {'v': 2,\n",
       "  'key': [('hobbies', 1)],\n",
       "  'ns': 'students_details.students'}}"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.index_information()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "op=db.students.find({'hobbies':'Football'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertManyResult at 0x7f9820d4b310>"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.insert_many([{\n",
    "    'name':'kenjal',\n",
    "    'gender':'female',\n",
    "    'state':'krnk',\n",
    "    'phy':89,\n",
    "    'maths':78,\n",
    "    'bio':97,\n",
    "    'hobbies':['Shopping','Cricket']\n",
    "\n",
    "},\n",
    "{\n",
    "    'name':'nirjara',\n",
    "    'gender':'female',\n",
    "    'state':'delhi',\n",
    "    'phy':78,\n",
    "    'maths':90,\n",
    "    'bio':98,\n",
    "    'hobbies':['Reading','Watching Movies']\n",
    "\n",
    "},\n",
    "{\n",
    "    'name':'sid',\n",
    "    'gender':'male',\n",
    "    'state':'up',\n",
    "    'phy':89,\n",
    "    'maths':78,\n",
    "    'bio':97,\n",
    "    'hobbies':['Gaming','Cricket','Football']\n",
    "\n",
    "}]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "i=db.students.find({\n",
    "    'hobbies':'Football'\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3144'), 'name': 'sid', 'gender': 'male', 'state': 'up', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Gaming', 'Cricket', 'Football']}\n"
     ]
    }
   ],
   "source": [
    "for i in i:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'hobbies': {'$eq': 'Football'}},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'hobbies': 1},\n",
       "    'indexName': 'hobbies_1',\n",
       "    'isMultiKey': True,\n",
       "    'multiKeyPaths': {'hobbies': ['hobbies']},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'hobbies': ['[\"Football\", \"Football\"]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 2,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 2,\n",
       "  'totalDocsExamined': 2,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'nReturned': 2,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 3,\n",
       "   'advanced': 2,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 2,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 2,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 3,\n",
       "    'advanced': 2,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'hobbies': 1},\n",
       "    'indexName': 'hobbies_1',\n",
       "    'isMultiKey': True,\n",
       "    'multiKeyPaths': {'hobbies': ['hobbies']},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'hobbies': ['[\"Football\", \"Football\"]']},\n",
       "    'keysExamined': 2,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 2,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.find({\n",
    "    'hobbies':'Football'\n",
    "}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "i=db.students.find({\n",
    "    'hobbies':'Reading'\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies']}\n"
     ]
    }
   ],
   "source": [
    "for i in i:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "i=db.students.find({\n",
    "    'hobbies':'Reading books'\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n"
     ]
    }
   ],
   "source": [
    "for i in i :\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "i=db.students.find({\n",
    "    'hobbies':[ 'Reading books' , 'Football','Cricket']\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n"
     ]
    }
   ],
   "source": [
    "for i in i:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertOneResult at 0x7f97687d74f0>"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.insert_one({\n",
    "    'name':'jitin',\n",
    "    'gender':'male',\n",
    "    'state':'Delhi',\n",
    "    'phy':89,\n",
    "    'maths':78,\n",
    "    'bio':97,\n",
    "    'hobbies':['Reading books','Football','Cricket'],\n",
    "    'education':[\n",
    "        {\n",
    "            'year':2014,\n",
    "            'school_name':'bnps',\n",
    "            'graduation_year':2015,\n",
    "            'education_type':'12th Standard'   },\n",
    "        {\n",
    "            'year':2016,\n",
    "            'school_name':'dtu',\n",
    "            'graduation_year':2020,\n",
    "            'education_type':'B.Tech'        }\n",
    "    ]\n",
    "\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'education.school_name_1_education.education_type_-1'"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# compound multi index on embedded documents\n",
    "\n",
    "db.students.create_index([('education.school_name',1),('education.education_type',-1)])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "i=db.students.find({'education.school_name':'bnps'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n"
     ]
    }
   ],
   "source": [
    "for i in i:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'education.school_name': {'$eq': 'bnps'}},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'education.school_name': 1, 'education.education_type': -1},\n",
       "    'indexName': 'education.school_name_1_education.education_type_-1',\n",
       "    'isMultiKey': True,\n",
       "    'multiKeyPaths': {'education.school_name': ['education'],\n",
       "     'education.education_type': ['education']},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'education.school_name': ['[\"bnps\", \"bnps\"]'],\n",
       "     'education.education_type': ['[MaxKey, MinKey]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 1,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 1,\n",
       "  'totalDocsExamined': 1,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'nReturned': 1,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 1,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 1,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 1,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 2,\n",
       "    'advanced': 1,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'education.school_name': 1, 'education.education_type': -1},\n",
       "    'indexName': 'education.school_name_1_education.education_type_-1',\n",
       "    'isMultiKey': True,\n",
       "    'multiKeyPaths': {'education.school_name': ['education'],\n",
       "     'education.education_type': ['education']},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'education.school_name': ['[\"bnps\", \"bnps\"]'],\n",
       "     'education.education_type': ['[MaxKey, MinKey]']},\n",
       "    'keysExamined': 1,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 1,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.find({'education.school_name':'bnps'}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'$and': [{'education.education_type': {'$eq': '12th Standard'}},\n",
       "    {'education.school_name': {'$eq': 'bnps'}}]},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'filter': {'education.education_type': {'$eq': '12th Standard'}},\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'education.school_name': 1, 'education.education_type': -1},\n",
       "    'indexName': 'education.school_name_1_education.education_type_-1',\n",
       "    'isMultiKey': True,\n",
       "    'multiKeyPaths': {'education.school_name': ['education'],\n",
       "     'education.education_type': ['education']},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'education.school_name': ['[\"bnps\", \"bnps\"]'],\n",
       "     'education.education_type': ['[MaxKey, MinKey]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 1,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 1,\n",
       "  'totalDocsExamined': 1,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'filter': {'education.education_type': {'$eq': '12th Standard'}},\n",
       "   'nReturned': 1,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 1,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 1,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 1,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 2,\n",
       "    'advanced': 1,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'education.school_name': 1, 'education.education_type': -1},\n",
       "    'indexName': 'education.school_name_1_education.education_type_-1',\n",
       "    'isMultiKey': True,\n",
       "    'multiKeyPaths': {'education.school_name': ['education'],\n",
       "     'education.education_type': ['education']},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'education.school_name': ['[\"bnps\", \"bnps\"]'],\n",
       "     'education.education_type': ['[MaxKey, MinKey]']},\n",
       "    'keysExamined': 1,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 1,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.find({'education.school_name':'bnps',\n",
    "                  'education.education_type':'12th Standard'}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'$and': [{'education.education_type': {'$eq': '12th Standard'}},\n",
       "    {'education.school_name': {'$eq': 'bnps'}},\n",
       "    {'education.year': {'$eq': 2014}}]},\n",
       "  'winningPlan': {'stage': 'FETCH',\n",
       "   'filter': {'$and': [{'education.education_type': {'$eq': '12th Standard'}},\n",
       "     {'education.year': {'$eq': 2014}}]},\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'keyPattern': {'education.school_name': 1, 'education.education_type': -1},\n",
       "    'indexName': 'education.school_name_1_education.education_type_-1',\n",
       "    'isMultiKey': True,\n",
       "    'multiKeyPaths': {'education.school_name': ['education'],\n",
       "     'education.education_type': ['education']},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'education.school_name': ['[\"bnps\", \"bnps\"]'],\n",
       "     'education.education_type': ['[MaxKey, MinKey]']}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 1,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 1,\n",
       "  'totalDocsExamined': 1,\n",
       "  'executionStages': {'stage': 'FETCH',\n",
       "   'filter': {'$and': [{'education.education_type': {'$eq': '12th Standard'}},\n",
       "     {'education.year': {'$eq': 2014}}]},\n",
       "   'nReturned': 1,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 2,\n",
       "   'advanced': 1,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'docsExamined': 1,\n",
       "   'alreadyHasObj': 0,\n",
       "   'inputStage': {'stage': 'IXSCAN',\n",
       "    'nReturned': 1,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 2,\n",
       "    'advanced': 1,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'keyPattern': {'education.school_name': 1, 'education.education_type': -1},\n",
       "    'indexName': 'education.school_name_1_education.education_type_-1',\n",
       "    'isMultiKey': True,\n",
       "    'multiKeyPaths': {'education.school_name': ['education'],\n",
       "     'education.education_type': ['education']},\n",
       "    'isUnique': False,\n",
       "    'isSparse': False,\n",
       "    'isPartial': False,\n",
       "    'indexVersion': 2,\n",
       "    'direction': 'forward',\n",
       "    'indexBounds': {'education.school_name': ['[\"bnps\", \"bnps\"]'],\n",
       "     'education.education_type': ['[MaxKey, MinKey]']},\n",
       "    'keysExamined': 1,\n",
       "    'seeks': 1,\n",
       "    'dupsTested': 1,\n",
       "    'dupsDropped': 0,\n",
       "    'seenInvalidated': 0}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.find({'education.school_name':'bnps',\n",
    "                  'education.education_type':'12th Standard',\n",
    "                  'education.year':2014}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.students.drop_index('hobbies_1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'hobbies_text'"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Text index\n",
    "# index created on text field\n",
    "# there can be only one text index per collection\n",
    "# Multiple fields can be there in single text index\n",
    "# Can be created on string value field or an array of string values\n",
    "# Beforehand we mention text to let the mongodb know that its a text index\n",
    "db.students.create_index([('hobbies','text')])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [],
   "source": [
    "op=db.students.find({'$text':{'$search':'Reading'}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies']}\n",
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n",
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'queryPlanner': {'plannerVersion': 1,\n",
       "  'namespace': 'students_details.students',\n",
       "  'indexFilterSet': False,\n",
       "  'parsedQuery': {'$text': {'$search': 'Reading',\n",
       "    '$language': 'english',\n",
       "    '$caseSensitive': False,\n",
       "    '$diacriticSensitive': False}},\n",
       "  'winningPlan': {'stage': 'TEXT',\n",
       "   'indexPrefix': {},\n",
       "   'indexName': 'hobbies_text',\n",
       "   'parsedTextQuery': {'terms': ['read'],\n",
       "    'negatedTerms': [],\n",
       "    'phrases': [],\n",
       "    'negatedPhrases': []},\n",
       "   'textIndexVersion': 3,\n",
       "   'inputStage': {'stage': 'TEXT_MATCH',\n",
       "    'inputStage': {'stage': 'FETCH',\n",
       "     'inputStage': {'stage': 'OR',\n",
       "      'inputStage': {'stage': 'IXSCAN',\n",
       "       'keyPattern': {'_fts': 'text', '_ftsx': 1},\n",
       "       'indexName': 'hobbies_text',\n",
       "       'isMultiKey': True,\n",
       "       'isUnique': False,\n",
       "       'isSparse': False,\n",
       "       'isPartial': False,\n",
       "       'indexVersion': 2,\n",
       "       'direction': 'backward',\n",
       "       'indexBounds': {}}}}}},\n",
       "  'rejectedPlans': []},\n",
       " 'executionStats': {'executionSuccess': True,\n",
       "  'nReturned': 3,\n",
       "  'executionTimeMillis': 0,\n",
       "  'totalKeysExamined': 3,\n",
       "  'totalDocsExamined': 3,\n",
       "  'executionStages': {'stage': 'TEXT',\n",
       "   'nReturned': 3,\n",
       "   'executionTimeMillisEstimate': 0,\n",
       "   'works': 4,\n",
       "   'advanced': 3,\n",
       "   'needTime': 0,\n",
       "   'needYield': 0,\n",
       "   'saveState': 0,\n",
       "   'restoreState': 0,\n",
       "   'isEOF': 1,\n",
       "   'invalidates': 0,\n",
       "   'indexPrefix': {},\n",
       "   'indexName': 'hobbies_text',\n",
       "   'parsedTextQuery': {'terms': ['read'],\n",
       "    'negatedTerms': [],\n",
       "    'phrases': [],\n",
       "    'negatedPhrases': []},\n",
       "   'textIndexVersion': 3,\n",
       "   'inputStage': {'stage': 'TEXT_MATCH',\n",
       "    'nReturned': 3,\n",
       "    'executionTimeMillisEstimate': 0,\n",
       "    'works': 4,\n",
       "    'advanced': 3,\n",
       "    'needTime': 0,\n",
       "    'needYield': 0,\n",
       "    'saveState': 0,\n",
       "    'restoreState': 0,\n",
       "    'isEOF': 1,\n",
       "    'invalidates': 0,\n",
       "    'docsRejected': 0,\n",
       "    'inputStage': {'stage': 'FETCH',\n",
       "     'nReturned': 3,\n",
       "     'executionTimeMillisEstimate': 0,\n",
       "     'works': 4,\n",
       "     'advanced': 3,\n",
       "     'needTime': 0,\n",
       "     'needYield': 0,\n",
       "     'saveState': 0,\n",
       "     'restoreState': 0,\n",
       "     'isEOF': 1,\n",
       "     'invalidates': 0,\n",
       "     'docsExamined': 3,\n",
       "     'alreadyHasObj': 0,\n",
       "     'inputStage': {'stage': 'OR',\n",
       "      'nReturned': 3,\n",
       "      'executionTimeMillisEstimate': 0,\n",
       "      'works': 4,\n",
       "      'advanced': 3,\n",
       "      'needTime': 0,\n",
       "      'needYield': 0,\n",
       "      'saveState': 0,\n",
       "      'restoreState': 0,\n",
       "      'isEOF': 1,\n",
       "      'invalidates': 0,\n",
       "      'dupsTested': 3,\n",
       "      'dupsDropped': 0,\n",
       "      'recordIdsForgotten': 0,\n",
       "      'inputStage': {'stage': 'IXSCAN',\n",
       "       'nReturned': 3,\n",
       "       'executionTimeMillisEstimate': 0,\n",
       "       'works': 4,\n",
       "       'advanced': 3,\n",
       "       'needTime': 0,\n",
       "       'needYield': 0,\n",
       "       'saveState': 0,\n",
       "       'restoreState': 0,\n",
       "       'isEOF': 1,\n",
       "       'invalidates': 0,\n",
       "       'keyPattern': {'_fts': 'text', '_ftsx': 1},\n",
       "       'indexName': 'hobbies_text',\n",
       "       'isMultiKey': True,\n",
       "       'isUnique': False,\n",
       "       'isSparse': False,\n",
       "       'isPartial': False,\n",
       "       'indexVersion': 2,\n",
       "       'direction': 'backward',\n",
       "       'indexBounds': {},\n",
       "       'keysExamined': 3,\n",
       "       'seeks': 1,\n",
       "       'dupsTested': 3,\n",
       "       'dupsDropped': 0,\n",
       "       'seenInvalidated': 0}}}}},\n",
       "  'allPlansExecution': []},\n",
       " 'serverInfo': {'host': 'ebd1ea749913',\n",
       "  'port': 27017,\n",
       "  'version': '3.6.8',\n",
       "  'gitVersion': '8e540c0b6db93ce994cc548f000900bdc740f80a'},\n",
       " 'ok': 1.0}"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.students.find({'$text':{'$search':'Reading'}}).explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "op=db.students.find({'$text':{'$search':'Reading -Cricket'}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies']}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "op=db.students.find({'$text':{'$search':'Reading'}},\n",
    "                     {'score':{'$meta':'textScore'} })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'score': 0.75}\n",
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}], 'score': 0.75}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies'], 'score': 1.0}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Collection(Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'students_details'), 'nonfiction')"
      ]
     },
     "execution_count": 241,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.create_collection(name = 'nonfiction',\n",
    "validator={\n",
    "            '$jsonSchema': {\n",
    "                'bsonType': \"object\",\n",
    "                'required':['name','price'],\n",
    "                'properties':{\n",
    "                    'name':{\n",
    "                        'bsonType':'string',\n",
    "                        'description':'required'\n",
    "                    },\n",
    "                    'price':{\n",
    "                        'bsonType':'number',\n",
    "                        'description':'required'\n",
    "                    }\n",
    "\n",
    "                }\n",
    "            }\n",
    "\n",
    "        },  \n",
    "    validationAction='error'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertOneResult at 0x7f9763994880>"
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.nonfiction.insert_one({'name':'Naman', 'price':100})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Collection(Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'students_details'), 'users')"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.create_collection(name=\"users\",\n",
    "                             validator={\"$jsonSchema\": {\n",
    "                                 \"bsonType\": \"object\",\n",
    "                                 \"required\": [\"username\"],\n",
    "                                 \"properties\": {\n",
    "                                     \"username\": {\n",
    "                                         \"bsonType\": \"string\",\n",
    "                                         \"pattern\": \"[a-z0-9]{5,15}\",\n",
    "                                         \"description\": \"user name (required), only lowercase letters \"\n",
    "                                                        \"and digits allowed, from 5 to 15 characters long\"\n",
    "                                     },\n",
    "                                     \"email\": {\n",
    "                                         \"bsonType\": \"string\",\n",
    "                                         \"description\": \"User's email (optional)\"\n",
    "                                     },\n",
    "                                 }\n",
    "                             }},\n",
    "                             validationAction=\"error\",\n",
    "                             )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.nonfiction.drop()\n",
    "db.users.drop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertOneResult at 0x7f97639946a0>"
      ]
     },
     "execution_count": 244,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write concern\n",
    "db.students.insert_one({\n",
    "    'name':'knaman',\n",
    "    'gender':'female'\n",
    "},\n",
    "{\n",
    "    'writeConcern':{'w':3}\n",
    "}\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [],
   "source": [
    "# notin and in operator \n",
    "\n",
    "o=db.students.find({'hobbies':{'$in':['Reading books','Cricket']}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3142'), 'name': 'kenjal', 'gender': 'female', 'state': 'krnk', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Shopping', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3144'), 'name': 'sid', 'gender': 'male', 'state': 'up', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Gaming', 'Cricket', 'Football']}\n",
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n"
     ]
    }
   ],
   "source": [
    "for i in o:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [],
   "source": [
    "# notin and in operator \n",
    "\n",
    "o=db.students.find({'hobbies':{'$nin':['Reading books','Cricket']}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d1f9917a7edc19f313e'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647d9d3a9917a7edc19f313f'), 'name': 'naman'}\n",
      "{'_id': ObjectId('647d9d3c9917a7edc19f3140'), 'name': 'preeti', 'gender': 'female'}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies']}\n",
      "{'_id': ObjectId('647dbde89917a7edc19f3152'), 'name': 'knaman', 'gender': 'female'}\n"
     ]
    }
   ],
   "source": [
    "for i in o:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Logical operators\n",
    "\n",
    "op=db.students.find({'$or':[{'phy':{'$lt':56}}, {'maths':{'$gt':56}}] })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d1f9917a7edc19f313e'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3142'), 'name': 'kenjal', 'gender': 'female', 'state': 'krnk', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Shopping', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3144'), 'name': 'sid', 'gender': 'male', 'state': 'up', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Gaming', 'Cricket', 'Football']}\n",
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [],
   "source": [
    "# and operator\n",
    "\n",
    "op=db.students.find({\n",
    "    '$and':[\n",
    "        \n",
    "        {\"hobbies\":'Reading books'},\n",
    "        {'hobbies':'Cricket'}\n",
    "    ]\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n",
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [],
   "source": [
    "# $nor operator\n",
    "\n",
    "# Logical operators\n",
    "\n",
    "op=db.students.find({'$nor':[{'phy':{'$lt':56}}, {'maths':{'$gt':56}}] })\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d3a9917a7edc19f313f'), 'name': 'naman'}\n",
      "{'_id': ObjectId('647d9d3c9917a7edc19f3140'), 'name': 'preeti', 'gender': 'female'}\n",
      "{'_id': ObjectId('647dbde89917a7edc19f3152'), 'name': 'knaman', 'gender': 'female'}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Logical operators\n",
    "\n",
    "op=db.students.find(\n",
    "    {'hobbies':{\n",
    "        '$not':{'$in':['Reading books']}\n",
    "    }}\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d1f9917a7edc19f313e'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647d9d3a9917a7edc19f313f'), 'name': 'naman'}\n",
      "{'_id': ObjectId('647d9d3c9917a7edc19f3140'), 'name': 'preeti', 'gender': 'female'}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3142'), 'name': 'kenjal', 'gender': 'female', 'state': 'krnk', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Shopping', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3144'), 'name': 'sid', 'gender': 'male', 'state': 'up', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Gaming', 'Cricket', 'Football']}\n",
      "{'_id': ObjectId('647dbde89917a7edc19f3152'), 'name': 'knaman', 'gender': 'female'}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Element operators \n",
    "# exists\n",
    "# This operator for the existence of the fields in the documents if they exist then they will be shown in the result\n",
    "\n",
    "op=db.students.find(\n",
    "    {\n",
    "        'hobbies':{'$exists':True}\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3142'), 'name': 'kenjal', 'gender': 'female', 'state': 'krnk', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Shopping', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3144'), 'name': 'sid', 'gender': 'male', 'state': 'up', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Gaming', 'Cricket', 'Football']}\n",
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combining more than one filter\n",
    "\n",
    "# Element operators \n",
    "# exists\n",
    "# This operator for the existence of the fields in the documents if they exist then they will be shown in the result\n",
    "\n",
    "op=db.students.find(\n",
    "    {\n",
    "        'hobbies':{'$exists':True,'$nin':['Cricket','Football'] }\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3142'), 'name': 'kenjal', 'gender': 'female', 'state': 'krnk', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Shopping', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3144'), 'name': 'sid', 'gender': 'male', 'state': 'up', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Gaming', 'Cricket', 'Football']}\n",
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combining more than one filter\n",
    "\n",
    "# Element operators \n",
    "# exists\n",
    "# This operator for the existence of the fields in the documents if they exist then they will be shown in the result\n",
    "\n",
    "op=db.students.find(\n",
    "    {\n",
    "        'hobbies':{'$exists':True,'$nin':['Cricket','Football'] }\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies']}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {},
   "outputs": [],
   "source": [
    "op=db.students.find(\n",
    "    {\n",
    "        'hobbies':{\n",
    "            '$exists':True,\n",
    "            '$type':'array'\n",
    "        }\n",
    "\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3142'), 'name': 'kenjal', 'gender': 'female', 'state': 'krnk', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Shopping', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3144'), 'name': 'sid', 'gender': 'male', 'state': 'up', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Gaming', 'Cricket', 'Football']}\n",
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "metadata": {},
   "outputs": [],
   "source": [
    "i=db.students.find({'$expr':{'$gt':['$phy','$maths']}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3142'), 'name': 'kenjal', 'gender': 'female', 'state': 'krnk', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Shopping', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3144'), 'name': 'sid', 'gender': 'male', 'state': 'up', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Gaming', 'Cricket', 'Football']}\n",
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n"
     ]
    }
   ],
   "source": [
    "for i in i:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "metadata": {},
   "outputs": [],
   "source": [
    "i=db.students.find({'$expr':{'$gte':['$phy',{'$avg':'$phy'}]}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647d9d1f9917a7edc19f313e'), 'name': 'sanjay', 'gender': 'male', 'state': 'mp', 'phy': 34, 'maths': 56, 'bio': 89}\n",
      "{'_id': ObjectId('647da5dc9917a7edc19f3141'), 'name': 'naman', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3142'), 'name': 'kenjal', 'gender': 'female', 'state': 'krnk', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Shopping', 'Cricket']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3143'), 'name': 'nirjara', 'gender': 'female', 'state': 'delhi', 'phy': 78, 'maths': 90, 'bio': 98, 'hobbies': ['Reading', 'Watching Movies']}\n",
      "{'_id': ObjectId('647da9679917a7edc19f3144'), 'name': 'sid', 'gender': 'male', 'state': 'up', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Gaming', 'Cricket', 'Football']}\n",
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n"
     ]
    }
   ],
   "source": [
    "for i in i:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 300,
   "metadata": {},
   "outputs": [],
   "source": [
    "op=db.students.find({'education.year':2014})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('647dad329917a7edc19f3145'), 'name': 'jitin', 'gender': 'male', 'state': 'Delhi', 'phy': 89, 'maths': 78, 'bio': 97, 'hobbies': ['Reading books', 'Football', 'Cricket'], 'education': [{'year': 2014, 'school_name': 'bnps', 'graduation_year': 2015, 'education_type': '12th Standard'}, {'year': 2016, 'school_name': 'dtu', 'graduation_year': 2020, 'education_type': 'B.Tech'}]}\n"
     ]
    }
   ],
   "source": [
    "for i in op:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.10 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "e7370f93d1d0cde622a1f8e1c04877d8463912d04d973331ad4851f04de6915a"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}