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

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

> On Tue, May 31, 2016 at 05:06:00PM -0400, David G. Johnston wrote:
> > 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.
>
> You cut out the part where I introduced the part that's not
> equivalent, so "there is more than one way to do it" isn't on point
> here. More on that specific issue below.
>
> UNNEST, and ROWS FROM have more capabilities including WITH ORDINALITY
> than the json_array_elements-like functions do.

​ Is it really more

> efficient to build and maintain those capabilities separately in the
> JSON[B] set-returning functions, or to force our end users to use
> atrocious hacks like putting
> generate_series(1,jsonb_array_length(foo)) in the target list than
> just to make UNNEST and ROWS FROM do the right thing?
>
>
​​Both of these work in 9.5...

​
​
SELECT * FROM ROWS FROM (jsonb_array_elements('["a","b","c"]'::jsonb)) WITH
ORDINALITY
​value | ordinality
----------------------
"a" | 1
"b" | 2
"c" | 3​​​

> > Having an overloaded form: <unnest(jsonb) : setof jsonb> is unappealing.
>
> On what grounds?
>

​Aesthetic. YMAV (your mileage apparently varies).

> > While likely not that common the introduction of an ambiguity makes
> > raises the bar considerably.
>
> What ambiguity?
>
> SELECT jsonb_array_elements('{"a":2,"b":1}'::jsonb);
> ERROR: cannot extract elements from an object
>
>
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.

> > 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.
>
> To your earlier point about "there is more than one way to do it," we
> have made no attempt to make some sort of language composed of
> orthonormal vectors, and the SQL standard actually requires that we
> not do so. For a trivial example, there's
>
> SELECT * FROM foo
> and
> TABLE foo
>
> which are equivalent and both spelled out in the standard.
>
>
​And would we have done so had we not been compelled to by the standard?

Maybe it should be enshrined somewhere more obvious but Alvaro, and
indirectly Tom, recently made the same claim[1] against TIMTOWTDI so I feel
justified making such a claim on behalf of the project - as well as my
general personal feeling. Adding user-friendly UI to the system, to
correct deficiencies, does have merit, but that is not what I personally
see here.

​David J.​

[1]
https://www.postgresql.org/message-id/20160405164951.GA286879@alvherre.pgsql