Multiple aggregations into one graphql query

I am trying to aggregate multiple values in one graphQL query. It seems to be impossible to do this (Using Hasura GraphiQL to build the query). It is possible to do something like it in Postgres.

Background
I want to show statistics about my advertisement's number of views (shown), clicks and dismisses. In graphQL I need to make separate queries for interaction_type= "shown" , "dismissed" or "clicked"

query AdStatistics {
  ads_interaction_aggregate(
    where: {created_at: {_gte: "2023-06-22"}, 
      interaction_type: {_eq: "shown"}, 
      ad_id: {_eq: "7e9250bc-4f85-45ad-900d-f08558ebc775"}}) {
    aggregate {
      count(columns: [interaction_type], distinct: false)
    }
   
  }
}

My goal is to re-create this SQL query in GraphQL.

select 
    count(*) FILTER (WHERE interaction_type = 'clicked') as clicked,
    count(*) FILTER (WHERE interaction_type = 'shown') as shown,
    count(*) FILTER (WHERE interaction_type = 'dismissed') as dismissed
    
    from ads.interaction group by ad_id

Disclosure: I'm a developer at Hasura.

At the moment, Hasura doesn't directly support what you want. However, we're currently thinking about adding group_by support to aggregations that in the future will allow you to group by ad_id, then interaction_type, then apply a count aggregation. Please see this GitHub issue where we've put out an RFC.

For now, the easiest way to achieve what you want is to use the Native Queries feature. Native queries should allow you to take your desired SQL snippet and query it from GraphQL.

For example, you could use this parameterised SQL query in your Native Query:

select 
    ad_id,
    count(*) FILTER (WHERE interaction_type = 'clicked') as clicked,
    count(*) FILTER (WHERE interaction_type = 'shown') as shown,
    count(*) FILTER (WHERE interaction_type = 'dismissed') as dismissed
from ads.interaction 
where created_at >= {{created_since}}
group by ad_id

and then once that's set up as a Native Query named ads_interaction_summary, you could query it from your GraphQL like so:

query {
  ads_interaction_summary(
    args: { created_since: "2023-06-22" }
    where: { ad_id: {_eq: "7e9250bc-4f85-45ad-900d-f08558ebc775"} }
  ) {
    clicked
    shown
    dismissed
  }
}

1. Define a new type:

type ViewStats {
  clicked: Number
  shown: Number
  dismissed: Number
}

2. Use this field in the parent object, ex:

type Advertisement {
  id: ID!
  …other fields
  stats: ViewStats
}

3. In the field resolver for the stats field under your Advertisement object use your previously defined query:

select 
    count(*) FILTER (WHERE interaction_type = 'clicked') as clicked,
    count(*) FILTER (WHERE interaction_type = 'shown') as shown,
    count(*) FILTER (WHERE interaction_type = 'dismissed') as dismissed
    
    from ads.interaction 
    where ad_id=${parent.id} 
    group by ad_id

That query will return clicked, shown and dismissed which will populate the 3 fields of the ViewStats type.