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

On Tue, May 31, 2016 at 06:15:32PM -0400, David G. Johnston wrote:
> I stand corrected. I was thinking you could somehow craft unnest('<literal
> value here>') but there is no way to auto-convert to "anyarray"...
>
> > The json_array_elements family manages to do the right thing. Why
> > would it be harder to make sure UNNEST and ROWS FROM() do so?
> >
>
> I have apparently failed to understand your point. All I saw was that you
> wanted "unnest(jsonb)" to work in an identical fashion to
> "​jsonb_array_elements(jsonb)". If there is some aspect beyond this being
> an aliasing situation then you have failed to communicate it such that I
> comprehended that fact.
>

Upon further investigation, I think UNNEST should Just Work™ which is
to say that it should unnest arrays into their top-level constituent
elements if the standard doesn't specify some other behavior.

Separately, I suppose, I think there needs to be an easy way to cast
the output of UNNEST. Lacking knowledge of the intricacies of
parsing, etc., I'd propose CAST(UNNEST(...) AS ...), or better yet,
UNNEST(...):: at least in the case without WITH ORDINALITY.

Further out in the future, at least so it seems to me, it would be
nice to have a feature where one could cast a column to an expanded
row type, e.g.:

SELECT my_jsonb::(i INT, t TEXT, p POINT), foo, bar
FROM ...

and get a result set with 5 columns in it.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate