DEV Community: Utsav Upadhyay

Enhance Your Search Capabilities with MongoDB Atlas: Advanced Querying Techniques

Utsav Upadhyay — Sun, 02 Jul 2023 18:09:43 +0000

Welcome back to the second part of our blog series on MongoDB Atlas Full-Text Search. In the first part, "From Indexing to Querying: How MongoDB's Full-Text Search Stacks Up Against Elasticsearch," we explored the fundamentals of MongoDB's full-text search and compared it to Elasticsearch. If you haven't read it yet, we highly recommend starting there to get a comprehensive understanding of MongoDB's full-text search capabilities. In this blog post, we will dive deeper into MongoDB Atlas Full-Text Search and explore advanced querying techniques that will enable you to take full advantage of its powerful features.

Understanding Compound Queries.

Compound queries are very important and powerful when it comes to real-world use cases.
A compound operator in MongoDB combines multiple operators to form a single query. Each part of the compound query is referred to as a clause, and each clause contains one or more sub-queries.

A compound query has the following syntax:

{
  $search: {
    "index": <index name>, // optional, defaults to "default"
    "compound": {
      <must | mustNot | should | filter>: [ { <clauses> } ],
      "score": <options>
    }
  }
}

Explanation:

Each must, mustNot, should, and filter clause contains an array of subclauses. Use array syntax even if the array contains only one subclause.

We required the following terms to construct the query, which we also explained above -

Document for explaining all the queries -

{
  "_id" : 1,
  "type" : "apple",
  "description" : "Apples come in several varieties, including Fuji, Granny Smith, and Honeycrisp.",
  "category" : "nonorganic",
  "in_stock" : false
},
{
  "_id" : 2,
  "type" : "banana",
  "description" : "Bananas are usually sold in bunches of five or six.",
  "category" : "nonorganic",
  "in_stock" : true
},
{
  "_id" : 3,
  "type" : "pear",
  "description" : "Bosc and Bartlett are the most common varieties of pears.",
  "category" : "organic",
  "in_stock" : true
}

Operators -

must Clauses that must match for a document to be included in the results. The returned score is the sum of the scores of all the subqueries in the clause.

Maps to the AND boolean operator.

Example:

db.col.aggregate([
  {
    $search: {
      "compound": {
        "must": [{
          "text": {
            "query": "varieties",
            "path": "description"
          }
        }]
      }
    }
  }
])

Explanation :

In the above query we are searching for the term Varieties in the field description which is called path in the query, and this query will return only those document which contains this - Varieties term in the field - description.

mustNot Clauses that must not match for a document to be included in the results. mustNot clauses don't contribute to a returned document's score.

Maps to the AND NOT boolean operator.

Example:

db.col.aggregate([
  {
    $search: {
      "compound": {
        "mustNot": [{
          "text": {
            "query": "apples",
            "path": "description"
          }
        }]
      }
    }
  }
])

Explanation :

In the above query we are searching for the data which do not contains apples in the field description, so this query will return only those documents which do not have apples in the field description.

should Clauses that you prefer to match in documents that are included in the results. Documents that contain a match for a should clause have higher scores than documents that don't contain a should clause. The returned score is the sum of the scores of all the subqueries in the clause.

If you use more than one should clause, you can use the minimumShouldMatch option to specify a minimum number of should clauses that must match to include a document in the results. If omitted, the minimumShouldMatch option defaults to 0.

Maps to the OR boolean operator.

Example:

db.col.aggregate([
  {
    $search: {
      "compound": {
        "Should": [{
          "text": {
            "query": "apples",
            "path": "description"
          }
        }]
      }
    }
  }
])

Explanation :

In the above query we are searching for the data which contains apples in the field description, so this query will return those documents which have apples in the field description.

for a better understanding of this Should operator which is the OR operator also, here is a second example -

db.col.aggregate([
  {
    $search: {
      "compound": {
        "Should": [{
          "text": {
            "query": "apples",
            "path": "description"
          },
          "text": {
            "query": "Banana",
            "path": "description"
          }
        }],
          "minimumShouldMatch": 1
      }
    }
  }
])

Explanation :

In the above query we are searching for the data which contains apples OR Banana in the field description, so this query will return those documents which have either apples or Banana in the field description, also if you noticed we added "minimumShouldMatch": 1 in this query, minimumShouldMatch means specify a minimum number of should clauses that must match to include a document in the results(the given term has to occur specific times whatever we pass as a parameter, default it is 0)

filter Clauses must all match for a document to be included in the results. filter clauses do not contribute to a returned document's score.

in other words, it is a replacement of $in operator in full-text search.

filter behaves the same as must, except that the filter clause is not considered in a returned document's score, and therefore does not affect the order of the returned documents.

Example:

$search: {
  "compound": {
    "filter": [{
      "text": {
        "query": ["CLIENT", "PROFESSIONAL"],
        "path": "role"
      }
    }]
  }
}

Now, as we understand all the important operators of full-text search, here a an example of a query which combines all these operators to get the result.

Query -

db.col.aggregate([
  {
    $search: {
      "compound": {
        "must": [{
          "text": {
             "query": "varieties",
             "path": "description"
          }
        }], 
        "mustNot": [{
          "text": {
            "query": "apples",
            "path": "description"
          }
        }],
        "should": [
          {
            "text": {
              "query": "Fuji",
              "path": "description"
            }
          },
          {
            "text": {
              "query": "Golden Delicious",
              "path": "description"
            }
          }],
          "minimumShouldMatch": 2
        }
      }
    }
])

Explanation:

The following example uses a combination of must, mustNot, Shouldclauses to construct a query. The mustclause uses the text operator to searchfor the term varietiesin the description field. For a document to match, it must fulfil the mustclause. The mustNotclause performs a searchoperation for the term applesin the descriptionfield. For a document to match, it must not fulfil the mustNotclause. The Should clause uses the text operator to searchfor the term Fuji or Golden Delicious in the description field. For a document to match either of them at least 2 times i.e. minimumShouldMatch we used, it will return any terms or both terms given if it occurs more than 1.

I hope you find this blog post informative and helpful as you dive deeper into MongoDB Atlas Full-Text Search. Happy querying!

From indexing to querying: How MongoDB's full-text search stacks up against ElasticSearch.

Utsav Upadhyay — Sun, 09 Apr 2023 17:17:07 +0000

MongoDB has been one of the most popular NoSQL databases in recent years, and its popularity continues to grow among developers and technology communities.
Some Major reasons for that is it's - Flexibility, Scalability, Performance, Cloud-Based, Integration and community.

But When it comes to Full-text search there are very few options available and suitable, in which ElasticSearch is the elephant amongst all, followed by Solr.

So, What is Full text search ?

Full-text search is a feature that allows you to use natural language queries to search through text data for specific words or phrases. It's commonly used in applications like search engines, e-commerce sites, and content management systems, where the ability to search through large amounts of text data quickly and accurately is critical.

MongoDB developer has been continuously working over the past year to provide us this full text search.

How to use MongoDB Atlas Full text search:

By Using MongoDB Atlas search we can easily search the terms into the specific fields of our database, also, we can use the Operators to search words or Phrases, Operators like - AND, OR, NOT

For Using MongoDB Atlas Full text search, we need to take care some steps and rules/syntax.

Create a MongoDB Atlas Cluster: If you haven't already, sign up for MongoDB Atlas and create a new cluster. Make sure to choose a cluster tier that supports Full-Text Search, such as M10 or above.
Enable Full-Text Search: Once your cluster is configured, go to the Atlas UI's "Indexes" tab and click "Add Index." Select "Full-Text Search" as the index type and the fields to index. Then, to enable Full-Text Search for your cluster, click "Create Index.
Configure Search: After you've enabled Full-Text Search, you can configure your search settings using the "Search Configuration" tab in the Atlas UI. Here, you need to configure the index.
I would recommend Visual editor for first time and it is easy to use.
In the configure Search after selecting Visual editor we need to name our Index and select the Database and collection where we need to do Text Search.
Index configuration and Field Mapping this is the most important step for our Full text search. In Index configuration we define Analyzers and Fields/path where we need to search, for example there is a field name products where you want to search product name. so, you will enter the field name in the Field Mappings
After making all these configuration you can click on save and it will start enabling all of your defined field and collection into full text search, this process will take a bit time and solely depend upon the size of the collection.
Now you will see a dashboard like this where you can test your search but in a limited way example- by clicking on option query you can search you single text or phrase in the interface.

For searching more than one or multiple words with Operators like - AND, OR, NOT you need to write queries (see image for the reference)

Now Let's comes to MongoDB Atlas Search Syntax and Basic Queries :

    db.collection.aggregate([
      {
        $search: {
          index: "your-index-name",
          "text": {
            "query": "your-word",
            "path": "field"
          }
        }
      } 
    ])

Explanation -
In the above query we are using aggregation as search is always works with aggregation, and by using.
$search: The $search stage performs a full-text search on the specified field or fields which must be covered by an Atlas Search index. defining Index is option, because if you not define index name it will always use the default one. but if you have multiple index then you need to defined. Also, best practice is to define index name.
Query: you will define the word which you are searching for. If you are searching for phrase then you need to change text: with phrase
Path: in the path we need to define the field where we are searching the word, We can define multiple paths in array forms too and it will only use those path which we defined at time of index making.

Example:

{
  "_id" : 1,
  "type" : "apple",
  "description" : "Apples come in several varieties, including Fuji, Granny Smith, and Honeycrisp.",
  "category" : "nonorganic",
  "in_stock" : false
},
{
  "_id" : 2,
  "type" : "banana",
  "description" : "Bananas are usually sold in bunches of five or six.",
  "category" : "nonorganic",
  "in_stock" : true
}

in the above data if we need to search a term like - Banana or Apple out of 2 documents above. we can easily search and return the document which contain this word.

So suppose we need to Search Bananas here and in the field description and my index name is search-fruits -
Query:

    db.collection.aggregate([
      {
        $search: {
          index: "search-fruits", //define your index name
          "text": {
            "query": "Bananas",
            "path": "description"
          }
        }
      } 
    ])

this above query return 1 document out 2 because only 1 document contains Bananas.

{
  "_id" : 2,
  "type" : "banana",
  "description" : "Bananas are usually sold in bunches of five or six.",
  "category" : "nonorganic",
  "in_stock" : true
}

There are more advance queries like - compound search which use operators like AND, OR, NOT (Should, Must, Mustnot) and features like - autocompletes, fuzzy searches etc. We will cover these advance topics in next article very soon!

Few Points to consider before using MongoDB Atlas Full text search over ElasticSearch and Solr -

Integrated within the database: MongoDB Atlas Full-Text Search is integrated within the MongoDB database, whereas Elasticsearch and Solr are standalone search engines that must be integrated with a separate database. This integration can simplify the deployment and management process for users who already use MongoDB.
Native JSON support: MongoDB stores data in a JSON-like format, and Atlas Full-Text Search leverages this format for indexing and searching. Elasticsearch and Solr also support JSON, but they use a different data structure and query syntax.
Automatic indexing: MongoDB Atlas Full-Text Search automatically indexes data as it's added to the database, making it easier for developers to implement search functionality without the need for a separate indexing process.
Scale-out architecture: MongoDB Atlas is designed to be scalable and can handle large amounts of data and concurrent users. It allows for the creation of replica sets and sharded clusters, making it easier to scale out search capacity as needed.
No need for dedicated search servers: With MongoDB Atlas Full-Text Search, there is no need for a dedicated search server or infrastructure, unlike Elasticsearch or Solr. This can simplify the deployment and management process for users.
Cost: If you're already using MongoDB, using MongoDB Full-Text Search can be more cost-effective than using ElasticSearch, which requires additional infrastructure and management.

Conclusion: In the end, the use of MongoDB Atlas Full-Text Search is entirely dependent on your specific use case. While it lacks the advanced features of Elasticsearch or Solr, it does provide a number of advantages such as ease of use, integration with the MongoDB ecosystem, and scalability. Finally, it is up to you to evaluate your specific needs and determine which full-text search engine will best meet them.

Streamlining Data Export to Excel: A comprehensive guide to using Python, Nodejs, PHP.

Utsav Upadhyay — Wed, 11 Jan 2023 12:10:20 +0000

Excel is a widely-used format for data analysis and reporting, and from our website or portal is most important feature nowadays, many libraries allow developers to export data in Excel format. There are several libraries and inbuilt features in programming languages which help us to export data in excel format, some languages and their libraries are listed below -

Python

Nodejs

PHP

PhpSpreadsheet
(inbuilt .csv import method)

Note: I have only mentioned most used and versatile libraries which has all the features and fast in terms of speed with these features.

Although there are tonnes of libraries for excel but above library has the required features like - text hyperlink, combine columns/rows and text decorations and width controls etc, which is very hard to find in any other library.

Let's try how can we export data in excel using xlsxwriter in Python.

Now before jump into creating an export to excel code, we need to learn some basics about excel.

.xls: The.xls file extension is used for Microsoft Excel 97-2003 workbooks. It supports Excel versions 97-2003 and has a maximum row and column count of 65,536. This extension, however, does not support certain Excel features such as macros, pivot tables, and data validation.

.xlsx: Microsoft Excel 2007 and later versions use the.xlsx file extension. It can hold up to 1,048,576 rows and 16,384 columns. This file format also supports advanced Excel features like macros, pivot tables, and data validation.
.xlsm: Similar to.xlsx, the.xlsm file extension is used for workbooks that contain macros. It has the same row and column constraints as.xlsx.

.xlsb: Also known as "Binary Excel," this file extension is faster and uses less memory than other Excel file formats. It is advised to use it with large datasets and complex calculations. It is, however, not human-readable and cannot be opened with a text editor.

.csv: This is a plain-text file format that can be opened by Excel, but it does not retain the original excel file's formatting and data types. It can only store data in tabular format and does not support formulas or styles.

.Odds: This is an OpenDocument Spreadsheet file; it is an open spreadsheet standard that can be used by many spreadsheet programmes, including Apache OpenOffice Calc and LibreOffice Calc.
It is not fully compatible with Excel, but it can open.ods files in Excel with some restrictions.

Finally, the file extension you choose will be determined by the specific needs of your project and what you intend to do with the Excel file. . The most commonly used formats for compatibility and functionality are.xlsx and.xlsm, but.csv and .ods can be used for data exchange or open-source project compatibility. Because of their limitations,.xls and.xlsb are less commonly and less strongly recommended.

Let's built a basic application with xlsxwriter.

First we need to import xlsxwriter library with the help of PyPi
pip install XlsxWriter
then we can import the library and write some basic export to excel application.

import xlsxwriter

# Create a new workbook and add a worksheet
workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet()

# Prepare data to be written to the worksheet
data = [['Name', 'Age', 'City'],
        ['John', 25, 'New York'],
        ['Mike', 32, 'Los Angeles'],
        ['Mary', 28, 'Chicago']]

# Write data to the worksheet
for row_num, row_data in enumerate(data):
    for col_num, cell_data in enumerate(row_data):
        worksheet.write(row_num, col_num, cell_data)

# Save the workbook
workbook.close()

Some feature about this library is it can also be accessible with Pandas too and we can use it with all the databases like -SQL, MongoDB, Postgres etc.

xlsxwriter is a low-level library that provides a lot of flexibility and control over the Excel file format, which makes it useful for advanced Excel manipulation. However, it also makes the code slightly more complex when compared to other libraries such as pandas.

As you continue to explore the topic of data export to Excel using different libraries in programming languages, I invite you to reach out to me through comments, direct messaging here, or on Twitter. if you have any further questions or would like to request additional in-depth coverage of specific libraries in Node.js or PHP.