Hi
út 12. 11. 2019 v 22:51 odesílatel Nikita Glukhov <[email protected]>
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
>
