MongoDB Query Tricks for Getting the Information You Need

Posted on by
Contents hide
1 Introduction
2 Prerequisites
3 Examples
4 Conclusion

Introduction

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.

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

Examples

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'

“Or”

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

MongoDb SQL tricks Highest Date Example

Highest Date Example

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

MongoDb SQL tricks order by

Sorting Orders Example

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')

MongoDb SQL tricks tags in

Matching Elements Example

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')

MongoDb SQL tricks tags not in

Excluding Elements Example

Conclusion

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.

Posted in Uncategorized.