Nested JSON to Table

I have an API connection that returns data in this format:

{
"Jeff": {
"A": {
"Score1": 6,
"Score2": 5,
"Score3": 3
},
"B": {
"Score1": 8,
"Score2": 9,
"Score3": 2
}
},
"Caroline": {
"A": {
"Score1": 1,
"Score2": 10,
"Score3": 5
}
}

There are names, trials A-C for those names, and 3 scores per trial.

I'm having trouble transforming this to come through as a table. When I try it comes through as a table with names as the columns and one row of nested objects for each name.

What I need in the end is Name as a column and highest score in any trial as one, but I could get there from any readable format.

Other solutions posted here have not helped. Does anyone know how this would work?

Thank you in advance.

1 Like

Hey @Roobis!

You can use a JS Transformer query type to write JS to manipulate your query data before passing it along to your table.

Tables accept either an array of objects or an object of arrays. Here's a quick example using your data to separate it into two columns.

const data = {{your_api_query.data}}
let final_data = [];
Object.keys(data).forEach(key => {
  let new_obj = {}
  new_obj['name'] = key
  new_obj['high_score'] = data[key]
  final_data.push(new_obj)
})
return final_data;

Note: I didn't write any logic to loop through the scores to find the highest one, but you can add that in! Let me know if you have any questions at all.

Perfect! That worked. Thank you so much!

Of course! Very happy to hear that. Let us know if we can help with anything else :blush: