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

On Tue, May 31, 2016 at 4:34 PM, David Fetter <david(at)fetter(dot)org> wrote:

> Folks,
>
> While querying some JSONB blobs at work in preparation for a massive
> rework of the data infrastructure, I ran into things that really
> puzzled me, to wit:
>
> SELECT * FROM unnest('["a","b","c"]'::jsonb);
> ERROR: function unnest(jsonb) does not exist
>
> SELECT * FROM jsonb_array_elements('["a","b","c"]'::jsonb);
> value
> ───────
> "a"
> "b"
> "c"
> (3 rows)
>
>
​I'd be inclined to -1 such a proposal. TIMTOWTDI is not a principle that
we endeavor to emulate.

Having an overloaded form: <unnest(jsonb) : setof jsonb> is unappealing.
While likely not that common the introduction of an ambiguity makes raises
the bar considerably.

That said we do seem to be lacking any easy way to take a json array and
attempt to convert it directly into a PostgreSQL array. Just a conversion
is not always going to succeed though the capability seems worthwhile if as
yet unasked for. The each->convert->array_agg pattern works but is likely
inefficient for homogeneous json array cases.

David J.