How to query MongoDB by date or ISODate

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.

 

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)

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.

Using ISODate syntax (SSIS MongoDB Source – Native Query)

Using $date syntax (SSIS MongoDB Source – Native Query)

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

Query-2: Date supplied as Local Time (No “Z” at the end) – Date is Converted to UTC and then passed to Mongo Engine

 

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

 

MongoDB Source – SQL style MongoDB Query (filter by date)

SSIS MongoDB Source - MongoDB SQL Query - Filter by date (Query 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

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

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

MongoDB SQL Query Language – SSIS MongoDB Source

 

Download FREE SSIS Components

Posted in SSIS MongoDB Source and tagged , .