Question 210699 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to merge two (or multiple) rows of jsonb array into one in SQL

I am using postgres and I have multiple entries of jsonb inside an array in a single column called observation. They’re input as
'[{"a": 1}, {"b": 2}, {"c": 0.5}]'::jsonb.
There are multiple rows, with multiple json elements inside of each one of them. I would like to combine them into one big entry in one row, so that I will just have one observation of one column as a result.

I have tried the following

INSERT INTO data
SELECT jsonb_agg(observation) AS concatenated_json
FROM (
  SELECT observation
  FROM test
) AS subquery;

But the result I got was an array of arrays. I don’t want to get multiple arrays, just a single one.

Tags:

3

Answers


  1. 0

    You can use jsonb_object_agg:

    select jsonb_agg((select jsonb_object_agg(k1.key, k1.value) 
     from jsonb_array_elements(t.observation) k 
     cross join jsonb_each(k.value) k1)) 
from test t

    See fiddle

    This query will, for each row, merge all key-value pairs in the array elements of observations into a single object. Then, the merged objects are aggregated into a single array.

    Login or Signup to reply.
  2. 0 
    INSERT INTO data
SELECT jsonb_agg(s.elements) FROM (
    SELECT jsonb_array_elements(observation) FROM test
) AS s(elements);

    Use jsonb_array_elements() to expand a json array to a set of json values. For example:

    [{"a": 1}, {"b": 2}, {"c": 0.5}]
[{"d": 2.2}, {"e": 2.4}, {"f": 3.5}]
[{"g": 1.7}, {"h": 5.4}, {"i": 8.9}]

    (3 rows)

    will convert into 9 rows of json values:

    {"a": 1}
{"b": 2}
{"c": 0.5}
{"d": 2.2}
{"e": 2.4}
{"f": 3.5}
{"g": 1.7}
{"h": 5.4}
{"i": 8.9}

    then we aggregate the json values using jsonb_agg() into table data to output one row:

    [{"a": 1}, {"b": 2}, {"c": 0.5}, {"d": 2.2}, {"e": 2.4}, {"f": 3.5}, {"g": 1.7}, 
{"h": 5.4}, {"i": 8.9}]
    Login or Signup to reply.
  3. 0

    You can create a user-defined aggregate which does what you want pretty trivially:

    create aggregate jsonb_concat_agg(jsonb) (stype=jsonb, sfunc=jsonb_concat);

    Then use it with GROUP BY, or since you only want one row, use it without GROUP BY:

    with t as (select '[{"a":1},{"b":2},{"c":3}]'::jsonb p union select '[{"d":4},{"e":5},{"f":6}]'::jsonb) 
select jsonb_concat_agg(p) from t;
------------------------------------------------------------
[{"a": 1}, {"b": 2}, {"c": 3}, {"d": 4}, {"e": 5}, {"f": 6}]

    On the one hand it is nicer than having to unnest the objects and then reassemble them, on the other hand people reading your code might be confused by what looks like it might be a built-in function, but isn’t.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search