CODEWITHSUNDEEP
postgresqlpostgresql-10
MAK
MAK

Reputation: 7270

Concatenate JSON rows

I have the following table with sample records:

create table jtest
(
    id int,
    jcol json
);

insert into jtest values(1,'{"name":"Jack","address1":"HNO 123"}');
insert into jtest values(1,'{"address2":"STREET1"}');
insert into jtest values(1,'{"address3":"UK"}');

select * from jtest;

id      jcol
-------------------------------------------
1       {"name":"Jack","address":"HNO 123 UK"}
1       {"address2":"STREET1"}
1       {"address3":"UK"}

Expected result:

id      jcol
--------------------------------------------------------------------------------------------
1       {"name":"Jack","address":"HNO 123 UK", "address2":"STREET1", "address3":"UK"}

Tried the following query:

select id,json_agg(jcol) as jcol
from jtest
group by id;

But getting result is unexpected:

id      jcol
--------------------------------------------------------------------------------------------
1       [{"name":"Jack","address":"HNO 123 UK"}, {"address2":"STREET1"}, {"address3":"UK"}]

Upvotes: 1

Views: 107

Answers (1)

S-Man
S-Man

Reputation: 23766

demo:db<>fiddle

SELECT
    id,
    json_object_agg(key, value)      -- 2
FROM
    t,
    json_each(jcol)                  -- 1
GROUP BY id
  1. First you have to extract all elements into one row
  2. Afterwards you can reaggregate all of them

Upvotes: 2

Related Questions