Add day to ISODate in MongoDB

We can use $add an operator to add days in ISODate in MongoDB, $add is the Arithmetic Aggregation Operator, which adds number and date in MongoDB.

Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.

Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in the above note, we have to convert days in a millisecond because $add operator treat to another argument as milliseconds if one of the arguments is a date.

Add Minutes

We can add minute same as above, we need to convert minutes into milliseconds, see following query.

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 10*60*1000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-07T08:10:00.000Z") }

$not operator in MongoDB

Before explaining $not operator I want to remind you about NOT IN in SQL Server. In T-SQL query we used NOT operator like below.

<SELECT QUERY> <WHERE ID> NOT IN (<a sub query>)

Same as that $not operator behaves in MongoDB.

$not performs a logical NOT operation on the specified <operator-expression> and selects the documents that do not match the <operator-expression>. This includes documents that do not contain the field.

Syntax:

Syntax: { field: { $not: { <operator-expression> } } }

Example: Suppose we have a collection of books inventory like below.

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 } { "_id" : 3, "item" : "item1", "qty" : 4, "Price" : 300 } { "_id" : 4, "item" : "item1", "qty" : 8, "Price" : 700 } { "_id" : 5, "item" : "item1", "qty" : 2, "Price" : 500 }

See the following query

db.Order.find( { qty: { $not: { $gte: 4 } } } )

Result:

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 } { "_id" : 5, "item" : "item1", "qty" : 2, "Price" : 500 }

Note:

• The operation of the $not operator is consistent with the behavior of other operators but may yield unexpected results with some data types like arrays.
• The $not operator does not support operations with the $regex operator. Instead use // or in your driver interfaces, use your language’s regular expression capability to create regular expression objects.

For more details, you can follow this link https://docs.mongodb.com/manual/reference/operator/query/not/

Text Index in MongoDB

Text index supports text search queries on string content. We can any add fields which value is string or an array of string elements.

Text index is introduced in MongoDB version 3 but some key features added in MongoDB version 3.2, the new features are:

1-   Case sensitive: Previous versions of the text index are case insensitive for [A-z] only; i.e. case insensitive for non-diacritics Latin characters only. For all other characters, earlier versions of the text index treat them as distinct.

2-  Diacritic Insensitivity: There are no facility in the index to distinguish between characters that contain diacritic marks and no marks the corresponding item for example é, ê, and e.

3-  Tokenization Delimiters: In previous version text index uses the delimiters categorized under dash and hyphen. For example: if given a string "Il a dit qu'il «était le Meilleur joueur du monde»", the text index treats «, », and spaces as delimiters.

Create text index: Suppose we have a collection “Inventory of books”

{"_id":1, "Title":"title1", "Author":"author1", "Publisher":"publisher1", "Qty":10, "Price":20.02} {"_id":2, "Title":"title2", "Author":"author2", "Publisher":"publisher2", "Qty":12, "Price":26.00} {"_id":3, "Title":"title3", "Author":"author3", "Publisher":"publisher3", "Qty":14, "Price":20.07} {"_id":4, "Title":"title4", "Author":"author4", "Publisher":"publisher4", "Qty":11, "Price":243.08} {"_id":5, "Title":"title5", "Author":"author5", "Publisher":"publisher5", "Qty":14, "Price":202.00} {"_id":6, "Title":"title6", "Author":"author6", "Publisher":"publisher6", "Qty":16, "Price":102.02} {"_id":7, "Title":"title7", "Author":"author7", "Publisher":"publisher7", "Qty":13, "Price":130.02} {"_id":8, "Title":"title8", "Author":"author8", "Publisher":"publisher8", "Qty":15, "Price":233.02} {"_id":9, "Title":"title9", "Author":"author9", "Publisher":"publisher9", "Qty":16, "Price":244.02} {"_id":10, "Title":"title10", "Author":"author1", "Publisher":"publisher1", "Qty":17, "Price":250.02} {"_id":11, "Title":"title11", "Author":"author2", "Publisher":"publisher2", "Qty":13, "Price":260.02} {"_id":12, "Title":"title12", "Author":"author3", "Publisher":"publisher3", "Qty":13, "Price":267.02} {"_id":13, "Title":"title13", "Author":"author4", "Publisher":"publisher4", "Qty":12, "Price":288.02} {"_id":14, "Title":"title14", "Author":"author5", "Publisher":"publisher5", "Qty":14, "Price":286.02} {"_id":15, "Title":"title15", "Author":"author6", "Publisher":"publisher6", "Qty":16, "Price":50.02} {"_id":16, "Title":"title16", "Author":"author7", "Publisher":"publisher7", "Qty":17, "Price":60.02}

Create text index using the following query

db.books.createIndex( { Title: "text", Author: "text",Publisher:"text" } )

Result:

/* 1 */ {     "createdCollectionAutomatically" : false,     "numIndexesBefore" : 1,     "numIndexesAfter" : 2,     "ok" : 1.0 }

Note: A collection can have most on text index.

References site : https://docs.mongodb.com/manual/core/index-text/#overview

$and operator in MongoDB

$and: This operator works the same as AND in SQL Server. $and operator uses to perform a logical operation on two or more than two arrays and select the documents that satisfy all expression.

Syntax:

{ $and: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }

Example: Suppose we have a collection “Order” and want to select those records whose quantity is 2 and the price is 200. See the following collection.

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 } { "_id" : 3, "item" : "item1", "qty" : 4, "Price" : 300 } { "_id" : 4, "item" : "item1", "qty" : 8, "Price" : 700 } { "_id" : 5, "item" : "item1", "qty" : 2, "Price" : 500 }

Run the following query to get the expected result described above.

db.Order.find({$and:[{qty:2},{Price:200}]})

Result:

{ "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 }

$or operator in MongoDB

$or: If you are aware with SQL Server, $or operator behaves the same as OR in SQL Server. $or operator uses to perform logical OR operation on two or more than two arrays and its select the documents that satisfy at least one of the expression.

Syntax:

{ $or: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }

Example: Suppose we have a collection “Order” and want to select those records whose quantity is 1or price is 200. See the following collection.

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 } { "_id" : 3, "item" : "item1", "qty" : 4, "Price" : 300 } { "_id" : 4, "item" : "item1", "qty" : 8, "Price" : 700 } { "_id" : 5, "item" : "item1", "qty" : 2, "Price" : 500 }

Run the following query to get the exact result described above.

db.Order.find({$or:[{qty:1},{Price:200}]})

Result:

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 }

Drop and Disable trigger using query in SQL Server

Drop Trigger: We can drop trigger using following query.

Example:

USE [DB_NAME] GO DROP TRIGGER TGGR_NAME GO

Disable Trigger: You can disable/enable trigger using following query

Example:

--Disable trigger USE [DB_NAME] GO DISABLE TRIGGER TGGR_NAME ON TBL_NAME --Enable trigger USE [DB_NAME] GO ENABLE TRIGGER TGGR_NAME ON TBL_NAME

Case statement in SQL Server

SQL Server gives us facility to fetch or modify records on condition basis. Suppose we have an “Order” table and we need to fetch records with conditions if user order price is less than 200 Rs it means user is “silver”, if price is greater than 200Rs and less than 500Rs it means user is Gold same like this if price is greater than 500Rs it means user is “Platinum” .

We can easily solve this problem using SQL Server.

Example:

CREATE TABLE #Order( ID INT IDENTITY(1,1) PRIMARY KEY, USR_NAME VARCHAR(100), SKU VARCHAR(100), QTY INT, PRICE DECIMAL(18,2), CREATED_DATE DATETIME ) INSERT INTO #Order (USR_NAME,SKU,QTY,PRICE,CREATED_DATE) SELECT 'User1','9876543210',2,200,GETDATE() UNION ALL SELECT 'User2','9876543210',2,300,GETDATE() UNION ALL SELECT 'User3','9876543210',2,400,GETDATE() UNION ALL SELECT 'User4','9876543210',2,550,GETDATE() --Case statement SELECT USR_NAME,PRICE, (             CASE             WHEN PRICE<=200 THEN 'SILVER'             WHEN PRICE>200 AND PRICE<=500 THEN 'GOLDEN'             WHEN PRICE>500 THEN 'PLATINUM' ELSE 'NULL' END  ) AS USER_TYPE FROM #Order DROP TABLE #Order

 The basic formulations for CASE Expression

Simple Case Expression: Simple case expressions check one expression against multiple values. Simple case expressions allow only an equality check.

Example:

DECLARE @value INT=2 SELECT CASE @value WHEN 1 THEN 'ONE'                                                    WHEN 2 THEN 'TWO'                                                    WHEN 3 THEN 'THREE'                                                    ELSE 'NOT MATCH'                                                    END

Searched Case Expression: Searched Case Expression allows comparison operators. The simple CASE expression checks only for equivalent values and can’t contain Boolean expressions.

Example:

DECLARE @value INT=20 SELECT CASE WHEN @value<20 THEN 'SILVER'                      WHEN @value>=20 AND @value<=50 THEN 'GOLDEN'                      WHEN @value >50 THEN 'PLATINUM'                      END

Find all stored procedures related to table in database in SQL Server

There are many different ways to find all stored procedures related to the specific table, some are described below.

Using sp_depends: This is the system stored procedure which can use to find all users defined stored procedures related to specific table.

USE[DB_NAME] GO EXEC sp_depends @objname = N'TBL_NAME' ; GO

Note: sp_depends does not always return accurate results.

See following script this will help you to find the stored procedures related to the specific table.

----Script 1 SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%' ----Script 2 SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '%tablename%'

Referral sites: http://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure/ 

$in and $nin operator in MongoDB

$in operator is responsible to select those records where the value of the fields is equal to any elements specified in the array.

Same as $nin operator is responsible to select those records where the value of the field is not equal to any elements specified in the array.

Syntax:

{ field: { $in: [<value1>, <value2>, ... <valueN> ] } }

{ field: { $nin: [ <value1>, <value2> ... <valueN> ]} }

Example: Suppose we have a collection “order” and we have to select those records which quantity is 2, 4(we will use here $in operator) same as if we want to select those records which quantity is not equals to 2, 4 (we will use here $nin operator).

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 } { "_id" : 3, "item" : "item1", "qty" : 4, "Price" : 300 } { "_id" : 4, "item" : "item1", "qty" : 8, "Price" : 700 } { "_id" : 5, "item" : "item1", "qty" : 2, "Price" : 500 }

Query:

//for $in db.Order.find({qty:{$in:[2,4]}})

//for $nin db.Order.find({qty:{$nin:[2,4]}})

Results:

For $in

{ "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 } { "_id" : 3, "item" : "item1", "qty" : 4, "Price" : 300 } { "_id" : 5, "item" : "item1", "qty" : 2, "Price" : 500 }

For $nin

{ "_id" : 1, "item" : "item1", "qty" : 1, "Price" : 500 } { "_id" : 4, "item" : "item1", "qty" : 8, "Price" : 700 }