Searching with filter range on dates brings nothing

Alfredo_Barra · 2024-07-16T17:20:30.368Z

Hi!

I’m having a problem with an Search Index. I’m trying to do a search query filtering a range of dates, but it results into an empty array of results. The index is defined like this.

{
  "mappings": {
    "dynamic": false,
    "fields": {
      "country": {
        "type": "string"
      },
      "item_unit_price": {
        "type": "number"
      },
      "opportunity_id": {
        "type": "string"
      },
      "opportunity_publish_date": {
        "type": "date"
      },
      "opportunity_status": {
        "type": "string"
      },
      "opportunity_type": {
        "type": "string"
      },
      "original_description": {
        "type": "string"
      },
      "summarize_description": {
        "type": "string"
      },
      "type": {
        "type": "string"
      }
    }
  }
}

And the search query is like this:

{
      $search: {
        index: 'better_match',
        compound: {
          must: [
            {
              text: {
                query: itemName,
                path: 'summarize_description',
              },
            },
          ],
          mustNot: {
            text: {
              path: 'opportunity_status',
              query: 'Cancelada',
            },
          },
          filter: [
            {
              text: {
                query: type,
                path: 'opportunity_type',
              },
            },
            {
              text: {
                query: country,
                path: 'country',
              },
            },
            {
              range: {
                path: 'opportunity_publish_date',
                gte: sixMonthsAgo, //Date
                lte: now, //Date
              },
            },
          ],
        },
        sort: defaultSort,
        count: {
          type: 'total',
        },
      },
    }

Is there anything wrong with this query?

Thanks in advance!

amyjian · 2024-07-16T18:36:16.507Z

Hey @Alfredo_Barra , could you create an Atlas Search Playground snapshot with a sample document and have this query + index loaded? Just add in your data and press Share to generate a unique link.

Please make sure to use the exact values that you expect to be stored by each variable, ie. itemName, type, country, sixMonthsAgo, now, defaultSort

Jennysson_Junior · 2024-07-17T15:30:28.209Z

Hi there!

Here are a few things to check and some suggestions to help you troubleshoot

Date Format: Ensure that the dates sixMonthsAgo and now are in the correct format that MongoDB expects. They should be in ISODate format.

Field Type: Double-check that the field opportunity_publish_date is indeed indexed as a date type in MongoDB. From your mapping, it looks correct, but it’s good to verify.
Index Configuration: Ensure that the better_match index is properly configured and up to date.

And finally to help you on troubleshooting:

Testing Individual Filters: Test each filter individually to ensure that they are working correctly. This can help isolate which part of the query might be causing the issue.

I hope that it help you

Alfredo_Barra · 2024-07-18T11:21:37.097Z

Hi!

Thanks for your replys. I found the problem. It was that the “opportunity_publish_date” was being saved as string, so the Date operatiors were not working. The solution was to transform the field to actual Date type.

Thank you guys!!