Hello everyone!
I have a table with two columns: created_at and impression_count
Very simply, I'd like to group the data by created_at and take the sum of impression_count for each group.
After that, I'll chart the time-series data with the newly aggregated data.
All would be quite simple in Python/Pandas (my native tongue), but I'm having trouble finding grouping functionality/writing the proper JS in Retool.
Any and all help is greatly appreciated!
This really should be done in the query using group by and/or sum so if you can post your query here it might be more helpful for me to edit it and determine how it should be written
@ScottR agreed! So I'm originally pulling from a rest API and using a JS query to loop through each page of the API results:
All is well there.
I then pull the results of the JS query into the table below:
The JSON for the JS query looks like this, so I had to use some custom columns in the table to get things formatted properly:
All this said, it looks like I could either 1) write a transformer for the original JS query so that the results are grouped by created_at or 2) create a 'Query JSON with SQL' resource to modify the result of the original JS query.
Hopefully this is right! I'm a bit of a Retool noob.
This would be the way to go....
Also, this post may put you in the right direction:
@ScottR Thank you for this! Reading through this post now. I'm facing a similar issue with accessing all indices inside a query:
SELECT {{ Twitter_Tweets_All_JS.data[i].created_at }}, SUM({{ Twitter_Tweets_All_JS.data['public_metrics'][i]['like_count'] }})
FROM {{ Twitter_Tweets_All_JS.data }}
GROUP BY {{ Twitter_Tweets_All_JS.data[i].created_at }}
I'm sure there are cleaner ways right to this by assigning variables -- Where i am going wrong?
@jordanp What is the error, if any, that you are seeing or what is the result?
@ScottR it looks like created_at isn't getting retrieved properly, so like_count doesn't aggregate correctly either. i assume it has something to do with how i use index i.
@jordanp Hard to say what it could be with not being able to see your code.... I would type out one element at a time where your seeing that error and see what Retool suggests as you add a . after each element... it may help you get to the correct structure you need within the SUM....
@ScottR i figured it out!
i was overcomplicating the syntax. looks like Retool SQL automatically recognizes keys in a JSON when you specify the resource in the FROM statement.
for accessing nested JSON keys, you can use AlaSQL's -> instead of .
hope this helps others in the Retool community!
