Conditional logic in MongoDB update operations

[zean_validated]

I'm trying to write some conditional logic when writing to MongoDB directly from Retool.

I'm hitting an issue where conditional operators are treated as string literals. For instance, I've tried both these JSON-valid syntaxes:

{
  $set: {
    foo: {{ companyName.value }},
    bar: {
      $cond: {
        if: { $ne: [{{ fbAccountId.value }}, null] },
        then: {
          account_id: {{ fbAccountId.value }}
        },
        else: "$$REMOVE"
      }
    }
  }
}

{
  "$set": {
    "foo": {{ companyName.value }},
    "bar": {
      "$cond": {
        "if": { "$ne": [{{ fbAccountId.value }}, null] },
        "then": {
          "account_id": {{ fbAccountId.value }}
        },
        "else": "$$REMOVE"
      }
    }
  }
}

In either event, the Mongo (or Retool) has intepreted the conditional logic operators as string literals as evidenced by the persisted value:

My guess is that Retool is doing some casting of keys in ways I'm not expecting. Has anyone found the correct method for executing such a query?

[zean_validated]

I tried to wrap the entire query in brackets like so:

[
  {
    $set: {
      foo: {{ companyName.value }},
      bar: {
        $cond: {
          if: { $ne: [{{ fbAccountId.value }}, null] },
          then: {
            account_id: {{ fbAccountId.value }}
          },
          else: "$$REMOVE"
        }
      }
    }
  }
]

This resulted in a the error: "BSON field 'insert.documents.0' is the wrong type 'array', expected type 'object'".

Separately, I tried to wrap the value to $set in brackets – this just ended up writing the same value as above, but as an element within an array.

FYI @Darren @Tess

[Darren]

Thanks for testing that, @zean_validated! This is the thread that I was referencing earlier. For that particular case, I verified that wrapping the operation object in brackets fixed the issue.

Remind what kind of operation you're wanting to perform here - is it an insert or update?

[Paulo]

Hi @zean_validated, after some testing I found something to consider:

With this condition:
if: { $ne: [{{ fbAccountId.value }}, null] }
We are checking if the value of fbAccountId is not null. Is fbAccountId a variable, column, or an input? If it's an input, it will evaluate to "" when there is no value (the user has not typed in), so we should use "" instead of null.

For example, given this document:

If we want to remove the role key if a text input is empty, or update it to the value of the input when is not empty:

The Update query should be:

[{
    $set: { 
      role: {
        $cond: {
          if: { $ne: [{{ textInput1.value }}, ""] },
          then: {{ textInput1.value }},
          else: "$$REMOVE"
        }
      }
    }
  }]

Here is this query in action:

After we run it, the document is updated correctly:

If there is no input:

The key role is successfully removed:

[Paulo]

If we don't wrap the 'Update' object with an array:

We run into the issue you described during Office Hours where the string literal is sent:

[zean_validated]

@Paulo

[{
    $set: { 
      role: {
        $cond: {
          if: { $ne: [{{ textInput1.value }}, ""] },
          then: {{ textInput1.value }},
          else: "$REMOVE"
        }
      }
    }
  }]

Thanks for the investigation. I've implemented these ideas, but I'm still hitting some snags.

First, I should note that I'm using the syntax for insertOne – wrapping in square brackets yields a "BSON field 'insert.documents.0' is the wrong type 'array', expected type 'object'" error:

Without wrapping in square brackets, the write executes, but again, interpolates conditional operators as string literals (see output at the bottom):

@Paulo FWIW I would use updateOne as you suggested, but this operation is only run to create new records, and passing {} as the filter doesn't seem to do anything.

[Darren]

Appreciate the update, @zean_validated! I couldn't remember if this was an insert or update operation.

I'm still trying to narrow down exactly where the issue here is occurring - if it's a limitation of the Extended JSON syntax as it pertains to document creation or the result of typecasting/parsing that is happening on the Retool side.

In the meantime, my recommendation is to just construct the entire document JSON within curly braces. This allows you to perform fairly complex conditionals as long as you only need access to data within Retool.

For more complex objects, you could even move all the logic for constructing the document into a separate JS query. I'll provide an update here when I have a more concrete answer about using MongoDB aggregates and other functions but hopefully this unblocks you for the time being!

[zean_validated]

@Darren thanks for the suggestion – I've tried to employ this in several ways. It's successful when inserting a single-field document, but I'm still encountering syntax errors when trying to insert a multi-field document.

Here's my 1:1 implementation of your suggested logic:

Then, I tried to enclose static strings within double quotes:

Any suggestions for how I can overcome the continued syntax errors with this more Retool purist approach?

[zean_validated]

@Paulo @Darren I've gotten a transformer to do exactly what I'd been hoping.

Here's the logic I wrote in the transformer:

Here's how the MongoDB insertion successfully invokes the transformer:

This transformer pattern resolves my issue – thanks for the help.