Reputation: 123
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": ""
}
Upvotes: 3
Views: 5792
Reputation:
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
)
Upvotes: 19