Re: SQL/JSON: JSON_TABLE

Hi

út 12. 11. 2019 v 22:51 odesílatel Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
napsal:

> On 12.11.2019 20:54, Pavel Stehule wrote:
>
> > Hi
> >
> > please, can you rebase 0001-SQL-JSON-functions-v40.patch. I have a
> > problem with patching
> >
> > Pavel
>
> Attached 41th version of the patches rebased onto current master.
>

I testing functionality - randomly testing some examples that I found on
internet.

I found:

a) Oracle & MySQL (Oracle) supports EXISTS clause, this implementation not.
I think should be useful support this clause too.

SELECT * FROM JSON_TABLE('...', '...' COLUMNS x INT EXISTS PATH ...

There is a question how to map boolean result to other data types.

b) When searched value is not scalar, then it returns null. This behave can
be suppressed by clause FORMAT Json. I found a different behave, and maybe
I found a bug. On MySQL this clause is by default for JSON values (what has
sense).

SELECT *
FROM
JSON_TABLE(
'[{"a":[1,2]}]',
'$[*]'
COLUMNS(
aj JSON PATH '$.a' DEFAULT '{"x": 333}' ON EMPTY
)
) AS tt;

It returns null, although it should to return [1,2].

There is another bug maybe. Although there is DEFAULT clause. It returns
NULL.

I got correct result when I used FORMAT JSON clause. I think it should be
default behave for json and jsonb columns.

Another question - when I used FORMAT JSON clause, then I got syntax error
on DEFAULT keyword .. . Is it correct? Why I cannot to use together FORMAT
JSON and DEFAULT clauses?

Note - this behave is not described in documentation.

Regards

Pavel

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