How to generate a json object as key, value using postgres?

Question

I am trying to extract some statistical data from my database. I have an _employees table and I want to get employee count by country. Suppose _employees table has a column country and I have this query for a key-value representation of an aggregate count.

select json_object_agg(key, value) 
from (
        select distinct e.country 
        from _employees as e group by (e.country)
    ) as key, 
    (
        select distinct count(1) 
        from _employees as e 
        group by(e.country)
    ) as value;

I want the data like this

{
     "ET": 100,
     "JM": 245 // And it will go on like this
}

but I've got the following error

key value must be scalar, not array, composite, or json.

Can this be done? Is there a better way to go about this?

klin · Accepted Answer · 2017-07-22 12:00:01Z

11

In the inner query select countries with the number of employees in each country:

select json_object_agg(country, count)
from (
    select country, count(*)
    from _employees
    group by 1
    ) s;

answered Jul 22, 2017 at 12:00

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

How to generate a json object as key, value using postgres?

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