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

On Tue, May 31, 2016 at 06:20:26PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > On Tue, May 31, 2016 at 05:06:00PM -0400, David G. Johnston wrote:
> >> While likely not that common the introduction of an ambiguity makes
> >> raises the bar considerably.
>
> > What ambiguity?
>
> My first thought about it was that
>
> select unnest('{1,2,3}');
>
> would start failing. But it turns out it already does fail:
>
> ERROR: function unnest(unknown) is not unique
>
> You get that as a result of the recent introduction of unnest(tsvector),
> which we debated a few weeks ago and seem to have decided to leave as-is.
> But it failed before 9.6 too, with
>
> ERROR: could not determine polymorphic type because input has type "unknown"
>
> So at least in this particular case, adding unnest(jsonb) wouldn't be a
> problem from the standpoint of not being able to resolve calls that we
> could resolve before.
>
> Nonetheless, there *is* an ambiguity here, which is specific to json(b):
> what type of array are you expecting to get? The reason we have both
> json[b]_array_elements() and json[b]_array_elements_text() is that there
> are plausible use-cases for returning either json or plain text. It's not
> hard to imagine that somebody will want json[b]_array_elements_numeric()
> before long, too. If you want to have an unnest(jsonb) then you will need
> to make an arbitrary decision about which type it will return, and that
> doesn't seem like an especially great idea to me.

How about making casts work? UNNEST(jsonb)::NUMERIC or similar,
whatever won't make the parser barf.

> > UNNEST, and ROWS FROM have more capabilities including WITH ORDINALITY
> > than the json_array_elements-like functions do.
>
> AFAICT, this is nonsense. We did not tie WITH ORDINALITY to UNNEST;
> it works for any set-returning function.

Oops. My mistake. Sorry about the noise.

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