Format JSON object array keys as separate columns

App Building
#1

Response from a bulk get query to a rest API is a an array of objects containing made up PII as follows:

{
"records": [
{
"fields": {
"date_of_birth": "2003-02-02",
"email_address": "cslat.co.jp",
"name": "Abigne",
"ssn": "",
"state": "Massachusetts"
}
},
{
"fields": {
"date_of_birth": "1985-02-13",
"email_address": "gsqust.fm",
"name": "Feotrey",
"ssn": "",
"state": "Colorado"
}
}, ........]}

When loading this data in a table all the records show up in one column:

image
image828×718 67.4 KB

I would like to have each subfield as a separate column in my retool table. For example, for each record, I would like to have the following columns:

Name | Email Address | State

I'm not very familiar with JS so any help is appreciated.

#2

Welcome to the forum!

You should probably build a transformer, potentially combined with some array flattening from lodash (built-in to Retool already).

Can't help you much more without know your data shcema :-).

#3

Found the answer! Thanks to @victoria

Here is a copy of my transformer that can create a table with columns for the aforementioned payload:

const data = {{query1.data.records}}
let final_data = [];
Object.keys(data).forEach(key => {
let new_obj = {}

new_obj['EmployeeID'] = data[key]['fields']['employee_id']
new_obj['Name'] = data[key]['fields']['name']

final_data.push(new_obj)
})
return final_data;

#4

Hi @ajose Thanks for sharing! :pray: Thanks to @victoria as well :100: