Docs Menu

Docs HomeLaunch & Manage MongoDBMongoDB Atlas

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

On this page

  • Required Access
  • Create a Materialized View on the Collection
  • Create Atlas Search Indexes on Fields in the Materialized View
  • Perform Text Search on Converted Fields

This tutorial describes how to run Atlas Search queries against string, date, and number fields in the sample_airbnb.listingsAndReviews collection. You will create a materialized view that stores the numeric and date field values as strings. You will then create an Atlas Search index on the materialized view and run queries against these string fields using the queryString and autocomplete operators. This tutorial takes you through the following steps:

  1. Create a materialized view on the sample_airbnb.listingsAndReviews collection name, property_type, last_scraped, and accomodates fields.

  2. Set up dynamic and static Atlas Search indexes on the materialized view.

  3. Run Atlas Search queries against the fields in the materialized view using the queryString and autocomplete operators to search for properties.

Before you begin, ensure that your Atlas cluster meets the requirements described in the Prerequisites.

To create an Atlas Search index, you must have Project Data Access Admin or higher access to the project.

In this section, you will create a materialized view named airbnb-mat-view for name, property_type, last_scraped, accomodates, and maximum_nights fields in the airbnb_listingsAndReviews collection. The materialized view allows you to take the numeric and date fields in the source collection and store them as string fields in the materialized view.

1

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

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

    show dbs
    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
    switched to db sample_airbnb
3

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" } }
    ] )
4

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

In this section, you will create Atlas Search indexes on the lastScrapedDate, name, propertyType, accommodatesNumber, and maximumNumberOfNights fields for running queries against these fields.

1
  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 Atlas Search tab.

2
3
  • For a guided experience, select Visual Editor.

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

4
  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.

5

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.

6

Note

The You're All Set! modal window appears to let you know your index is building.

7

You can run queries against the numeric and date fields that were converted to strings. This tutorial uses queryString and autocomplete operators to search for properties. The query uses the following pipeline stages:

  • $search stage to search the collection

  • $limit stage to limit the output to 5 results

  • $project stage to exclude _id

In this section, you will connect to your Atlas cluster and run the sample queries using the operator against the fields in the airbnb_mat_view collection.

Note

You can't run near or range queries against the date and number fields that were converted to strings in your materialized view.


Use the Select your language drop-down menu on this page to set the language of the examples in this section.


←  How to Run Atlas Search Queries with a Date Range FilterHow to Sort Your Atlas Search Results →