group by in sql value with json format in postgresql

Question

I have data like below in my table

key         value
department  maths
department  science
class       one
class       two
book        science
book        maths
department  Tamil
book        SS
class       ten

in this table i want to get like below

"department":{
department : maths,
department :scicence
},
"class":{
 class : one,
class :two
}

in sql it self

If this is for "postgressql", why are you tagging SQL Server and MySQL...? — Thom A
– Thom A ♦, Commented Aug 21, 2018 at 14:39
That sentence makes no sense I'm afraid. Postgresql, MySQL and SQL Server are all completely different RDBMS owned by completely different companies. — Thom A
– Thom A ♦, Commented Aug 21, 2018 at 14:52
As an aside, duplicate key names in the same object in your JSON are likely going to make life hard for you, at least from a development aspect. — Diado
– Diado, Commented Aug 21, 2018 at 15:13

Emilio Platzer · Accepted Answer · 2018-08-21 15:13:39Z

Strange but posible.

Warning the output is a strange pseudo JSON because it has repeated keys:

This does what you want:

create table data1 (
  key text,
  value text
);

insert into data1(key,value) values 
('department','maths'),
('department','science'),
('class','one'),
('class','two'),
('book','science'),
('book','maths'),
('department','Tamil'),
('book','SS'),
('class','ten');

select json_object_agg(key, joined_values)::text
  from (
    select key, json_object_agg(key, value) joined_values
      from data1
      group by key
  ) data_joined;

If you do not want repeated keys in the object you can use an array inside

select json_object_agg(key, joined_values) 
  from (
    select key, json_agg(value) joined_values
      from data1
      group by key
  ) data_joined;

Collectives™ on Stack Overflow

group by in sql value with json format in postgresql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related