Displaying nested JSON in a table

App Building
1

Hi! I'm entirely new to this...and have hit a problem with the very first thing I've tried. I'm querying for some JSON (see below) via an API, which is returning what I'm asking for OK. However, when I view it within a table, it's putting the entire content of the returned line within a single column called 'GAZETTEER_ENTRY'. I can see why it's doing this, but how do I tell it that I really want 'ID', 'Name' and 'Type' to be the three separate column headers? Can I change the code in the Data block to request this? It's currently set at {{ myquery.data.results}}, but adding '.GAZETTEER_ENTRY' to the end of that string (which was my guess at solving it) didn't seem to work. Any ideas, please?

Guy

{
"header": {
"query": "Glasgow",
"format": "JSON",
"totalresults": 1
},
"results": [
{
"GAZETTEER_ENTRY": {
"ID": "12345",
"NAME": "Glasgow",
"TYPE": "populatedPlace"
}
}
]
}

1 Like
2

I took your JSON and added it to a temp state as a test and then used a table component and added the value as [{{state2.value.results['0'].GAZETTEER_ENTRY}}]. Note here that I put [ and ] around that to make it an array...

Screen Shot 2022-09-23 at 11.02.43 AM
Screen Shot 2022-09-23 at 11.02.43 AM2188×1264 237 KB

1 Like
3

Ah, many thanks for that! It's now working for me at this end, also. Cheers.

Guy

4

@GuyHeathcote - happy to help
If you are running a query or an API that pulls that data in you can just take that resource name and replace "state2" so you don't have to use the temp state and populate the table directly....please mark as solution above so other people can find it. Best of luck

1 Like
5

hi @ScottR ,

I am having a similar issue when querying with the Airtable API.

I am using the RESTQuery (rest api) and fetching data into a table with :

{{formatDataAsObject(query1.data.records)}}

Works fine to display id, created time and fields.

I would like to display the nested data from {fields}, tried using your solution but was not successful, any tips to make that work ?

{
  "records": [
    {
      "id": "rec008skQpGLnGYh7",
      "createdTime": "2021-10-10T15:04:55.000Z",
      "fields": {
        "name": "test1,
        "email": "test1@test.com"
      }
    },
    {
      "id": "rec00HHwPWEqD4krs",
      "createdTime": "2021-11-14T15:00:41.000Z",
      "fields": {
        "name": "test2,
        "email": "test2@test.com"
      }
    }
],
  "offset": "itrnwLZgJoR8iDeAO/rec08b8yJSBNqTrtq"
}
6

Hello @intimur,

You would need to use JS to manipulate your data to get the items you need within this nested field.

Here is a sample code that takes in your value, use Map to retrieve the values within "fields"

let x = {{ state1.value.records }}
let y = x.map(element => ({"id": element.id, "createdTime" : element.createdTime, "name": element.fields.name,"email": element.fields.email }))
return y

1181×799 58.9 KB

1 Like
7

Thanks for your reply & solution! it works perfectly

8

Please be gentle, I just started learning retool.
I just tried your solution. How come that the retool transformer fails and says "x.map is not a function". Do I miss something here?

EDIT: I just figured it out. Seems so I was not passing my tempState data as an array. Bracketing [] my data solved the error message.