How to prepend an element to a JSON array?

Question

I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty).

How can I prepend the value of _x to the array _y?

I was trying to do it like this:

SELECT jsonb_build_array(_x, VARIADIC array_agg(value))
FROM jsonb_array_elements_text(_y)
INTO _y;

which is wrong. I was trying to use info about VARIADIC from this answer.

Answer 1

Burns down to a simple solution:

_y := to_jsonb(ARRAY[_x]) || _y;

This is concatenating two jsonb arrays with the || operator.. You need to wrap the simple text value _x into a jsonb array for this. There are various way, to_jsonb(ARRAY[_x]) is one.

Or even just:

_y := to_jsonb(_x) || _y;

The manual:

The || operator concatenates the elements at the top level of each of its operands. [...]

So this happens to work for element-to-array concatenation as well.

---

You could unnest the array and reconstruct it (like you were trying), but that's more expensive. And might be tricky since a JSON array can hold elements of various types, unlike a Postgres array. (Which would not be a problem for your JSONB array of text.)

Your attempt failed because (as commented at the related answer), jsonb_build_array() expects a list of values, which can be replaced with a single array following the keyword VARIADIC - but not with a single element, followed by VARIADIC array.

• user9645: How dare they make it that easy!
• Erwin Brandstetter: Outrageous indeed!