1

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.