SQL LIKE Statement in MongoDB

Introduction

SQL LIKE statements, or clauses, are used to compare different values in a document collection. This allows for querying a document collection using basic parameters and can also be used to search for phrases or words, similar to the LIKE operator that is used in relational databases. This tutorial will explain how to use the SQL LIKE statement in MongoDB to perform pattern matching and will provide hands-on examples.

Prerequisites

  • MongoDB must be properly installed and configured on the local system in order to use a SQL LIKE statement in MongoDB.

Create a Sample Dataset

This following code will create the sample dataset that will be used for all of the examples in this tutorial:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
db.resorts.insertMany([
  {
    resortName: "ByTheSea",
    resortId: "bts123",
    pricePerDay: 100,
    address: { city: "Manila", region: "NCR" },
    reviews: [
      { username: "Robert", rate: 5 },
      { username: "Glen", rate: 4 }
    ]
  },
  {
    resortName: "DeepLevel",
    resortId: "bts223",
    pricePerDay: 150,
    address: { city: "Manila", region: "NCR" },
    reviews: [
      { username: "Jason", rate: 6 },
      { username: "Gina", rate: 5 }
    ]
  },
  {
    resortName: "Swim Club",
    resortId: "bts145",
    pricePerDay: 180,
    address: { city: "Olongapo", region: "Region 3" },
    reviews: [
      { username: "Risa", rate: 7 },
      { username: "Greg", rate: 6 }
    ]
  },
  {
    resortName: "Drift",
    resortId: "bts908",
    pricePerDay: 120,
    address: { city: "Olongapo", region: "Region 3" },
    reviews: [
      { username: "Romeo", rate: 6 },
      { username: "Anne", rate: 6 }
    ]
  }
]);

The above code should produce the following results:

1
2
3
4
5
6
7
8
9
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("5e2548a57ab60723c4652d6e"),
ObjectId("5e2548a57ab60723c4652d6f"),
ObjectId("5e2548a57ab60723c4652d70"),
ObjectId("5e2548a57ab60723c4652d71")
]
}

Now execute the db.resorts.findOne(); command to fetch and verify one document. The results should resemble the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
{
"_id" : ObjectId("5e2548a57ab60723c4652d6e"),
"resortName" : "ByTheSea",
"resortId" : "bts123",
"pricePerDay" : 100,
"address" : {
"city" : "Manila",
"region" : "NCR"
},
"reviews" : [
{
"username" : "Robert",
"rate" : 5
},
{
"username" : "Glen",
"rate" : 4
}
]
}

The preceding section illustrated how powerful the db.collection.find() method is. The find() method allows for querying a document’s collection using basic parameters that returns a cursor. Here a cursor is defined as a set of results that can be looped through to further process the result set as required.

Find Word Similarities

In the previous section, the sample collection could be queried using the basic form of the find() method. This section will explain how to make use of this method, with a slight modification, to allow searching for matches of a phrase or words that partially matches a given field. Note that this is similar to the LIKE operator that is used in relational databases.

To search for partial matches of a word or phrase of a given field, use a regular expression, or just use the regex abbreviation, where it essentially takes a string argument that describes the search pattern.

Examine the following example where a basic regex is executed to determine how many resorts are located in the city of “Manila” that are listed in the collection:

1
db.resorts.find({ "address.city": /Manila/ }).count();

The output should resemble the following:

1
2
> db.resorts.find( { "address.city": /Manila/ } ).count();
2

NOTE: The word or phrase between the two forward slashes, /Manila/ in the above example, can be changed as needed.

Notice that the actual query of the find() method was not changed in the above code. Here all documents were only scanned, however, this time the focus was on the city field that corresponds to “Manila”. Therefore, instead of using the usual quoted string, the regex form was used to begin fetching records that match part of the queried word or phrase.

Conclusion

This tutorial explained how to use the SQL LIKE statement in MongoDB to perform pattern matching and provided specific examples. The tutorial covered how to create a sample dataset and execute the db.resorts.findOne(); command to fetch and verify a document. The article also explained how to find word similarities by searching for partial matches of a phrase or words of a given field by modifying the find() method. When looking for word similarities in a given document collection, remember that the word or phrase between the two forward slashes (//) can be changed as needed for executing various queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.