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
