Re: JSON to INT[] or other custom type

On Sun, Jun 11, 2017 at 2:35 PM, Rory Campbell-Lange <
rory(at)campbell-lange(dot)net> wrote:

> I'm hoping, in the plpgsql function, to unfurl the supplied json into a
> custom type or at least an array of ints, and I can't work out how to do
> that.

​You will be unable to cast the array itself. You must use json functions
to unfurl the json into a result set with each row containing a single
value. You can then explicitly cast that value to integer. If the casting
doesn't fail you now have column of integers that can be
"array_agg(value_as_integer)​" to construct an SQL array of type integer[]
(or whatever you decide to cast it to).

You can write you own pl/pgsql function to do these operations.

As your example includes a two-dimensional array you probably will need
to json_array_elements_text twice, then array_agg twice, to get a similar
structure in the eventual SQL array.

Hopefully that moves you in a useful direction.

David J.