Postgres: How to get json array of multiple boolean columns?

Question

PG_VERSION: 9.2

I have got a table with multiple boolean flags.

Eg:

id,flag1,flag2,flag3

Problem is, I want to get json array of these flags with its count like:

[{"title":"flag1","count":2},{"title":"flag2","count":3},{"title":"flag3","count":0}]

How to achieve this result? Tried this

    create or REPLACE function count_test(OUT json_string text) returns text
LANGUAGE plpgsql    AS $$
BEGIN    SELECT array_to_json(array_agg(rows))into json_string FROM ( select sum(case when flag1 then 1 else 0 end) as flag1,
sum(case when flag2 then 1 else 0 end) as flag2,
sum(case when flag3 then 1 else 0 end) as flag3 FROM flags
) rows;
  END;
$$;

but with this I can only get [{"flag1":2,"flag2":2,"flag3":0}]

Awaiting immediate response.

instead of case I'd rather cast flag1::int?.. so sum(flag1::int) looks more readable — Vao Tsun
– Vao Tsun, Commented Sep 11, 2017 at 7:15

Vao Tsun · Accepted Answer · 2017-09-11 07:29:32Z

1

you could aggregate row_to_json results to array and then try casting it to json and so on, but considering the very limited json support in 9.2 I'd just build json from string, eg:

postgres=# with flags(id,flag1,flag2) as (values(1,true,true),(2,false,true))
, s as (select sum(flag1::int) as f1, sum(flag1::int) as f2 from flags)
select concat('[{"title":"flag1","count":',f1,'},{"title":"flag2","count":',f2,'}]')::json from s;
                          concat
-----------------------------------------------------------
 [{"title":"flag1","count":1},{"title":"flag2","count":1}]
(1 row)

edited Sep 11, 2017 at 7:29

answered Sep 11, 2017 at 7:20

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: How to get json array of multiple boolean columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related