How should I incoporate REST API query result to my POSTGRESQL query

Queries and Resources
, ,
1

I have a Google analytics Oauth 2.0 REST API query. I was able to successfully retrieve the ItemId dimension, and itemsViewedInList and customItem:dream_program_vocation_adjective metrics. What's currently happening is I have two display table components right now:
Table 1 - google analytics
Columns: Program ID, itemsViewedInList, and customItem:dream_program_vocation_adjective

Table 2 - postgresql query (from our database)
Columns: Program ID, number of enrollments

I would like to use the result in a postgresql query to match the program_id in my sql database to the ItemId from google analytics and I would like to display the combined query result in one table component.
So it should look like:
Columns: Program ID, itemsViewedInList, and customItem:dream_program_vocation_adjective, number of enrollments

Unfortunately, I can't use currentsourcerow in my postgresql query but instead just selectedRow so what happens is every time I click on a row that's the time that it will display the number of enrolments. However, I want the table to the corresponding number of enrollments per program ID.

I am aware that there is a way to transform the result of my google analytics REST API and that could potentially help me in this scenario. Unfortunately, I am a beginner in all of this so I'm not sure how to do that yet. For more context here are the queries.

GA oauth:
{"dimensions":[{"name":"itemId"},{"name":"customItem:dream_program_vocation_adjective"}],"metrics":[{"name":"itemsViewedInList"}],"dateRanges":[{"startDate":"30daysAgo","endDate":"today"}],"metricAggregations":["TOTAL"]}

postegreSQL:
select COUNT(*) from enrollment_item where program_id = {{ table2.selectedRow.dimensionValues[0].value }} and status = 'LEAD'

Looking forward to your suggestions!

2

Have you tried to Query JSON with SQL and do the join there?
image

Something along the lines of the following, where GAoath is the query currently populating your "Table 1" and postgreSQLqry is the query populating your "Table2":

select 
  *
from 
  {{ GAoauth.data }} t1
  join {{postegreSQLqry.data }} t2
  on t1.itemId = t2.program_id
3

Unfortunately, I encountered this error

image
image838×570 25.4 KB

Disclaimer: those are my exact table names.

4

You need to put the column in brackets due to the - in the field name:

select 
  *
from 
  {{ table2.data }} t1
  join {{table3.data }} t2
  on t1.programId= t2.[program-id]
5

Thanks for your help, @jg80! :raised_hands:

Did that work for you, @Dreambound_Services?