JSON & Array data types in postgreslq

[jalberto]

Hello,

I am exploring budibase to build a basic CRUD-admin panel for my Postgres DB, I use some jsonb and array columns to store random data.

Is it possible to read/write those data types safely in budibase? if so, which is the best way?

[mjashanks]

Hi José.

To use these types you must use a custom query. You have 2 options on how to handle it

1. Use postgres' syntax to alias a field

e.g.

SELECT personJson->>'email' as "Person_Email" FROM People`

In this case, Person_Email will be part of the query's schema, and available for use in Budibase

2. Select the whole JSON / Array and use a transformer to mutate it

e.g.
Custom query is

SELECT personJson FROM People

Transformer is

for (let row of data) {
   row.Person_Email = row.personJson.email;
}
return data;

We might soon handle JSON more natively - i.e. in the Fetch Tables feature - then we could use the whole object in javascript in the Design section. But we're not there yet!

[mjashanks]

@jalberto Actually I just realised that we can also just select the whole column in the query

SELECT personJson FROM People

Then, in your bindings in the Design section, you can use it like a JSON object, so:

Handlebars: {{ New Repeater.person JSON.personJson.email }}
Javascript: return $("New Repeater.person JSON.personJson").name

[jalberto]

Thanks! I can work with these tips.

My main concern is how safe is to update the json/array field, as mostly tools I tested do it wrong (as a string or allowing not valid json)

[mjashanks]

Yes, this is something that we've talked about - i.e. natively supporting a JSON data type. We would like to do this soon, but not sure when.

I think that you should be able to update JSON safely, using Javascript bindings, in the "Save Row" action:

When you open the binding (the lightning bolt), you can use javascript to return a javascript object:

[jalberto]

thanks :) will try it

In other hand, maybe good idea to add some kind of % of support features per DB? Just to avoid surprises.

[jalberto]

also, I guess support JSON/arrays as datatypes is key to work with other DBs like elasticsearch where every attribute can be an array and nested jsons are very common

[mjashanks]

Totally agree. You can get pretty far with transformers & javascript bindings right now. But there's a lot more we can do too.

[garyboyes]

@mjashanks sorry to drag up an old issue, but has there been any progress around this issue regarding native json/jsonb support for imported tables? Still seems to show up as object Object in the table imports/through select * on a table with a json or jsonb column

[oren0e]

@mjashanks any updates on native json/jsonb support when working with external connected DBs like postgres? This is a must have for me as my whole application is using a jsonb column.