MongoDB Update Many Example

[theleadingpractice]

Hi - I'm trying to work with MongoDB UpdateMany method but cannot seem to get my table.recordUpdates structured correctly.

Can someone provide a simple example of how to do this?

{
  $set: {
    {{tableSettings.recordUpdates}}
  },
  upsert:true,
  multi:true
}

[Tess]

Hey @theleadingpractice

A lot of users actually use a JS loop & an updateOne query type.

You'll start with a JS query that maps through your .recordUpdates and triggers a new updateOne query for each row (You'll replace id and sales with the values in your table. You'll also want to replace table5 and updateOne with your table name & update query name):

Then, you create an updateOne query that has dynamic values for each value from your recordUpdates:

Let me know if you think this would be helpful for your use case!

[voboxjohn]

By this answer do you mean updateMany does not work with MongoDB? If it does can you provide an example, I'm also trying to get this to work

[Tess]

Hi @voboxjohn

It does work in Retool. For example, this query updates all sales in the users table to be equal to 10

[Tess]

Let us know if you're seeing any errors with your query

[yeh]

Can you provide an example on how to update the whole row? I'm trying to use table.recordUpdates[0] to update the db with table values.

However, it would require me to remove the _id from the object.

Performing an update on the path '_id' would modify the immutable field '_id'

I'd like to know how to do this right from the Update.

[Tess]

Hi @yeh Thanks for reaching out. If you only need to reference recordUpdates[0], you could do something like this (using _.omit()):

However, I'd typically recommend following the JS query mapped approach:

Query1:

Query2:

[yeh]

Thanks a lot!

[Raul_Vazquez_Mendez]

Hi!

Sorry Tess, I have a doubt, your JSCode return a query3, whats this? where you do define it?

Otherwise what I want to do is to manually edit the fields in the table and when I save them, update fields on my MongoDB table.

[Tess]

Hi @Raul_Vazquez_Mendez thanks for checking in! Realizing my post was a bit confusing.

The Mongo query (in my third screenshot above and posted again below) is named "query3"

So the JS query shown in my previous message triggers this query and passes in dynamic values for the _id, _name, and number:

[osirisfrik]

Hi @Tess, I try use your example, but don't work for me

retool say success but it is not reflected in the database

[Tess]

Hi @osirisfrik Hmm, that is strange From what I can tell, this looks to be set up correctly. Are you triggering this on the save changes button in the table?

Can we expand those items in the Retool debug console to see the exact values that are sent from saveChanges to updateOne? (i.e. how was additionalScope defined)

I'd also be curious if hardcoding some values works -- just to confirm there isn't a syntax issue

For example, changing {{_id}} to a hardcoded _id & changing {{readableCode}} to a hardcoded value

[osirisfrik]

HI @Tess, I found an error in the js code (additionalScoope -> additionalScope), I have already solved it but the problem persists

[Tess]

Oh right! Thanks for calling that out. I think we're working on some linting to make that more noticeable. Hmm, if you put those values into the updateOne query by hardcoding them, does it also not update?

How are you confirming that the values aren't updating? Are you calling a get request in Retool?

[oyinda_david]

Hello, I have tried these steps but my mongo db instance still doesn't reflect the changes. Also the read lines beneath the params you are passing, isn't that the sign of an error. Please forgive me, I am new to Retool @Tess

[Tess]

Hi @oyinda_david can you share some screenshots? The mongo query will have red lines for the params you're passing in because the values are not defined yet. They'll be defined when you run the JS query

[oyinda_david]

Hello @Tess thank you for your explanation. I didnt understand the earlier explanations, but its clear now. I have been able to update multiple rows in the mongoDB

[Tess]

Oh, wonderful! Welcome to Retool, btw! Let us know if other questions come up

[Mike_M]

Hi all! To anyone revisiting this thread to see how to perform multiple updates from a table on Retool to a collection of documents on MongoDB, I wanted to add some additional info. updateMany() works differently than a bulk update in postgres, as it's use case is limited to making one change for multiple documents. Let's say you wanted to change the status of a subset of records to "Complete", then updateMany() would be perfect for that scenario. It can change multiple fields, but it still has to be same change for all records that you're filtering over.

This is not likely to be useful in the context of updating a table in Retool, in which you may be trying to update the individual values of multiple fields in a table, referenced by table1.changesetArray for example. This is why Tess recommends looping over the array and performing an updateOne() for each row. This is perfectly good for a small amount of changes, but I should mention for larger updates (>5 at once) you should use a bulkWrite(). Here's an example of the syntax:

bulkWrite([
  {
    updateOne: {
      filter: { _id: 1 },
      update: { $set: { status: "active" } }
    }
  },
  {
    updateOne: {
      filter: { _id: 2 },
      update: { $set: { status: "pending" } }
    }
  }
])

Due to this formatting constraint, you can translate the info from a changesetObject, and you could provide the array of operations as follows:

{{ Object.entries(table16.changesetObject).map(row => (
  {
    updateOne: {
      filter: {_id: {$oid: row[0]} },
      update: {$set: row[1]}
    }
  }
)) }}

This would update all your rows in one query, rather than performing a separate query for each row