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

I would try simple way too - SELECT ('['||string_agg(json_data::text,',')||']')::jsonb FROM temp."testConcat" GROUP BY "groupID". This gives you jsonb in format you want. Function jsonb_agg seems to convert original jsonb parts into texts... — JosMac
– JosMac, Commented Jun 21, 2017 at 14:46
with t(x) as (values('{"name": "JSON_name1"}'::jsonb),('{"name": "JSON_name2"}')) select jsonb_agg(x) from t; — Abelisto
– Abelisto, Commented Jun 21, 2017 at 16:52

user330315 · Accepted Answer · 2017-06-21 14:46:40Z

7

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.

answered Jun 21, 2017 at 14:46

user330315

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

4 Comments

Vicking Over a year ago

Thanks for your suggestion. I try it, but it seems that the result is not corresponding to this that I am searching for. When I have 2 lines with the same keys, but differents values I would like to preserve all this differences.

user330315 Over a year ago

@Vicking: can you extend your sample data in the question to also show that?

Vicking Over a year ago

I change the question's description. I hope it is more clear now.

user330315 Over a year ago

The expected result [{"name": "JSON_name1", "value": "Toto"}, {"name": "JSON_name2"}] is a simple select jsonb_agg(json_data) from testconcat

Vicking · Accepted Answer · 2017-06-21 14:41:29Z

3

Finally I just find a solution, even if it is not the best, it seems to works.

I create the agreate function, as previously described with a small modification :

DROP AGGREGATE IF EXISTS jsonb_merge(jsonb);

CREATE AGGREGATE jsonb_merge(jsonb) (
   SFUNC = jsonb_concat(jsonb, jsonb),
   STYPE = jsonb,
   INITCOND = '[]'
);

I just replace :

INITCOND = '{}'

with

INITCOND = '[]'

And after used it as previously :

SELECT jsonb_merge(json_data)
FROM testConcat
GROUP BY groupID

answered Jun 21, 2017 at 14:41

Vicking

5932 gold badges8 silver badges16 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - GROUP_CONCAT for JSONB column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related