Re: JSON[B] arrays are second-class citizens

On Wed, Jun 1, 2016 at 11:55 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:

> On 5/31/16 7:04 PM, Peter van Hardenberg wrote:
>
>> The idea of converting a JSONB array to a PG array is appealing and
>> would potentially be more general-purpose than adding a new unnest. I'm
>> not sure how feasible either suggestion is.
>>
>
> The one part I think is missing right now is unnest allows you to 'stitch'
> or 'zip' multiple arrays together into a single recordset via
> unnest(array1, array2, ...). Presumably that could be added to the json
> equivalents.

You can just use "ROWS FROM" to get the same result.

https://www.postgresql.org/docs/9.6/static/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS
"""
The special table function UNNEST may be called with any number of array
parameters, and it returns a corresponding number of columns, as if UNNEST
(Section 9.18) had been called on each parameter separately and combined
using the ROWS FROM construct.
​"""

​Yes, the unnest form can be used within the target-list but that argument
is not going to get you very far as that use of SRF is greatly frowned upon
now that we have LATERAL.

David J.