postgresql json: keys as values

Question

I just discovered the json capabilities of postgresql but have trouble understanding how to generate json with queries. I hope the question I am asking makes sense and please excuse me if I am missing something obvious.

my problem ? how to generate json with some values being keys to others. here an example

drop table if exists my_table;
create table  my_table(id int, sale_year int, sale_qty int);

insert into my_table values (10, 2007, 2);
insert into my_table values (10, 2008, 1);
insert into my_table values (10, 2009, 0);
insert into my_table values (20, 2009, 2);
insert into my_table values (30, 2011, 1);
insert into my_table values (30, 2012, 3);

The following statement

SELECT id, json_agg(to_json(my_table)) FROM public.my_table group by id;

gives me a json per id (e.g. for id = 20)

 20, [{"id":20, "sale_year": 2009, "sale_qty": 2}]

my question is:

is it possible to return a json with the following structure ?

  {"2009": 2}

user330315 · Accepted Answer · 2016-05-11 06:35:22Z

1

I think you want something like this:

select id, json_agg(json_build_object(sale_year, sale_qty))
from my_table
group by id
order by id;

This returns:

id | json_agg                                  
---+-------------------------------------------
10 | [{"2007" : 2}, {"2008" : 1}, {"2009" : 0}]
20 | [{"2009" : 2}]                            
30 | [{"2011" : 1}, {"2012" : 3}]

answered May 11, 2016 at 6:35

user330315

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

2 Comments

user1043144 Over a year ago

fantastic. THANKS a lot.

user1043144 Over a year ago

will it be possible to have as result a jsonb with key values and not a list ? so {"2007" : 2, "2008" : 1, "2009" : 0} and not [{"2007" : 2}, {"2008" : 1}, {"2009" : 0}] ? should I ask this in another question ?

Community · Accepted Answer · 2017-05-23 12:00:29Z

I hope that this will help someone else

in some cases, one would want to get, not an array of jsonb data but a single jsonb element.

inspired from this post this post this is an example of how to do it

with tx1
as
(
 select 
 * 
 from 
 (values 
 (10, 2007, 2), 
 (10, 2008, 1),
 (10, 2009, 0),
 (20, 2009, 2),
 (30, 2011, 1),
 (30, 2012, 3))
 as t (id, sale_year, sale_qty)),
 tx2
 as
 (select id, 
  jsonb_agg(json_build_object(sale_year, sale_qty)) as x_data
  from tx1
  group by id
  order by id)
  SELECT  
  id, 
  x_data,
  jo.obj
  FROM    tx2
  CROSS JOIN
    LATERAL
    (
    SELECT  JSON_OBJECT_AGG(jt.key, jt.value) obj
    FROM    JSONB_ARRAY_ELEMENTS(x_data) je
    CROSS JOIN
            LATERAL JSONB_EACH(je.value) jt
    ) jo

This gives

 { "2007" : 2, "2008" : 1, "2009" : 0 }
 { "2011" : 1, "2012" : 3 }

Collectives™ on Stack Overflow

postgresql json: keys as values

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related