Joining two queries using Query JSON with SQL

Queries and Resources
1

Hello :wave:,

I'm joining two tables from the different databases (Postgres and Snowflake) using Query JSON with SQL. I tried the solution in the doc. It returns 0 rows, no matter if formatDataAsArray is used.

select * from {{ postgres.data }} as p
    join {{ snowflake.data }} as s on p.user_id = s.id

select * from {{ formatDataAsArray(postgres.data) }} as p
    join {{ formatDataAsArray(snowflake.data }} as s on p.user_id = s.id

I'm also wondering if there's any doc about how to do aggregation using Query JSON with SQL.

Thank you in advance for the help.

2

Hey @doris.l,

Are these two queries getting triggered before you run the above?

Meaning, for you to access .data of a query it needs to be executed first. May not be the problem at all just thought I'll point it out because the joins look ok otherwise.

3

Hi @minijohn,

Thanks for the reminder. I found the issue is the column names in queries need to be the same case as they are in tables. Just need to pay attention to whether the columns are written in capital or not. Now query starts working like a charm! Thank you.

1 Like