result.pl

{
  queues => {
    default   => {
      active_jobs   => 1,
      inactive_jobs => 4,
      failed_jobs   => 1,
      finished_jobs => 5
    },
    important => {
      active_jobs   => 1,
      inactive_jobs => 0,
      failed_jobs   => 0,
      finished_jobs => 2
    }
  }
}

script.pl

my $stats = $pg->db->query(
  "select json_object_agg(queue, queue_state_count(queue)) from (
    select distinct queue from _table
  ) x"
)->expand->array->[0];

sql_functions.sql

-- Run once to create functions needed to generate stats

create or replace function job_count(arg_queue text, arg_state text) returns bigint as $$    
  select count(*) from _table where state = arg_state and queue = arg_queue                                
$$ language sql stable strict;                                                                           

create or replace function queue_state_count(arg_queue text) returns jsonb as $$          
  select jsonb_object_agg(state || '_jobs', job_count(arg_queue, state)) from (
    select unnest(array['active', 'failed', 'finished', 'inactive']) state) x
$$ language sql stable;

-- The state column can be dynamically generated for the function above:

--create or replace function queue_state_count(arg_queue text) returns json as $$          
--  select jsonb_object_agg(state || '_jobs', job_count(arg_queue, state)) from (
--     select e.enumlabel from pg_type t, pg_enum e where t.oid = e.enumtypid and typname = 'minion_state') x
--$$ language sql stable;

-- The advantage of this is that should a new state be required and be added to the type, it would automatically
-- appear in the queues' stats.

table.txt

   id   |   queue   |  state   
--------+-----------+----------
 270007 | default   | finished
 270005 | default   | inactive
 270006 | important | finished
 270009 | important | active
 270010 | important | finished
 270011 | default   | active
 270021 | default   | inactive
 270024 | default   | finished
 270023 | default   | failed
 270022 | default   | finished
 270025 | default   | inactive
 270027 | default   | inactive
 270026 | default   | finished
 270028 | default   | finished