Query JSON with SQL - Combining Postgres and Salesforce Data

💬 Queries and Resources
,
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