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 !

Answer 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
              }
            }
          ]
        } 
      } 
    }
  ])

Answer 2

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.