I am watching this thread since quite a while and I am waiting eagerly a long time already that this feature finally lands in PostgreSQL.
Given that in around 2 weeks PostgreSQL 15 will go into feature freeze (in the last years that usually happened around the 8th of April AFAIK), is there any chance this will be committed? As far as I understand the patches are almost ready.
We are waiting too :)
Sorry for the noise, I just wanted to draw attention that there are people out there looking forward to JSON_TABLE ;)
IS JSON is also cool in light of the work on JSON Schema
On 2/9/22 08:22, Himanshu Upadhyaya wrote: > On Wed, Feb 2, 2022 at 12:44 AM Andrew Dunstan <andrew@dunslane.net> wrote: >> >> rebased with some review comments attended to. > I am in process of reviewing these patches, initially, have started > with 0002-JSON_TABLE-v55.patch. > Tested many different scenarios with various JSON messages and these > all are working as expected. Just one question on the below output. > > ‘postgres[1406146]=#’SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS > (a int PATH '$.a' ERROR ON EMPTY)) jt; > a > --- > > (1 row) > > ‘postgres[1406146]=#’SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS > (a int PATH '$.a' ERROR ON ERROR)) jt; > a > --- > > (1 row) > > is not "ERROR ON ERROR" is expected to give error?
I think I understand what's going on here. In the first example 'ERROR ON EMPTY' causes an error condition, but as the default action for an error condition is to return null that's what happens. To get an error raised you would need to say 'ERROR ON EMPTY ERROR ON ERROR'. I don't know if that's according to spec. It seems kinda screwy, arguably a POLA violation, although that would hardly be a first for the SQL Standards body. But I'm speculating here, I'm not a standards lawyer.
In the second case it looks like there isn't really an error. There would be if you used 'strict' in the path expression.
