Query JSON with SQL - Combining Postgres and Salesforce Data

Queries and Resources
,
1

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

Steps:

  • 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)

Details:
The query I am trying to execute:

SELECT
  accounts.Name,
  reports.reportid,
  reports.contractid,
  reports.reportreceiveddate,
  reports.dateentered,
  reporttypes.reporttypedescription
FROM
  {{ 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
LIMIT 5

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.

Screenshots:
Output of query above
image

Console
image

Output of query above with transformer applied
image

1 Like
2

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:

SELECT
  [reports].[reportid],
  [accounts].[Name],
  [reporttypes].[reporttypedescription],
  [reports].[contractid],
  [reports].[reportreceiveddate],
  [reports].[dateentered],
  [reports].[createdby],
  [reports].[modifiedby]
FROM
  {{ 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
LIMIT 10

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