Combine multiple JSON rows into one JSON object in PostgreSQL

Question

I want to combine the following JSON from multiple rows into one single JSON object as a row.

{"Salary": ""}
{"what is your name?": ""}
{"what is your lastname": ""}

Expected output

{
  "Salary": "",
  "what is your name?": "",
  "what is your lastname": ""
}

Please do not post sample data as images. You will get more help if you provide sample data as formatted text as shown here or even better as a little SQL script with create table and the insert into statements. — user330315
– user330315, Commented Jul 29, 2019 at 8:25

user330315 · Accepted Answer · 2019-07-29 08:39:07Z

19

With only built-in functions, you need to expand the rows into key/value pairs and aggregate that back into a single JSON value:

select jsonb_object_agg(t.k, t.v)
from the_table, jsonb_each(ob) as t(k,v);

If your column is of type json rather than jsonb you need to cast it:

select jsonb_object_agg(t.k, t.v)
from the_table, jsonb_each(ob::jsonb) as t(k,v);

A slightly more elegant solution is to define a new aggregate that does that:

CREATE AGGREGATE jsonb_combine(jsonb) 
(
    SFUNC = jsonb_concat(jsonb, jsonb),
    STYPE = jsonb
);

Then you can aggregate the values directly:

select jsonb_combine(ob)
from the_table;

(Again you need to cast your column if it's json rather than jsonb)

Online example

answered Jul 29, 2019 at 8:39

user330315

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

1 Comment

Dmytro Chaban Over a year ago

This should be first in a google search, I had to look for 10 other links before I found this simple solution. Thank you very much, you probably saved me 3-4 hours of refactoring :)

Collectives™ on Stack Overflow

Combine multiple JSON rows into one JSON object in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related