Re: [HACKERS] [PATCH] Generic type subscripting - Mailing list pgsql-hackers

On 12/17/20 15:50, Tom Lane wrote:
> Chapman Flack <chap@anastigmatix.net> writes:
>> On 12/17/20 14:28, Tom Lane wrote:
>>> If you're imagining that js['n'] and js['v'] would emit different
>>> datatypes, forget it.  That would require knowing at parse time
>>> what the structure of the json object will be at run time.
> 
>> Would it be feasible to analyze that as something like an implicit
>> 'treat as' with the type of the assignment target?
> 
> TBH, I think that expending any great amount of effort in that direction
> would be a big waste of effort.  We already have strongly-typed
> composite types.  The use-case for json is where you *don't* have
> ironclad guarantees about what the structure of the data is.
> 
> As for doing it implicitly, that is still going to fall foul of the
> fundamental problem, which is that we don't have the info at parse
> time.  Examples with constant values for the json input are not what
> to look at, because they'll just mislead you as to what's possible.

Respectfully, I think that fundamental problem is exactly what led to
XQuery having the 'treat as' construct [1]. XML is in the same boat as JSON
as far as not having ironclad guarantees about what the structure will be.
But there are situations where the programmer knows full well that the
only inputs of interest will have js['n'] an integer and js['v'] a string,
and any input not conforming to that expectation will be erroneous and
should produce an error at runtime.

That's likely to be what a programmer intends when writing
(variable explicitly typed integer) := js['n'] and
(variable explicitly types varchar) := js['v']

so it might be nice to be able to write it without a lot of extra
ceremony. What I had in mind was not to try too hard to analyze the
JSON subscript expression, but only to know that its result can only
ever be: more JSON, a string, a number, a boolean, or an array of one
of those, and if the assignment target has one of those types, assume
that a 'treat as' is intended.

Naturally there's a trade-off, and that provides economy of expression
at the cost of not giving an immediate parse-time error if the
programmer really made a thinko rather than intending a 'treat as'.

I haven't closely followed what's proposed as the subscript in
js[...] - can it be any arbitrary jsonpath? And does jsonpath have
an operator analogous to XQuery's 'treat as'?

If so, something like (but with jsonpath rather than XQuery spelling)
  n := js['n treat as number'];
  v := js['v treat as string'];

might be a happy medium: perhaps parsing the expression enough to see
that its outer node is a 'treat as' is not asking too much, and then the
programmer has to explicitly add that to avoid a parse-time error,
but it's a reasonably painless notation to add.

Regards,
-Chap

[1] https://www.w3.org/TR/2003/WD-xquery-20030822/#id-treat