MongoDb Connector - Distinct operation with null values

[diogoalfarelos]

Hello

I'm creating an Integration with the MongoDb connector.
The operation i'm using is Distinct and it's working fine most of the cases.

The exception so far is when the field i'm querying, in one of the documents has the value null and this returns an exception on the connector level. 

Is there any way i can handle this so that i can return all the valid results (that are not null)?

Event if i catch the exception i don't have a 'partial' result from the distinct operation.

{
  "error": {
    "code": 500,
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "metadata": {
          "action_name": "DistinctValues",
          "cause": "Expect a map object but found: null",
          "connection_type": "MongoDB"
        }
      }
    ],
    "message": "Unable to execute the specified action.",
    "status": "UNKNOWN"
  }
}

[Joy_S · Accepted solution]

Hi @diogoalfarelos,

Welcome to Google Cloud Community!

To avoid issues with null values during a Distinct operation using the MongoDB connector, the best way is to filter them out beforehand. You can do this by adding a query that excludes null values before running the distinct. You may try using any of the following:

• Using $ne (Not Equal to Null): This is the most straightforward approach. You can add a condition to your query that specifies the field should not be equal to null.
• Using $exists: true: Another option is to check that the field actually exists in the document. It’s a bit different, but using $exists: true usually works the same as $ne: null when you're just trying to skip over fields that are missing or set to null.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[diogoalfarelos]

Hello @Joy_S 

Thank you for the reply.
The filter is working. Struggled a bit initially because i had to JSON.stringify the filter element to be accepted by the connectorInputPayload