How to format a JSON array from a PostgreSQL data source for use in my app


I am brand new to ReTool and have limited coding experience, so this may be a really "easy" question. I am pulling in data from an Airtable database using FiveTran and the columns that contain Airtable mult-select type data return as JSON arrays. How can I use these data so that they show up and can be manipulated as a "tags" column type in ReTool? Thanks!

Hi @arle, welcome to Retool!

It looks like you've got that data tied to a table, which is step one. Your data just needs to turn your array of tags into a comma separated list.

You can do this by selecting the table, and in the upper right menu, in the content section, click on your Approved Roles column in the Columns section. You'll see a form like this for modifying the properties of your column.


Change the Format to "Tags" and it should work. I

That mapped value probably just has a grey {{ item }} and it should handle your array properly, but if you need to modify the data in some other way, you can put JS just about anywhere. Change the value in the Mapped value input to {{ item.split(', ') }} which will comma separate the values in your array.

Thank you so much - that worked - any ideas for how to format the string that comes from the changeset array so that I can then UPDATE the table in proper JSON format? See error.


It seems that the problem may be that when a column of data in a data table contains type "Tags" it is not formatted correctly in the changesetArray function for it to go straight into PostgreSQL properly formatted for a JSON column type. I am guessing I would need to create some kind of transformer to take whatever formatting that Tags column in a given table generates as part of the changesetArray function and make it into formatting that would be properly formatted for the database update. Am I onto something here? Thanks!

I figured it out but there's almost certainly a better way to do this - open to suggestions. I used ChatGPT to figure it out (link to my chat: ChatGPT).

I created a transformer that makes the necessary modifications so that the array is formatted in a matter that PostgreSQL wants for arrays.