CODEWITHSUNDEEP
sqlpostgresql
Shanmugapriya D
Shanmugapriya D

Reputation: 306

group by in sql value with json format in postgresql

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

Upvotes: 0

Views: 69

Answers (1)

Emilio Platzer
Emilio Platzer

Reputation: 2469

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;

Upvotes: 2

Related Questions