PostgreSQL - GROUP_CONCAT for JSONB column

Question

I try to find a way to concat JSONB values using Postgres.

For example I have two lines :

INSERT INTO "testConcat" ("id", "json_data", "groupID") 
VALUES (1, {"name": "JSON_name1", "value" : "Toto"}, 5);

INSERT INTO "testConcat" ("id", "json_data", "groupID") 
VALUES (2, {"name": "JSON_name2"}, 5);

I would like to do something like :

SELECT GROUP_CONCAT(json_data)
FROM testConcat
GROUP BY groupID

AND as results to obtain something like :

[{"name": "JSON_name1", "value": "Toto"}, {"name": "JSON_name2"}]

I try the creation of aggregate function, but when there is the same key into the JSON, then they are merged and only the last values is preserved :

DROP AGGREGATE IF EXISTS jsonb_merge(jsonb);

CREATE AGGREGATE jsonb_merge(jsonb) (
    SFUNC = jsonb_concat(jsonb, jsonb),
    STYPE = jsonb,
    INITCOND = '{}'
 );

When I use this function as here :

SELECT jsonb_merge(json_data)
FROM testConcat
GROUP BY groupID

The result is :

{"name": "JSON_name2", "value": "Toto"}

And not as those that I want because the

{"name": "JSON_name1"}

is missing. The function preserve only the different keys and merge the other one with the last value.

Thanks for any help

Answer 1

If there is always only a single key/value pair in the JSON document, you can do this without a custom aggregate function:

SELECT groupid, jsonb_object_agg(k,v order by id)
FROM testconcat, jsonb_each(json_data) as x(k,v)
group by groupid;

The "last" value is defined by the ordering on the id column

The custom aggregate function might be faster though.