Nested json in a table

Queries and Resources
1

Hi
I currently have a query which returns nested json.

image
image440×612 25.9 KB

initially there are 5 records returned and I really want to access all the data in "results" and display this in a table. There are many "results" entries within each of the 5 records and I am unable to extract them into individual records so I can display in a table

Any help will be hugely appreciated

2

Hello!

Are you trying to combine the "results" sets for all of the records in your query response?

Just getting any one set should be achievable by setting a table's data source to something like {{yourQuery.data.data.results}} -- once you can see the results for one set you should be able to use a JS Query or Transformer to parse the results for any record. At that point you could join the data together or separate out via pagination.

3

thank you but I cant go that low...
it accepts {{sql_js_pagespeed.data.data}} but get the following when trying to add {{sql_js_pagespeed.data.data.results}}

image

4

try let newResult = _.map([your json array],(x)=>x.data.results) - I don't have your full JSON so not able to test. However _.map is your best bet.

It will be easier to put this in a Transformer and then use that in the table vs trying to type in the Data Source field :slight_smile:

https://lodash.com/docs/4.17.15#map

1 Like
5

Thank you
Have to admit Im a bit lost with this function. However, Ive tried to add in to a transformer (as part of the query).
First off I get an error...
Error:Cannot read properties of undefined (reading 'results')

sql_js_pagespeed![](https://retool-edge.com/38449779248593f781bb5b50bcb7d220.svg)
in sql_js_pagespeed transformer, line 5(sql_js_pagespeed)
▶in sql_js_pagespeed.trigger()(sql_js_pagespeed)
1. additionalScope: {}
triggeredById: ""environment: "production"

I only get the error with x.data.results. I dont get the error just with x.data

Secondly, Im afraid I do not know how to compile the json array ([your json array]}

Let say I wanted to include test_uuid field in the results. How would I build the array?

6

Ok so likely the issue is one of your data array elements does not contain a results attribute. Here is another way to handle a missing results attribute.

The array that we need to loop over is the data array that exists in the transformer (the result of the query). This code will go in your transformer.

let newArray = data.reduce(function (array, element) {
      if (element.data.results) {
         array = _.concat(array,element.data.results);
      }
      return array;
   }, []);

return newArray;

What this is doing is looping through the data value in the transformer and checking to see if the results array exists and if it does add the contents of the results array to the array we will return as newArray.

1 Like
7

Absolutely amazing! works perfectly!! Genius!

Thank you so much!! :slight_smile:

1 Like