JSON Columns in Postgres Prevent Entire Row From Saving, unless modified

[souterjk]

Using Postgres13

We've built a pretty standard table in retool to represent data in a table. However, there is something wrong in the way that Retool is sending JSON column data back to the database when we modify a row. I've created a Loom below showing the exact problem, but we basically cannot save a row right now with JSON in it unless we also modify all of the JSON columns before the save. Even if the JSON in the columns is completely valid now, the save/update function is telling us its invalid when we modify a separate text column.

[minijohn]

Hey Joe, Welcome to the community

@souterjk

Even if the JSON in the columns is completely valid now

Valid for the table editor.

The fact that editing (new values) makes it work made me think it's the type of the value being passed in the DB query.

Do your .data and .recordUpdates look the same?

This is why it works after editing.

I would filter out the individual changesets of the row instead of pushing all columns to edit the record. Also patches your problem.

Something like:

let cs = {{table1.changeSet}};
let ru = {{table1.recordUpdates}};

let mapped = _.map(ru, function(e, i) {
  cs[i].id = e.id
});

return cs

in a transformer.

[souterjk]

Looking at the value of record updates before and after a change, there is a clear shift in the type that it's sending back to the database. It looks like before a change, the data is being kept as a JSON object (which should be fine) and after a change to that column its being set as a string (which is also fine).

This is before a change to the column:

This is after a change to the column:

Also the parsing on data for this simple JSON seems to be completely botched right now:

The JSON creating this is [{"domain": "poap.delivery", "allowSubdomains": false}]. There are no spaces, new lines or strange characters here.

[Jay]

Hey there @souterjk! I have reached out to our engineering team and they mentioned that you should be able to update the value to whatever format you need in a Temporary State or Transformer that pulls from recordUpdates or changeset on the table.

[ijames]

I'm just starting to dig into the versatility of using PostgreSQL JSON columns with Retool and came across this as one of the first Google search results. Ouch. From the outside, this seems like a bug. It's been a while, so has it been addressed?

The replies are handy but they feel like workarounds to me.

@Jay, what is a Temporary State? Is that a construct you can create like a Transformer?

Very new to Retool...

[victoria]

Hey @ijames! Welcome to Retool

Here is a doc on temporary state: https://docs.retool.com/reference/javascript-api-methods-temporary-state

You can create a temporary state variable in your app and use it to store values, reference the values from it and change the values using a JS query or event handlers!