Nested JSON to Table

Queries and Resources
#1

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
#2

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.

2964×1632 429 KB

#4

Perfect! That worked. Thank you so much!

#5

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

#6

In this example, if A and B were to be displayed as separate columns, how would you go about modifying the script?

#7

You’d add another key:value pair to the new_obj! Something like:

new_obj[‘name’] = key
new_obj[‘high_score_A’] = data[key][‘A’]
new_obj[‘high_score_B’] = data[key][‘B’]

Let me know how that works for you :slight_smile:

#8

Yes that worked, thank you so much Victoria!!

1 Like
#9