Concatenation of jsonb elements in postgreSQL with comma separation

Question

I would like to design a query where I can combine two jsonb with an unknown number/set of elements in postgreSQL in a controlled manner. The jsonb operator || almost exactly fits my purpose, but for one of the jsonb elements I would like to concatenate and separate by comma the two values rather than having the second jsonb's value override the first's value. For example:

'{"a":"foo", "b":"one", "special":"comma"}'::jsonb || '{"a":"bar", "special":"separated"}'::jsonb → '{"a":"bar", "b":"one", "special":"comma,separated"}'

My current query is the following:

INSERT INTO table AS t (col1, col2, col3_jsonb)
VALUES ("first", "second", '["a":"bar", "special":"separated"]'::jsonb))
ON CONFLICT ON CONSTRAINT unique_entries DO UPDATE
SET col3_jsonb = excluded.col3_jsonb || t.col3_jsonb
RETURNING id;

which results in a jsonb element for col3_jsonb that has the value special set to separated rather than the desired comma,separated. I understand that this is the concatenation operator working as documented, but I am not sure how to approach treating one element of the jsonb differently other than perhaps trying to pull out special values with WITH.. clauses elsewhere in the query. Any insights or tips would be hugely appreciated!

score 1 · Answer 1 · answered Jun 09 '21 at 21:47

You can use jsonb_each on the two values followed by jsonb_object_agg to put them back into an object:

…
SET col3_jsonb = (
 SELECT jsonb_object_agg(
    key,
    COALESCE(to_jsonb((old.value->>0) || ',' || (new.value->>0)), new.value, old.value)
  )
  FROM jsonb_each(example.old_obj) old
  FULL OUTER JOIN jsonb_each(example.new_obj) new USING (key)
)

^{(online demo)}

The casting of the arbitrary JSON value to the concatenable string required a trick. If you know that all your object properties have string values, you can simplify by using jsonb_each_text instead:

  SELECT jsonb_object_agg(
    key,
    COALESCE(old.value || ',' || new.value, new.value, old.value)
  )
  FROM jsonb_each_text(example.old_obj) old
  FULL OUTER JOIN jsonb_each_text(example.new_obj) new USING (key)

Thanks for the reply! This is an elegant approach, but I was hoping to only concatenate the "special" field and not all of the fields. — user11058068, Jun 14 '21 at 16:32

score 1 · Accepted Answer · answered Jun 09 '21 at 22:50

with t(a,b) as (values(
    '{"a":"foo", "b":"one", "special":"comma"}'::jsonb,
    '{"a":"bar", "special":"separated"}'::jsonb))
select
    a || jsonb_set(b, '{special}',
        to_jsonb(concat_ws(',', nullif(a->>'special', ''), nullif(b->>'special', ''))))
from t;
┌────────────────────────────────────────────────────────┐
│                        ?column?                        │
├────────────────────────────────────────────────────────┤
│ {"a": "bar", "b": "one", "special": "comma,separated"} │
└────────────────────────────────────────────────────────┘

nullif() and concat_ws() functions needed for cases where one or both "special" values is missing/null/empty

Thanks, this worked well I didn't put this part in my question, but I updated my query to remove duplicates in the 'special' array element like so: ```WITH example (a, b) AS (VALUES ('{"a":"foo", "b":"one", "special":"comma"}'::jsonb, '{"a":"bar", "special":"separated"}'::jsonb), ('{"a":"foo", "b":"one", "special":"comma"}'::jsonb, '{"a":"bar", "special":"comma"}'::jsonb) ) SELECT e.a || jsonb_set(e.b, '{special}', to_jsonb(array_to_string(array(select distinct unnest(string_to_array(concat_ws(',', e.a->>'special', e.b->>'special'), ','))), ',')) ) FROM example e;``` — user11058068, Jun 15 '21 at 16:43

Concatenation of jsonb elements in postgreSQL with comma separation

2 Answers2