Atlas search with embeddedDocuments

MongoDB Atlas Atlas Search
1

Hi all,
i need your help on implementing an atlas search index, i’m struggling to make it work as i expect.
I have actually this type of data :

{
  "_id": {
    "$oid": "5c9a407f1868e7ffb638a4c5"
  },  
  "providers": [
    {
      "city": "NEW YORK",
      "code": "code1",      
      "name": "NAME1",      
      "type": "type1",
      "validFrom": {
        "$date": "2023-07-01T00:00:00.000Z"
      }
    },
	{
      "city": "NEW YORK",
      "code": "code2",      
      "name": "NAME1",      
      "type": "type2",
      "validFrom": {
        "$date": "2023-11-01T00:00:00.000Z"
      }
    }
  ],
 "type" : "ST",
  "destinationCode": "destCode",
  "originCode": "orgCode",  
  "creationDate": {
    "$date": "2024-01-08T12:09:48.243Z"
  }
}

and i’m trying to reproduce this $find clauses using Atlas Search :

{
	providers: {
		$elemMatch : {
			code : 'code1' , 
			type:  'type1',
			validFrom: {$lte: ISODate('2023-09-11T08:56:22.012+00:00')}
		} 
	}, type : 'ST'
}

So, after reading some documentation and community posts, I started to work on embeddedDocument concepts in order to replace this $elemMatch, and i defined the below Atlas Search index :

{
  "mappings": {
    "dynamic": false,
    "fields": {
      "providers": {
        "type": "embeddedDocuments",
        "fields": {
          "code": {
            "type": "string"
          },
          "type": {
            "type": "string"
          },
          "validFrom": {
            "type": "date"
          }
        }
      },
      "type": {
        "type": "token"
      }
    }
  }
}

And the atlas search query :

{
  index: "default",	  	
  "embeddedDocument": {
    "path": "providers",
    "operator": {      
        "compound" :{          
          "must" : [{
            "text": {
              "path": "providers.code",
              "query": "code1"
            },
            "text":{
              "path" : "providers.type",
              "query":"type1"
            }
          }] ,
          "filter" : [{
            "range" : {
                    "path": "providers.validFrom",
                    "lt": ISODate('2023-09-11T08:56:22.012+00:00')                    
                  }
          }]         
        }    
                    
    }
  }  
}

But the results are not the expected one, in the sense it seems not taking the embeddedDocument in account, and returning data which have either the providers.code matching or the providers.type or providers.validFrom, but not only those having three criteria matching.
I don’t understand what i’m doing wrong, and i tried actually a lot of other combinations without sucess.
Could you please help telling me what is not good in my query ?

2

Hi @PILLOT_Christophe , welcome to the MongoDB Community and thanks for sharing your question! The embeddedDocument operator will return a document as long as any of the subdocuments satisfy the all 3 of the specified $search criteria.

Can you provide an example document which is being returned that you would not expect to match?

3

Hi @amyjian ,
sorry for late answer. Actually, i will put below the real use case data for which i’m having the issue. With the above one, the issue is not coming and i understood afterwards it seems to be linked to the format of providers.code.
Please find below the data :

{
  "_id": {
    "$oid": "65cded70d76dc7506caad867"
  },
  "providers": [
    {
      "city": "NEW YORK",
      "code": "00000309222",
      "name": "NAME1",
      "type": "DCD",
      "validFrom": {
        "$date": "2024-07-01T00:00:00.000Z"
      }
    },
    {
      "city": "NEW YORK",
      "code": "0000002779",
      "name": "NAME2",
      "type": "NAM",
      "validFrom": {
        "$date": "2024-11-01T00:00:00.000Z"
      }
    }
  ],
  "type": "ST",
  "destinationCode": "destCode",
  "originCode": "orgCode",
  "creationDate": {
    "$date": "2024-01-08T12:09:48.243Z"
  }
}
4

@PILLOT_Christophe Thanks for sharing the document. In my testing, this document does not get returned when using the index and query you shared in your original response. Can you clarify what issue you are still experiencing?

5

Hi @amyjian ,
actually i’ve attached to this message the collection data + search query for which i’ve an issue.
pricing.quotationDashboard.search.json (779 Bytes)
pricing.quotationDashboard.json (3.0 KB)

If you look at the result of the query, there is 2 results with wrong providers.code which are being retrieved. Thanks for your help.