Unable to access nested json from mysql query

App Building
#1

I’ve got a similar n00b question to (n00b) JSON response isn't recognized as data (?)

Here’s a sample of the data in question below:

{"results":{"total_rejected_recipients":0,"total_accepted_recipients":1,"id":"6948127074738232688"}}
{"results":{"total_rejected_recipients":0,"total_accepted_recipients":1,"id":"6948448299636518119"}}
{"results":{"total_rejected_recipients":0,"total_accepted_recipients":1,"id":"6948418110311503604"}}
{"results":{"total_rejected_recipients":0,"total_accepted_recipients":1,"id":"6948127242241955943"}}
{"results":{"total_rejected_recipients":0,"total_accepted_recipients":1,"id":"6948193006780987857"}}
{"results":{"total_rejected_recipients":0,"total_accepted_recipients":1,"id":"6948438507111124426"}}
{"results":{"total_rejected_recipients":0,"total_accepted_recipients":1,"id":"6948449283184027240"}}
{"results":{"total_rejected_recipients":0,"total_accepted_recipients":1,"id":"6948449339018578458"}}
{"results":{"total_rejected_recipients":0,"total_accepted_recipients":1,"id":"6948448497204987814"}}
{"results":{"total_rejected_recipients":0,"total_accepted_recipients":1,"id":"6948437678182411404"}}

I’ve tried {{ data.results }} and many similar combinations to no avail, when I type data. and attempt tab completion, retool returns hints to reference each character in the string associated with each value.

image
image988×624 65 KB

Thanks in advance!

#2

Hi there, It looks like the data is nested under the “results” key, which causes the raw JSON files to return in your table. The table components expects an array of objects or object of arrays. Since this data is nested, it needs to be converted to either one of these options. To have the table display properly, you can use a JS map function to unnest this data into an array of objects. Can you try using this code snippet in the tables data input? 

{{ searchEmailByUserId.data.map(row=>row.results) }} Let us know if this doesn’t work!
#3

Thanks @grace, unfortunately no luck…

image
image804×682 62.5 KB

#4

Would you be able to share a screenshot of what your searchEmailByUserId.data property looks like expanded out in the left panel of the editor (opened in the top middle of the screen)? Having the error of “.map is not a function” makes me think that it isn’t an array currently

#5

@alex-w that sounds logical to me, see below:

image
image532×1438 130 KB

#6

@alex-w hate to nag but does the screenshot help?

#7

It does! It looks like these are all coming in inside of data.providerResponse as strings:

searchEmailByUserId.data.providerResponse.map(row=>JSON.parse(row).results)
If you would always want to parse the return this way, I would recommend adding something like this as the Query’s transformer:
return data.providerResponse.map(row=>JSON.parse(row).results)
1 Like
#8

This did the trick, thank you @alex-w

1 Like