CODEWITHSUNDEEP
jsonpostgresqlpostgresql-9.5
Dev Raj Gautam
Dev Raj Gautam

Reputation: 123

Combine multiple JSON rows into one JSON object in PostgreSQL

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

Answers (1)

user330315
user330315

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)

Online example

Upvotes: 19

Related Questions