How to save json to a JSONB field in retool DB/Postgres in a workflow

💬 Workflows
1

Some of my json properties (returned from an API) contain objects/arrays that I want to save in a JSONB field in the retool database. However for this I have to manually stringify those fields, otherwise I cannot use the (very comfortable way) of just sending that json do the database via GUI mode (upsert). Did anyone found a solution to this?

2

Hi @Istvan_Palfy,

Yes! This is a common issue when working with JSON/JSONB fields in Retool Workflows + PostgreSQL.

When you're using GUI mode (Insert/Upsert) in a Retool workflow, the Postgres driver expects JSONB fields to be strings (i.e., manually stringified) — but it doesn’t automatically do the stringification for nested objects/arrays inside your JS blocks or REST API responses.

If you're using GUI mode (Upsert) and want to keep using the Retool-friendly way of passing data as key-value objects (rather than raw SQL), you need to manually JSON.stringify() the nested object/array before passing it to the database.

Let’s say your REST API gives you this:

{
  "id": "abc123",
  "name": "Test Record",
  "metadata": {
    "source": "SAP",
    "tags": ["finance", "priority"]
  }
}

In a transformer block:

const raw = apiQuery.data; // or wherever your response is from

return {
  id: raw.id,
  name: raw.name,
  metadata: JSON.stringify(raw.metadata) // manually stringify the nested object
};

Then in your Upsert block (GUI mode), use the return value of that transformer as input.

3

Unfortunately: True, I know this.

Now image you receive huge json files via REST with dozens of „real“ properties (to be stored in columns) and a lot of properties meant to be stored in JSON fields.

So this might be an easy to code feature request then, since the retool query object has all schema information anyway. So if the query „knows“ that it has to write into a JSON(B) field, why doesn’t it do that stringification automatically?

4

You're absolutely right — Retool could handle this more gracefully, and many of us have thought the same thing when juggling large JSON objects with mixed property types.

If you want to add a feature request, let me know and I would definitely give it a +1!

Until This Is fixed, if you’re consistently mapping certain fields to JSONB, you can make a little helper like:

function smartStringify(row, jsonFields = []) {
  const result = { ...row };
  jsonFields.forEach(key => {
    result[key] = JSON.stringify(row[key] ?? {});
  });
  return result;
}

// Usage
return apiResponse.data.map(row => smartStringify(row, ['metadata', 'details', 'extras']));
5

Wow!

That helper is a nice, flexible and simple one! I also tried to automate this but ran out of time today.

Thanks for this!

1 Like
6

Glad I could help!

1 Like