I started to look through this again, and really found myself wondering
why we're going to all this work to invent what are fundamentally pretty
bogus "features". The thing that particularly sticks in my craw is the
0005 patch, which tries to interpret a subscript of a JSON value as either
integer or text depending on, seemingly, the phase of the moon. I don't
think that will work. For example, with existing arrays you can do
something like arraycol['42'] and the unknown-type literal is properly
cast to an integer. The corresponding situation with a JSON subscript
would have no principled resolution.
It doesn't help any that both coercion alternatives are attempted at
COERCION_ASSIGNMENT level, which makes it noticeably more likely that
they'll both succeed. But using ASSIGNMENT level is quite inappropriate
in any context where it's not 100% certain what the intended type is.
The proposed commit message for 0005 claims that this is somehow improving
our standards compliance, but I see nothing in the SQL spec suggesting
that you can subscript a JSON value at all within the SQL language, so
I think that claim is just false.
Maybe this could be salvaged by flushing 0005 in its current form and
having the jsonb subscript executor do something like "if the current
value-to-be-subscripted is a JSON array, then try to convert the textual
subscript value to an integer". Not sure about what the error handling
rules ought to be like, though.
regards, tom lane