How to Run Atlas Search String Queries Against Date and Numeric Fields

Log in to Atlas and connect to your cluster using mongosh.

Open mongosh in a terminal window and connect to your cluster. For detailed instructions on connecting, see Connect via mongosh.

Verify and switch to the sample_airbnb database.

1. Run the following command to verify that the database exists in your cluster:

   show dbs

   HIDE OUTPUT

   sample_airbnb       55.3 MB
   sample_analytics    9.59 MB
   sample_geospatial   1.43 MB
   sample_guides         41 kB
   sample_mflix        51.1 MB
   sample_restaurants  6.95 MB
   sample_supplies     1.21 MB
   sample_training     55.5 MB
   sample_weatherdata  2.89 MB
   admin                348 kB
   local                2.1 GB

2. Run the following command to switch to the sample_airbnb database.

   use sample_airbnb

   HIDE OUTPUT

   switched to db sample_airbnb

Create a materialized view named airbnb_mat_view.

To create a materialized view, run the following query. The query specifies the following aggregation pipeline stages:

• $project: In this stage, the query does the following:

  • Converts the last_scraped date object to a string in the format YYYY-MM-DD using $dateToString.

  • Includes name and property_type string fields.

  • Converts accomodates number field to a string using $toString.

  • Converts maximum_nights number field to a string using $toString.

• $merge: In this stage, the query writes the output fields from the $project stage to a materialized view named airbnb_mat_view.

  db.listingsAndReviews.aggregate( [
    {
      $project: {
        lastScrapedDate: { $dateToString: { format: "%Y-%m-%d", date: "$last_scraped" } },
        propertyName: "$name",
        propertyType: "$property_type",
        accommodatesNumber: { $toString: "$accommodates" },
        maximumNumberOfNights: { $toString: "$maximum_nights" }
      }
    },
    { $merge: { into: "airbnb_mat_view", whenMatched: "replace" } }
  ] )

Verify that the materialized view was successfully created.

To verify, run the following command:

db.airbnb_mat_view.findOne()

{
  _id: '10006546',
  lastScrapedDate: '2019-02-16',
  propertyName: 'Ribeira Charming Duplex',
  propertyType: 'House',
  accommodatesNumber: '8',
  maximumNumberOfNights: '30'
}

Navigate to the Atlas Search page for your project.

1. If it is not already displayed, select the organization that contains your desired project from the Organizations menu in the navigation bar.

2. If it is not already displayed, select your desired project from the Projects menu in the navigation bar.

3. Click your cluster's name.

4. Click the Search tab.

Click Create Index.

Select a Configuration Method and click Next.

• For a guided experience, select Visual Editor.

• To edit the raw index definition, select JSON Editor.

Enter the Index Name, and set the Database and Collection.

1. In the Index Name field, enter date-number-fields-tutorial.

   Note

   If you name your index default, you don't need to specify an index parameter when using the $search pipeline stage. Otherwise, you must specify the index name using the index parameter.

2. In the Database and Collection section, find the sample_airbnb database, and select the airbnb_mat_view collection.

Define an index on the fields in the materialized view.

You can create one of the following indexes:

• Index that uses dynamic mappings for running queries using the queryString operator. You can't run queries using the autocomplete operator if your index definition uses only dynamic mappings.

• Index that uses static mappings for running queries using autocomplete operator. You can't run queries using the queryString operator against fields indexed as type autocomplete.

Click Create Search Index.

Close the You're All Set! modal window and wait for the index build to complete.