Aggregating multiple data sources in a single app

Hello, I need to display data sitting across multiple databases with some in-memory joining. Sort of along these lines:

  1. Run query A
  2. For each row from query A, parse JSON returned in a column and run query B augmenting the data
  3. Once the data is aggregated in steps 1 & 2, run query C and do an in-memory mapping of the results, further augmenting the data set

Once all of these steps are complete, render results in one big table. The total dataset is not too big, just a few hundred rows, so should not be a showstopper.

What's the best way to structure app for this functionality?

Would it make more sense to join the data externally and then just return a uniform dataset, or we can still accomplish that with minimal effort via scripting?

That would be my approach.... while you can accomplish the same while using some scripts in js in Retool or using Query JSON with SQL, I would advise trying to join as much as you can using JOIN in your queries... IMO it would be easier to manage and potentially faster to load.

Thank you for taking a look Scott! This is sort of been my criticism of most of the low-code tools. It's great in theory, but once you start hitting real-life scenarios it becomes a pain and you gotta work agains the tool... I can't do much joining in the DB, because it's three SEPARATE DB unfortunately, so now I need to provision a separate project/runtime, etc... So re-tool becomes less efficient. I always wondered, how hard it might be to just allow devs put in their own scripted pages with hooks into re-tool infrastructure.... Oh well, in my dreams, I guess...

You can use custom components in Retool and I wouldn't necessarily agree with regard to your 3 DB scenario... You can simply create three resources and run queries against each...A combination of some solid scripting and using temp states, etc.. and you should be able to achieve what you want with much less effort than building from the ground up... just my 2 cents...