Connect dropdown to JSONB field in table. Works as key value pairs, not as form object

Queries and Resources
,
1

I have a retool database table called jobs that has a JSONB column called "certs" which may contain a JSON array of cert ids ([43,21,33] or [33] for example).

I have a form with a Multiselect component containing the list of certs with each having an integer value associated with it.

Here's the issue: If I insert into the table as an object selecting tblJobs.data I get an error that says "invalid input syntax for type json". If I switch it to Key value pairs and pick the certs key and assign it to certs.value (the multiselect value) it inserts the field just fine. This method also works fine for updates from a form. I conceded that I'd probably just have to leave it this way and moved on.

Now I'm attempting to add the option to edit inline. This time around I'm using "Bulk update via a primary key" and again everything works perfectly until I attempt to update the certs field (this time it's coming from a Tags field in the table). Once again, I get the JSON syntax error as I was getting before.

For comparison (I've left all other fields blank for clarity):

Here's adding (and updating) that works:
Adding by Key Value Pair
image
image

Here's adding that doesn't work:
Adding by Object
image
image

Here's updating inline using changesetArray
image

Any help with this would be appreciated.

2

I recreated this in a simple new little app. I added two forms with multiselects and had one submit via key value pair with no issues and the second submit as an object and get the same error as before.

Key Value Pair

image
image1029×588 45.4 KB

Object

image
image1056×715 51.1 KB

The test table simply has two columns, ID and a JSONB column.

3

Bug reproduced by directly trying {{ { field: [1,2,3] } }} as the Object value vs [1,2,3] in Key value pairs mode (I actually ran into this when working on internal apps), the workaround is to use Key value pairs if possible. Looking into this.

4

Thanks Michael,

I'm using Key Value pairs to update from a separate form for now. This bug also applies to bulk updates via inline editing so I've had to disable inline editing for that particular column for now. Let me know if/when it's fixed and/or you have a workaround.

5

After some digging, it looks like the library we use needs arrays as strings for Postgres specific reasons:

For PostgreSQL, due to incompatibility between native array and json types, when setting an array (or a value that could be an array) as the value of a json or jsonb column, you should use JSON.stringify() to convert your value to a string prior to passing it to the query builder

From Schema Builder | Knex.js . So sending {{ { multiselect1: "[1,2]" } }} works as expected.

6

Thanks. How should this be approached when making a bulk inline edit using changesetArray, for example, where there are multiple columns spanning many data types?

image
image502×626 27.4 KB

7

Maybe something like

tblJobs.changesetArray.map(changeset => {
  return Object.entries(changeset).reduce((acc, [key, value]) => {
    try {
      acc[key] = _.isArray(value) ? JSON.stringify(value) : value
    } catch (e) {
      acc[key] = value
    }
    return acc
  }, {})
})
8

Actually I'll solve this before you get back to me. One incredibly useful function of the retool forum is that it suggests other posts based on the contents of the post you're on. This led to me to another post with someone having a similar issue with bulk updates on Google Sheets. I modified his solution to match my table and column and bulk updates work properly now.

In my examples the field "cert" is the field containing an array that will be inserted into a JSON field in the database.

Solution for bulk inline edit (sources from "Tags" field in table)

{{tblJobs.changesetArray.map(change => ({
  ...change,
  certs: JSON.stringify(change.certs)
}))}}

Solution for passing form data object to insert (sources from Multiselect in form)

{{ 
  Object.assign({}, frmJobEntry.data, { certs: JSON.stringify(frmJobEntry.data.certs) }) 
}}

The post I'm referencing is here:
New table tags array not saving to Google Sheet - App Building - Retool Forum

1 Like