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 the SSIS MongoDB Source. To see full details 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. It’s different but not too hard to learn.
MongoDB Query Example
Assume that you have the following Document stored in a MongoDB Collection. You want to query orders where the Order date is greater than a specific date. Here are some examples of MongoDB queries used by ZappySys SSIS MongoDB Source Connector. For more information about MongoDB Query, check the MongoDB Query Syntax documentation. An important thing is that your attribute must be stored as ISODate (not string). See the JSON document below; 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, refer to online blogs/help on how to write update scripts to convert string dates to 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 the ISODate attribute, you may scratch your head about how to do it. 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 an SSIS Variable to make your query dynamic. Variable placeholder supports datetime format specifiers too. E.g., for an 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 values in UTC (to confirm this, edit a Mongo document and you will see a Z at the end in the ISODate(….) value). When you use ZappySys MongoDB SQL Query Syntax like below, it may or may not perform Automatic Date Conversion from Local to UTC. If you append “Z” to a date/time value, it’s treated as UTC, and no DateTime conversion occurs. Dates without a Z at the end are treated as Local Date/Time, so they’re converted to UTC before being passed to MongoDB.
If you use JSON Style Native Query Syntax, ZappySys does not perform any date conversion.
Query-1: Date supplied as UTC (Suffixed with “Z”) – Date is passed as is to the 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 the 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)
Use the DATE function to convert a string value to an ISODate
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 placeholders to make queries 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 includes an SSIS MongoDB Source that supports a SQL-like query language for MongoDB. It automatically converts SQL queries to correct MongoDB JSON Query syntax. See the screenshot below. Here you can write an SQL Query. If you want to use a nested array element as a column name, you must use arrayname.elementname, e.g., LocationDetails.Country
MongoDB SQL Query Language – SSIS MongoDB Source
Download FREE SSIS Components
