Re: SQL/JSON: JSON_TABLE

On Tue, Mar 22, 2022 at 12:53 PM Matthias Kurz <m(dot)kurz(at)irregular(dot)at> wrote:

> Hi everyone!
>
> 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
https://github.com/json-schema-org/vocab-database/blob/main/database.md,
which opens a lot of useful features and optimizations like json
dictionary compression.

>
> Thanks everyone for your fantastic work!
> Matthias
>
>
> On Sun, 13 Mar 2022 at 22:22, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>>
>> On 2/9/22 08:22, Himanshu Upadhyaya wrote:
>> > On Wed, Feb 2, 2022 at 12:44 AM Andrew Dunstan <andrew(at)dunslane(dot)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.
>>
>>
>> This whole area needs more documentation.
>>
>>
>> cheers
>>
>>
>> andrew
>>
>> --
>> Andrew Dunstan
>> EDB: https://www.enterprisedb.com
>>
>>
>>
>>

--
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company