I have a JSON response which includes an array within each object as per screenshot
"totalSales": {
"amount": 1564.51,
"currencyCode": "GBP"
}
There are x amount of items in array. How do I sum all occurences of 'amount'? I can only seem to access each individually with this SQL query:
select * from {{Amazon_SalesAPI_SALES.data.payload['0'].totalSales.amount}}
I know I need to do select sum(*) from X but not sure what the correct structure is to access 'amount' within the totalSales array within each object?
I have solved this myself. I found out about the arrow function. To do what I want I wrote the following query: select sum(totalSales -> amount) from {{DATA}}.
Hope it helps someone.
@happylinen Thank you so much for posting your question and circling back to share the solution you found! I'm sure this will help other Retool users in the future, and I'm glad it's now searchable in our community forum.
Here's a PostgreSQL doc I found describing the operator you used. You used **-> **to extract "JSON object field with the given key" where amount was the key in this case.