Docs Menu

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

On this page

  • 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. We will create a materialized view that stores the numeric and date field values as strings. We 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.

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

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

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.

1

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

2

Run the following command at mongosh prompt:

use sample_airbnb
switched to db sample_airbnb
3

If you created an index that uses dynamic mappings, you can query the airbnb_mat_view collection using the queryString operator. If you created an index that uses static mappings, you can query the airbnb_mat_view collection using the autocomplete operator.

←  How to Run Multilingual Atlas Search QueriesCreate and Manage Atlas Search Indexes →
Give Feedback
© 2022 MongoDB, Inc.

About

  • Careers
  • Investor Relations
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2022 MongoDB, Inc.