How to convert integer[] to jsonb in a PL/pgSQL code block

Question

How to convert integer[] to jsonb?

declare ids int[];
declare jsonids jsonb;

jsonids := array(select id from student); -- what should I do here?

Answer 1

Use to_jsonb():

jsonids := to_jsonb(ARRAY(SELECT id FROM student));

Or:

SELECT INTO jsonids to_jsonb(ARRAY(SELECT id FROM student));

array_to_json() is only still useful to get line feeds in json (not jsonb!). The manual:

Converts an SQL array to a JSON array. The behavior is the same as to_json except that line feeds will be added between top-level array elements if the optional boolean parameter is true.

See:

• Store query result in a variable using in PL/pgSQL

To convert back:

• How to turn JSON array into Postgres array?

Answer 2

You can try this

select array_to_json(array_agg(id)) into jsonids from student