Query Across Different Resources causing groups

Queries and Resources
1

My query across different resources using "Query JSON with SQL is returning only two columns and they seem to be grouped by keys.

I found a similar topic that seemed to be the answer, but the return is unexpected. I want it to return all of the rows.

select * from {{ p6_activities.data }} a join {{ Project.data }} b on a.c_proj_id=b.p6project

2

Hello @durhamj,

The issue you're facing with the Query JSON with SQL is likely due to the way the JSON data is structured and interpreted. When you perform a join in a JSON query, the result might default to grouping by keys unless explicitly handled

  • Inspect Your JSON Structure :
    Ensure that the structure of {{ p6_activities.data }} and {{ Project.data }} is
    compatible for a join. For example:
    {{ p6_activities.data }} should contain a field like c_proj_id.
    {{ Project.data }} should contain a field like p6project.
SELECT 
    a.activity_id,
    a.activity_name,
    b.project_id,
    b.project_name
FROM 
    {{ p6_activities.data }} a
JOIN 
    {{ Project.data }} b
ON 
    a.c_proj_id = b.p6project
5 Likes
3

Hi @durhamj,

Can you share data from p6_activities and Project so that we can sort out why this is happening or how to modify the query?