PostgreSQL : access data in JSON field

Not sure why it’s doing that as I haven’t played around with fields that contain JSON. It’s actually quite interesting, I should look into it.

Potentially Skuid is attempting to translate the JSON as well, hence no treating it as a string.

Add Quotations Approach
That might be something to try, add quotation marks around your JSON data before returning it.

Payload Snippet Approach
Since you’re using Postgres DB, I assume you’re using REST Model, you can add payload snippet to your query .

Add the following lines to the start of the snippet

const params = arguments[0];
let responseBody = skuid.utils.parseAsJSONIfString(params.response.body);

then you can interact with responseBody where response body data type will depend on what your Postgres is returning, but most likely it will be of [{}] (aka. list → object, a.k.a. list of objects).

You transform it how ever you want. So, when you’re looking at your JSON object (e.g. responseBody[0].batch) you can return

Make sure the snippet returns a variable that has type of list of objects (a.k.a. [{}], a.k.a. list → object), because skuid will attempt to put the data into the model.

You’ve probably went through it, but the link bellow is documentation on Rest Models
https://docs.skuid.com/latest/en/data/rest/

Does that make sense or help?

Best,
Lukas

I am using the native connector which I don’t think allows you to change how you call or retrieve the data?

Ethan - this is a current limitation of Skuid - we don’t have built in support for JSON being returned as a field value. We don’t have a renderer to parse the string into a set of key value pairs alongside the model.

You are going to have to do some processing after model load to extract the batchId value from the JSON string and push it into a new field in your model.

Lukas is on the right track with a code solution - but since this is not a REST model - you’d simply have to put that code in a snippet that is triggered in a model action (on model requery)

Alternatively - you might get some success with a UI-Only formula field that parses the JSON string and returns a set of specific characters. (Using our Text Manipulation functions) I think it should penetrate the JSON string - but I have not tried.

Just to close the loop, I create a view in the DB with the data I needed and pulled it in that way. This might be a feature that when a JSON field is present you can transverse into the key-value pairs and display that data in the field. If you wanted more than one value you would need to create more fields using the same table/field.

Glad you got it sorted.