MongoDB Query Examples

After importing json file 

Mongodb

show dbs

use city

--------------------------------------------- 1.Insert --------------------------------------------- Q- Insert the recored in collection "inspections" where "id" : "10021-2015-ENFP", "certificate_number" : 9278809, "business_name" : "Codersarts", "date" : "Feb 20 2019", "result" : "No Violation Issued", "sector" : "Cigarette Retail Dealer - 127".

> db.inspections.insert( {"id" : "10021-2015-ENFM",
"certificate_number" : 9278810,
"business_name" : "Sofstack",
"date" : "Feb 20 2019",
"result" : "No Violation Issued",
"sector" : "Noida 63",
} );

Output look like: WriteResult({ "nInserted" : 1 })

-----------------------------------------------

2. Remove(delete) -----------------------------------------------

db.inspections.remove( {"id" : "10021-2015-ENFP",
"certificate_number" : 9278809,
"business_name" : "Codersarts",
"date" : "Feb 20 2019",
"result" : "No Violation Issued",
"sector" : "Cigarette Retail Dealer - 127", 
} );

Output look like: WriteResult({ "nRemoved" : 1 })

------------------------------------------------- 3. Remove(delete) by given criteria(codition) -------------------------------------------------

> db.inspections.find({})

>criteria = {"result" : "pass"}

output: { "result" :"No Violation Issued"}

>db.inspections.find(criteria)

>db.inspections.remove(criteria)

------------------------------------------------------------------ if you want to delete only one document of given crieteria then use -------------------------------------------------------------------

> criteria={"result":"No Violation Issued"}
{ "result" : "No Violation Issued"}

> db.inspections.find(criteria).count()
3795

> db.inspections.remove(criteria,1)
output:
WriteResult({ "nRemoved" : 1 })

--------------------------------------------------------------------------- 4. Update: ---------------------------------------------------------------------------

> db.inspections.find({})

>db.inspections.updateMany(
{ "result":"Pass"},
   { $set: { "result": "Violation Issued" } }
)

----------------------------------------------------- 5- show column data by colum name: ------------------------------------------------------ Display city India which exist in address column

>criteria = {"address.city" : "India"}

output: { "address.city" :"India"}

>db.inspections.find(criteria)

---------------------------------------------------------------------------------------------------------- MongoDB - Projection Queries ---------------------------------------------------------------------------------------------------------- Projection: A projection query is a query where you specify which fields should be returned. You can do this by including the field names in your query, and adding a 1 or 0 next to them, to specify whether it should be returned or not. This is a projection parameter. A projection parameter of 1 will display the field and a 0 will hide it. Example First let's do a query without projection (so we can see how many fields are returned): Without Projection:

>db.inspections.find( {"result": "Violation Issued"} )

Result: With Projection Now, let's use projection to display just the name field:

db.inspections.find( { "result": "Violation Issued" }, { "_id":0,"date": 1 ,"result":1} )

Result: You will notice that the _id field is automatically included, even if you don't specify it. You can exclude this field by using a 0 against it:

db.inspections.find( { "result": "Violation Issued" }, { _id: 0, "date": 1 } )

Result: You can't mix 1s(inclusion) and 0s(Exclusion)

db.inspections.find( { "result": "Violation Issued" }, { "date": 1, "sector":0 } )

You'll end up with this error: Error Result:

-------------------------------------------------------------------------------------------------- MongoDB - Limit the Results of a Query --------------------------------------------------------------------------------------------------

Return only the number of documents you need with the limit() method In MongoDB, you can use the limit() method to specify a maximum number of documents for a cursor to return. When you query a collection using the db.inspections.find() method, you can append limit() to specify the limit. Example First let's do a query without a limit (so we can see how many documents are returned): Without a Limit

db.inspections.find( { "result": "Violation Issued" }, { "date": 1 } )

Result: OK, so let's limit the results to say, 3 documents:

With a Limit

db.inspections.find( { "result": "Violation Issued" }, { "date": 1 } ).limit(3)

Add the skip() Method You can use the skip() method to skip to a document within the cursor

db.inspections.find( { "result": "Violation Issued" }, { "date": 1 } ).limit(3).skip(1)

Result: Skip first result and display next three result Note that skip() can be used on any query (not just ones with limit()). -------------------------------------------------------------------------------------- MongoDB - Sort the Results of a Query ---------------------------------------------------------------------------------------

The sort() method specifies a sort order for the cursor. It will typically contain one or more fields, each followed by either 1 or -1, depending on whether the sort should be in ascending or descending order. With sort() in Ascending Order A value of 1 next to a field name specifies that the documents should be sorted by the specified field in ascending order. Here we sort the results by name in ascending order:

db.inspections.find( ).sort( { "date": 1 } )

With sort() in Descending Order A value of -1 next to a field name specifies descending order. Here we sort the results by name in descending order:

db.inspections.find( ).sort( { "date": -1 } )

Multiple Fields

db.inspections.find( ).sort( { "result": 1, "date": -1 } )

Result: Sort with Limit

db.inspections.find( ).limit(3).sort( { "result": 1} )

MongoDB - Create a Relationship To create a relationship in MongoDB, either embed a BSON document within another, or reference it from another. One-to-One Relationship One-to-Many Relationship Querying the Relationship you can use $lookup to perform a left outer join on the two collections.This, in conjunction with the aggregate() method, and $match. Example:

db.collection1.aggregate([
    {
      $lookup:
        {
          from: "collection2",
          localField: "_id",
          foreignField: "artist_id",
          as: "band_members"
        }
   },
   { $match : { artistname : "Rush" } }
]).pretty()

Collection of employee

MongoDB shell version v4.0.9 connecting to: mongodb://127.0.0.1:27017/?gssapiServiceName=mongodb Implicit session: session { "id" : UUID("153d94df-eb08-41ff-8348-6e2790efc0bc") } MongoDB server version: 4.0.9 Server has startup warnings: 2019-06-08T17:41:59.265+0530 I CONTROL [initandlisten] 2019-06-08T17:41:59.265+0530 I CONTROL [initandlisten] ** WARNING: Access contr ol is not enabled for the database. 2019-06-08T17:41:59.265+0530 I CONTROL [initandlisten] ** Read and wri te access to data and configuration is unrestricted. 2019-06-08T17:41:59.265+0530 I CONTROL [initandlisten] --- Enable MongoDB's free cloud-based monitoring service, which will then receive an d display metrics about your deployment (disk utilization, CPU, operation statistics, etc) . The monitoring data will be available on a MongoDB website with a unique URL acc essible to you and anyone you share the URL with. MongoDB may use this information to make prod uct improvements and to suggest MongoDB products and deployment options to you. To enable free monitoring, run the following command: db.enableFreeMonitoring() To permanently disable this reminder, run the following command: db.disableFreeM onitoring()

> show dbs
MongoDB   0.005GB
admin     0.000GB
city      0.012GB
config    0.000GB
employee  0.000GB
local     0.000GB

> use employee
switched to db employee

> db.employee.find().pretty()
{
        "_id" : ObjectId("5cfa0ea5d547422e9da13839"),
        "no" : "1",
        "name" : "naveen",
        "salary" : 10000,
        "role" : "OB"
}
{
        "_id" : ObjectId("5cfa0ec5d547422e9da1383a"),
        "no" : "1",
        "name" : "jitendra",
        "salary" : 6500,
        "role" : "db"
}
{
        "_id" : ObjectId("5cfa16ffce2c8ca71875c757"),
        "no" : "2",
        "name" : "ak",
        "role" : "eng",
        "salary" : 5000
}
{
        "_id" : ObjectId("5cfa177dce2c8ca71875c75e"),
        "no" : "3",
        "name" : "bk",
        "role" : "data",
        "salary" : 12000
}
>

Group By

QUESTION: Example groups by the salary and no fields those documents that have SALARY greater than 6000.

>db.employee.group(
   {
     key: { "salary": 1, "no": 1 },
     cond: { "salary": { $gt: 6000 } },
     reduce: function ( curr, result ) { },
     initial: { }
   }
);

Group by two fields

Question: Example groups by the "salay" and "no" fields, those documents that have "no" field is equal to the "1" and calculates the sum of the salary field for each grouping:

>db.employee.group(
   {
     key: { "salary": 1, "no": 1 },
     cond: { "no": "1" },
     reduce: function ( curr, result ) {
                 result.total += curr.salary;
             },
     initial: { total : 0 }
   }
);

FIND RECORD WITH MATCHING WORD

Question:fIND DOCUMENT FROM COLLECTION "INSPECTIONS" IN WHICH "DATE" BEGINS WITH "M".

>db.inspections.find({"date":"/^M/"})

Question: FIND ALL DOCUMENTS FORM COLLECTION "INSPECTIONS" IN WHICH last digit of city end with "E" in address column.

>db.inspections.find({"address.city":"/$E/"})

Question:: FIND DOCUMENTS IN WHICH CITY HAVE "V" LETTER AT ANY POSSITION IN CITY IN ADDRESS

>db.inspections.find({"address.city":"/V/"})

Question:: INSERT DATA IN COLLECTIONS "EMPLOYEE" AND DISPLAY IN PROPER FORMATE USING PRETTY()

>db.employee.find().pretty()

Question:: INCREMENT SALARY OF EMPLOYEE "NAVEEN" OF COLLECTION "EMPLOYEE" BY 4000.

>db.employee.update({"name":"naveen"},{$inc:{"salary":4000}})

where $inc function use for increment salary

Question: INCREMENT ALL EMPLOYEE SALARY BY 4000

>db.employee.update({},{$inc:{"salary":4000}},{multi:true}})

Question: ADD NEW RECORD WITHOUT USING INSERT OPERATOR

>db.employee.update({"no":"3"},{$set:{"no":"3","name":"bk","salary":6000,"role":"data"}},{upsert:true})

Question: Remove docuement using "unset"

> db.employee.update({"name":"bk"},{$unset:{remark:"ek"}})

Question: MULTIPLY BY SALARY 2

>db.employee.update({"name":"naveen"},{$mul:{"salary":2}})