In this article, we will explore some MongoDB query tricks that can help you retrieve the information you require. Be sure to check out our articles on MongoDB source and MongoDB execute SQL task here.
PrerequisitesBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.
Filtering by Date: First, let’s look at filtering data by date. Ensure that your database has the date column in the same format. You can filter by date using either of these formats:
Select date from dataTable where date > '2019-08-17 10:42:20'
select date from dataTable where date > '2019/08/17 10:42:20'
Getting the Highest Date: If you need to retrieve the highest date in a column, you can use the ‘Order by’ command along with ‘top 1’. Use DESC for the highst result and ASC for the lowest result.
Select top 1 date from dataArray order by date DESC
Sorting Orders: The following SQL statement selects all orders from the ‘OrdersList’ table. It sorts the results in ascending order by the ‘AddressInfo.City’ and in descending order by the ‘stars’ column
SELECT * FROM OrdersList ORDER BY AddressInfo.City ASC, stars DESC
Matching Elements in an Array: In the next example, we are attempting to match elements inside an array
Select * from OrdersList where Tags in ('ca', 'aa')
Excluding Elements from an Array: You can also exclude elements that are not in the array
Select * from OrdersList where Tags NOT in ('ca', 'aa')
These are some examples that you can utilize with our SQL tool. If you want to explore more complex examples, you can refer to this article.