Is there a way to include a Int32 field in a search index in MongoDB (with Atlas Search)?

Question

I have a collection in a Mongo Atlas DB on which I have a search index including some specific string fields. What I want to do is include a Int32 field in this search index to be able to do a search on this number, along with the other fields. I tried to add the field (Number) as a new field in the search index, with the type number, but it doesn't work. I guess it's because it compares the query, a string, with an Int32, but is there a way to make it work ? Or do I have to copy the "Number" in another field "NumberString" to include in the search index ?

Here is an example of one of these documents :

{
  “_id” : ObjectId(“010000000000000000000003”),
  “Description” : {
    “fr-CA” : “Un lot de test”,
    “en-CA” : “A test item”
  },
  “Name” : {
    “fr-CA” : “Lot de test”,
    “en-CA” : “Test item”
  },
  “Number” : 345,
  “Partners” : [],
[...]
}

The index :

{
“mappings”: {
  “dynamic”: false,
  “fields”: {
    “Description”: {
      “fields”: {
        “en-CA”: {
          “analyzer”: “lucene.english”,
          “searchAnalyzer”: “lucene.english”,
          “type”: “string”
        },
        “fr-CA”: {
          “analyzer”: “lucene.french”,
          “searchAnalyzer”: “lucene.french”,
          “type”: “string”
        }
      },
      “type”: “document”
    },
    “Name”: {
      “fields”: {
        “en-CA”: {
          “analyzer”: “lucene.english”,
          “searchAnalyzer”: “lucene.english”,
          “type”: “string”
        },
        “fr-CA”: {
          “analyzer”: “lucene.french”,
          “searchAnalyzer”: “lucene.french”,
          “type”: “string”
        }
      },
      “type”: “document”
    },
    “Number”:
      {
      “representation”: “int64”,
      “type”: “number”
      },
    “Partners”: {
      “fields”: {
        “Name”: {
          “type”: “string”
        }
      },
    “type”: “document”
}}}}

And finally the query I try to do.

db.[myDB].aggregate([{ $search: { "index": "default", "text": { "query": "345", "path": ["Number", "Name.fr-CA", "Description.fr-CA", "Partners.Name"]}}}])

For this example, I want the query to be applied on Number, Name, Description and Partners and to return everything that matches. I would expect to have the item #345, but also any items with 345 in the name or description. Is it possible ?

Thanks !

score 1 · Answer 1 · answered Apr 21 '21 at 14:04

1

With your current datatype you, should be able to search for #345 in text. However, I would structure the query like so, to support the numeric field as well:

  db.[myDB].aggregate([
    { 
      $search: { 
        "index": "default", 
        "compound": {
          "should":[
            {
              "text": { 
                "query": "345", 
                "path": ["Name.fr-CA", "Description.fr-CA", "Partners.Name"] 
              }
            },
            {
              "near": { 
                "origin": 345, 
                "path": "Number",
                "pivot": 2
              }
            }
          ]
        } 
      } 
    }
  ])

answered Apr 21 '21 at 14:04

Nice-Guy

1,457
11
20

1

The [range](https://docs.atlas.mongodb.com/reference/atlas-search/range/) operator works well with numeric types as well. – Oren Apr 21 '21 at 15:56
Thanks to you both for the suggestions ! But "near" returns too many results in my case. "range" could work, but it's not exactly what I'm looking for. I'd like a way to do a "startsWith" with this field. For example, if I enter "2" in my app search bar, it returns item #2, #20, #21, etc. I tried to do it by creating "NumberSearch" string fields and search on this on instead, with a string index, but it still returns only the exact match. Any ideas ? – Fanny Apr 21 '21 at 19:35
for the string search implementation of this, you could use a `lucene.keyword` analyzer and regex operator in the `$search` stage. It should still be very fast. Alternatively, you could use autocomplete on the strings with an minnGram of 1. It will be even faster. – Nice-Guy Apr 22 '21 at 20:00

evolross · Answer 2 · 2023-06-22T16:21:47.827

We're finding this pattern effective without using tricks like near or range. Set up a compound search aggregation with the base text operation:

const searchAggregation = {
   index: "some-search-index",
   compound: {
      should: [
         {
            text: {
               query: query,
               path: {
                  wildcard: "*"
               }
            }
         }
      ],
      minimumShouldMatch: 1
   }
};

Then we check if the query is an integer by testing if the search query passes a parseInt(). This is true if query is either a well-formed integer OR the first token is a well-formed integer (e.g. "27 Tickets"). If so, then pass the integer to the query. It has to be done this way because normal text queries don't work with numbers in Mongo Search (Lucene). They have to use equals comparisons. And equals comparisons only work on set fields, so they can't be done in a single equals, but they can be all added to a compound search:

const parseIntQuery = parseInt(query);
if(!isNaN(parseIntQuery)) {
   //  Push the integer comparison for Number field "order"
   searchAggregation.compound.should.push({
      equals: {
         value: parseIntQuery,
         path: "order"
      }
   });

   //  Push the integer comparison for Number field "quantity"
   searchAggregation.compound.should.push({
      equals: {
         value: parseIntQuery,
         path: "quantity"
      }
   });

   //  Push the integer comparison for  Number field "age"
   searchAggregation.compound.should.push({
      equals: {
         value: parseIntQuery,
         path: "age"
      }
   });
}

Then pass searchAggregation to $search in your aggregation.

Is there a way to include a Int32 field in a search index in MongoDB (with Atlas Search)?

2 Answers2