How to flatten nested object values?

Queries and Resources
1

Hi Retool team,

I have a restAPI response and am wondering how to extract all the JSON keys and use them as table fields.

The API data looks like this:
"d": [
{
"id": 1,
"user": {
"name": "Jonas",
"age": 19,
"occupation": "teacher"}
},
{
"id": 2,
"user": {
"name": "Martha",
"age": 20,
"occupation": "manager"}
]

The table should have these fields: id, name, age, occupation.

Thank you in advance for the help!!!!

2

Hey @doris.l! If you're trying to load this data into a Retool Table, we do the work of setting up the column names automatically. So if your API response looks like the above, you'd want the "data" field in your Table settings to have apiQuery.data.d and everything should work.

If that doesn't match your use case, you can always extract the keys from an array of objects like so:

// Returns array of keys for each object
return responseData.map(item => Object.keys(item))

// Returns first set of keys from array
return responseData.map(item => Object.keys(item))[0]

Let me know if this helps!

3

Hi @justin ,
Thank you so much for the help!
Using apiQuery.data.d with the table component did give me a table with id and user column. But the user column has nested values. The first value of user column is {"name": "Jonas", "age": 19,"occupation": "teacher"}. I want to flatten the nested object and have name, age, occupation columns displayed along with other columns.
I know one option is to create additional customer columns and flatten the values using {{currentRow.nestedKeyName}} in Calculation. But I'm wondering if there are other solutions.

Thank you again for the help!

4

Hi,

any updates on this issue ? I am facing the same problem, making custom columns is very time consuming and difficult to maintain

.V

5

Sorry for the delay folks. As far as I can tell, there are two options here:

  1. Custom columns

Looks like you've both already figured this out, but you can just create any arbitrary number of custom columns that reference nested values in the data backing the table. Agreed that this can be time consuming and hard to maintain.

  1. Flatten the data in advance with a JS transformer

There's almost definitely a more elegant way to do this, but you can manually pull out item properties and restructure your array of objects. This worked for me:

const flattenUser = (obj) => {
  var newObj = {}
  newObj.id = obj.id
  newObj.userAge = obj.user.age
  newObj.userName = obj.user.name
  newObj.userOccupation = obj.user.occupation
  return newObj
}

newData = []

data.d.forEach((obj) => {
	const flattenedObj = flattenUser(obj)
  newData.push(flattenedObj)
})

console.log(newData)

You can add this into a query transformer on the query that returns your data and then return the transformed data. Hope this helps!

1 Like
6

Hi, I have the same question (in my case, I have three n rows * 2 columns arrays coming from Monday.com's questionably designed graphql api. I want a single n x 6 object or array to display in the table and query from other objects. Creating a new object and pushing works, and I've also seen (but didn't super-understand) the query json example with UNION.

But I'm wondering why array.prototype.map() or forEach(), then element.concat doesn't work for me? - it seems that the arrow function expression scope doesn't have access to built-in javascript functions. Either that or the input params aren't in the same scope as the expression?

const category = {{formatDataAsObject(formatDataAsObject(Projects.data.boards[0].groups[0].items).column_values)[0]}}
const budget = {{formatDataAsObject(formatDataAsObject(Projects.data.boards[0].groups[0].items).column_values)[1]}}
const name = {{formatDataAsObject(Projects.data.boards[0].groups[0].items).name}}
const table = category.map((element, i) => element.concat(budget[i],name[i]))
  return table

Whether I do this as the category.map or category.forEach method, the error always comes back: element.concat() function is not defined.

Is there some reason I missed why the object doesn't have the concat function in its context in that location?

7

Hey sbowlin, I think this has to do with how the data is returned and stored in the variables (category, budget, etc). If you take a look at those via console log, what shape do they have. I'd imagine you are trying to call concat on an object that is not an array and therefore does not have access to the concat function. Feel free to share some screenshots so we can help get to the bottom of this for you!

8

This helped me today. Thank you