Query JSON with SQL - Combining Postgres and Salesforce Data

Goal: Get data from a Postgres resource and a Salesforce resource using Query JSON as SQL and display it in a table component


  • Created a table component
  • Created an app resource query - Query JSON with SQL type
  • Entered my query details that reference a Postgres database and Salesforce data
  • Verified my data sources are connected and data is returned by the source queries (set to automatic run)

The query I am trying to execute:

  {{ qryRpts.data }} as reports
JOIN {{ formatDataAsObject(qrySFAccts.data) }} as accounts on reports.accountid=accounts.Id 
JOIN {{ qryRptTyps.data }} as reporttypes on reports.reporttypeid=reporttypes.reporttypeid
ORDER BY reports.reportid DESC

qryRpts - Postgres data source
qrySFAccts - Salesforce data source
qryRptTyps - Postgres data source

I have already:

  • Confirmed the column names match exactly, including case
  • Tried using a transformer to format the data as an object (returns a blank table)
  • Tried changing my query to just select from one of the source queries (same result - undefined data)
  • Tried USING in the Join statement
  • Tried changing the join type (added Left)
  • Tried adding brackets around column names
  • Various iterations of aliases
  • Removed the SF data source and tried the query with just the Postgres data - the results it returned were completely unexpected. I got all of the joined table (qryRptTyps) and none of the columns from the primarily selected table (qryRpts)

Because I'm trying to show this data in a table, I thought I had to format it as an object for the best results. I've tried formatting the whole thing and (as above) just the array data returned from SF - Postgres seems to return object data by default. I can also see the underlying data in the tooltips for each source query but I don't get anything back when I run it as a whole. I've done everything I can to check the AlaSQL syntax and there's probably something exceedingly simple that I'm missing but I can't figure it out for the life of me. :frowning_face:.

Any help is greatly appreciated; I'm also open to other ways to approach this or accomplish the same thing.

Output of query above


Output of query above with transformer applied

1 Like

I solved this myself. What I think tripped me up was my misunderstanding of how the data was coming back from the source queries. I was assuming it was already formatted as an array (being JSON) and it's not. Here is what ended up working for me:

  {{ formatDataAsArray(qryRpts.data) }} as reports
JOIN {{ qrySFAccts.data }} as accounts on [reports].[accountid]=[accounts].[Id]
JOIN {{ formatDataAsArray(qryRptTyps.data) }} as reporttypes on [reports].[reporttypeid]=[reporttypes].[reporttypeid]
ORDER BY reports.reportid DESC

Worth noting:

  • I have no idea if the brackets around the column names are actually necessary but I haven't tried removing them and running the query again.
  • The Salesforce data DOES come back as an array, natively. Again, counterintuitively to my thinking but probably just another example of my not understanding what Retool is returning.

In the end, probably mea culpa but I'm sharing this so someone else doesn't have to spend a week banging their head on this like I did. :person_shrugging:

1 Like