Search code examples
sqlpostgresqlplpgsqljsonb

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

How to convert integer[] to jsonb?

declare ids int[];
declare jsonids jsonb;

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

  • 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:

    To convert back: