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

On Tue, May 31, 2016 at 4:34 PM, David Fetter <david@fetter.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.