Support concatenate (||) for jsonb arrays

[ly9chee]

Is your feature request related to a problem? Please describe.

select '["1","2","3"]'::jsonb::varchar[];
this sql will cause a bind error

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[xxchan]

You may try

select array_agg(x) from jsonb_array_elements_text( '["1","2","3"]'::jsonb) as t(x);

[ly9chee]

@xxchan Hi, thanks for your reply! My actual needs is to concatenate two json array into a single array, just like bellow:

SELECT x || y as concated
FROM (
    VALUES ('["1","2","3"]'::jsonb, '["4","5","6"]'::jsonb)
) AS t (x, y);

the expected result is:
["1","2","3","4","5","6"]
but I found currently only normal array can do the concatenation (through || operator) so I'm wondering if there is a more convenient approach to doing this.

according to the example you provided above, the sql should change to:

select array_agg(u.a) || array_agg(u.b) as concated from (
  select jsonb_array_elements_text(x) as a, jsonb_array_elements_text(y) as b
    from (
        values ('["1","2","3"]'::jsonb, '["4","5","6"]'::jsonb)
    ) as t (x, y)
) as u;

it looks like more verbose(and maybe it is not performant? I think).
it is very common to do a concat with two json array in business logic, so is there a better way to accomplish this?

[xxchan]

We can support the || operator (#12180), which concatenates 2 jsonb values to a new jsonb.

-- in Postgres
xxchan@/tmp:postgres> select  '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;

+----------------------+
| ?column?             |
|----------------------|
| ["a", "b", "c", "d"] |
+----------------------+

Does it fit your needs?

[ly9chee]

exactly! I would be appreciate if this || operator could be supported

[xiangjinwu]

will be tracked by #12180