Introduction
ZappySys provides high performance drag and drop connectors for MongoDB Integration. In our previous post we discussed how to query/load MongoDB data (Insert, Update, Delete, Upsert). In this post you will see how to query MongoDB by date (or ISODate) using SSIS MongoDB Source. To see full detail about possible query syntax see online help.
MongoDB doesn’t have SQL Like query language so sometime you may feel odd…But has native JSON like Query language (Sorry SQL Guys.. )
No worry if you stumble upon how to query various things in MongoDB.. Its different but not too hard to learn.
MongoDB Query Example
Assume that you have following Document stored in MongoDB Collection. You like to query those orders where Order date is greater than 2015-01-01. Here are some examples of MongoDB Query used by ZappySys SSIS MongoDB Source Connector . For more information about MongoDB Query check MongoDB Query Syntax documentation. Important thing is your attribute must be stored as ISODate (not string). See below JSON document, notice how OrderDate is saved as ISODate( … ). This will allow Query to use any standard comparison operator (e.g. =, > , <, != ) for date search. If your data is not stored as ISODate then refer online blogs/help how to write update scripts to change/convert string date into ISODate.
|
1 2 3 4 5 6 |
{ "OrderID" : 100, "OrderDate" : ISODate("2015-02-28T00:00:00Z"), "Country" : "USA", "Total" : 5000 } |
How to Query MongoDB ISODate Attribute
If you want to query ISODate attribute then you may scratch your head how to do. There are two ways to do
Using SQL Query syntax (Only available in ZappySys SSIS MongoDB Source)
|
1 |
select * from Orders where OrderDate > 'DATE(2015-01-01)' |
Using SSIS Variable in Query
You can also use SSIS Variable to make your query dynamic. Variable placeholder supports datetime format specifiers too. E.g. for ISO date with 3 digit millisecond precision you may use yyyy-MM-ddTHH:mm:ss.fffZ format as below.
|
1 |
select * from Orders where OrderDate > 'DATE({{User::MyDateVar,yyyy-MM-ddTHH:mm:ss.fffZ}})' |
Using ISODate syntax (SSIS MongoDB Source – Native Query)
|
1 2 3 4 |
{Table: Orders } { "OrderDate" : { "$lt" : new ISODate("2015-01-02T00:16:15.184Z")} } |
Using $date syntax (SSIS MongoDB Source – Native Query)
|
1 2 3 4 |
{Table: Orders } { "OrderDate" : { "$gt" : { "$date" : "2015-01-01"}} } |
Remember to suffix “Z” in your date format to indicate its UTC date
MongoDB stores datetime as UTC (To confirm this edit Mongo document and you will see Z at the end in ISODate( …. ) value). When you use ZappySys MongoDB SQL Query Syntax like below it may or many not perform Automatic Date Conversion from Local to UTC. If you suffix “Z” after date/time value then its treated as UTC and no DateTime conversion happens. Date without Z at the end is treated as Local Date / Time so its converted to UTC before passing to MongoDB.
If you use JSON Style Native Query Syntax then no date conversion is performed by ZappySys.
Query-1: Date supplied as UTC (Suffixed with “Z”) – Date is passed as is to Mongo engine
|
1 2 |
select * from Orders where OrderDate > 'DATE({{User::MyDateVar,yyyy-MM-ddTHH:mm:ss.fffZ}})' select * from Orders where OrderDate > 'DATE({{User::MyDateVar,yyyy-MM-ddZ}})' |
Query-2: Date supplied as Local Time (No “Z” at the end) – Date is Converted to UTC and then passed to Mongo Engine
|
1 2 |
select * from Orders where OrderDate > 'DATE({{User::MyDateVar,yyyy-MM-ddTHH:mm:ss.fff}})' select * from Orders where OrderDate > 'DATE({{User::MyDateVar,yyyy-MM-dd}})' |
MongoDB Query using SSIS MongoDB Source
ZappySys SSIS MongoDB Source supports querying nested Documents using JSON Path expressions (e.g. Extract order Items from Order Document using $.Items expression)
Example MongoDB Document
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
{ "_id" : ObjectId("564bb3bea1e57e043363f1ea"), "OrderID" : 111, "OrderDate" : ISODate("2022-01-01T00:00:00.000Z"), "AuditInfo" : { "User" : "bob", "CDate" : ISODate("2015-01-01T00:00:00.000Z") }, "Items" : [ { "ProductID" : 100, "Qty" : 3 }, { "ProductID" : 101, "Qty" : 2 } ] } |
MongoDB Source – SQL style MongoDB Query (filter by date)
SSIS MongoDB Source – MongoDB SQL Query – Filter by date (Query ISODate)
MongoDB Source – Native MongoDB Query (filter by date)
Query MongoDB by Date Attribute (Query ISODate) using SSIS MongoDB Source
Dynamic MongoDB Query using Placeholders
MongoDB Source in SSIS PowerPack Supports using placeholders to make query dynamic you can use Query Placeholders like below (See Insert Placeholder menu)
Dynamic MongoDB Query – Use SSIS variable placeholder
SQL Like Query Language using SSIS PowerPack
SSIS PowerPack has SSIS MongoDB Source which supports SQL Like query language for MongoDB. It automatically converts SQL Query to correct MongoDB JSON Query syntax. See below screenshot.
MongoDB SQL Query Language – SSIS MongoDB Source
Download FREE SSIS Components
