CODEWITHSUNDEEP
jsonpostgresql
Johnson Anthony
Johnson Anthony

Reputation: 235

Combine row of jsons into one single jsons in postgresql

I have a JSON Column in each row like below

"{"car":12}"

"{"bike":9}"

How to make it into one row like this

{"car":12, "bike":9}

Upvotes: 0

Views: 587

Answers (2)

Mike Organek
Mike Organek

Reputation: 12494

Sorry about my earlier mistake.

You can do this like so:

with data (jcol) as (
  values ('{"car": 12}'::jsonb), ('{"bike":9}')
)
select jsonb_object_agg(k, v) 
  from data 
 cross join lateral jsonb_each(jcol) as j(k,v);

    jsonb_object_agg    
------------------------
 {"car": 12, "bike": 9}
(1 row)

Upvotes: 0

user330315
user330315

Reputation:

If you don't want an array as the result, you will need to create your own aggregate function:

create aggregate jsonb_merge_agg(jsonb) 
(
  sfunc = jsonb_concat(jsonb, jsonb),
  stype = jsonb
);

Then you can use it like this:

select jsonb_merge_agg(the_column)
from the_table;

If you have multiple values with the same key, the "last one" will overwrite all previous ones.

Online example

Upvotes: 2

Related Questions