Aggregate count over variable number of json keys How can one get a list of json objects like this:

[
    {"band": "beatles", "bass": 1, "drums": 1, "guitar": 2},
    {"band": "metallica", "bass": 1, "drums": 1, "guitar": 2, "vocals": 1}
]


from this data on Postgresql 9.4?

drop table if exists people;
create temp table people(pk serial primary key, band character varying(15), fname text, tag character varying(15));

insert into people(band, fname, tag) 
values
('beatles', 'john', 'guitar'), 
('beatles', 'paul', 'bass'),
('beatles', 'ringo', 'drums'),
('beatles', 'george', 'guitar'),
('metallica', 'james', 'vocals'),
('metallica', 'lars', 'drum'),
('metallica', 'kirk', 'guitar'),
('metallica', 'robert', 'bass'),
('metallica', 'marty', 'guitar');

--Summary table
select band, tag, count(*)  as ct from people  group by band, tag order by band, tag


This is my closest attempt:

SELECT band, tags from (
    select band, row_to_json(row(tag, count(*))) as tags from people group by band, tag
) t

-- Which leads me towards:
SELECT band, array_agg(tags) from (
    select band, row_to_json(row(tag, count(*))) as tags from people group by band, tag
) t
GROUP BY t.band;

--This is close, but from *{f1:v1, f2:v2}* I need *{v1:v2}*


Here is how I arrived to that one:

select array(SELECT row_to_json(r) from (
    SELECT band, array_agg(tag) from people group by band
    ) r
)

select row_to_json(r) from (
    SELECT band, array_agg(tag), count(*) from people group by band
    ) r;

select row_to_json(r) from (
    SELECT band, json_agg(tag), sum(ct) from (
        select band, tag, count(*)  as ct from people  group by band, tag
    ) x group by x.band
) r;
The only one thing which I not found is the function/operator for concatenation of JSON objects, so lets create it:

create function json_concat(in json, in json) returns json as $$
  select
    (
      left($1::text, length($1::text)-1) || ', ' ||
      right($2::text, length($2::text)-1)
    )::json; 
$$ immutable language sql;


After this the final query would be:

with t as (
  select band, tag, count(*) as ct 
  from people
  group by band, tag
  order by band, tag)

select json_agg(x)
from (
  select json_concat(json_build_object('band', band), json_object_agg(tag, ct)) as x
  from t 
  group by band) tt;


and result is

[{"band" : "metallica",  "bass" : 1, "drums" : 1, "guitar" : 2, "vocals" : 1 }, {"band" : "beatles",  "bass" : 1, "drums" : 1, "guitar" : 2 }]