Add support for standard JSON functions - continued

[lukaseder]

This is a continuation of #8944 from jOOQ 3.12

With JSON data type support having been added to jOOQ 3.12 (see #7242), it would be useful to also support standard SQL JSON functions, such as:

• <JSON value function>
• <JSON query> (more sophisticated than <JSON value>, but so far only really supported by Oracle. Will postpone to a later release through Add support for standard JSON functions - continued #9997)
• <JSON table>
• <JSON exists predicate>
• <JSON array constructor>
  • <JSON array constructor by enumeration> (emulation in SQL Server, see: https://stackoverflow.com/q/51492569/521799)
    • <JSON constructor null clause>
  • <JSON array constructor by query> (more research needed. And not really required, as JSON_ARRAYAGG can work, too. Will postpone to a later release through Add support for standard JSON functions - continued #9997)
• <JSON object constructor> improvements
  • <JSON constructor null clause> (see Add support for JSON_OBJECT <JSON constructor null clause> #9928)
• <JSON object aggregate constructor> (see Add support for JSON_OBJECTAGG #9936)
  • <JSON constructor null clause>
• <JSON array aggregate constructor> (see Add support for JSON_ARRAYAGG #9927)
  • <JSON array aggregate order by clause>
  • <JSON constructor null clause>

There are also some interesting vendor specific functions, such as:

• MySQL functions: https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html
• H2 functions: http://h2database.com/html/functions.html?highlight=JSON_ARRAY&search=json_#json_object
• Oracle functions: https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246
• PostgreSQL functions: https://www.postgresql.org/docs/current/functions-json.html
• SQL Server functions: https://docs.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql?view=sql-server-2017

Most of this will be out of scope for jOOQ 3.12 and moved to 3.14

[lukaseder]

Almost finished. Only documentation is still missing...

[lukaseder]

The PostgreSQL JSON_TABLE .. FOR ORDINALITY clause is currently being emulated using ROW_NUMBER() OVER (), but that risks not being 100% reliable as there is no guarantee that the jsonb_path_query() produces a table whose ordering is maintained until ROW_NUMBER() OVER () is calculated.

Luckily, PostgreSQL supports WITH ORDINALITY on all table valued functions, so we can write this:

select *
from jsonb_path_query('["a","b","c"]', '$[*]') with ordinality;

Producing:

jsonb_path_query|ordinality|
----------------|----------|
"a"             |         1|
"b"             |         2|
"c"             |         3|

[mkurz]

Hey @lukaseder,

reading the docs about JSON_TABLE:

First:
In the jOOQ example on the right side the double quotes are missing:

JSON.valueOf("[{\"a\":5,\"b\":{\"x\":10}},
// should be:
JSON.valueOf("[{\"a\":5,\"b\":{\"x\":10}},"

Second:
When executing this example with Postgres 13 I get following error (taken right from the logs with config log_statement = 'all'):

2020-11-22 15:45:52.029 UTC [12377] ERROR:  subquery in FROM must have an alias at character 33
2020-11-22 15:45:52.029 UTC [12377] HINT:  For example, FROM (SELECT ...) [AS] foo.
2020-11-22 15:45:52.029 UTC [12377] STATEMENT:  select "id", "a", "x", "y" from (select o as "id", (jsonb_path_query_first(j, '$.a'::jsonpath)->>0)::int as "a", (jsonb_path_query_first(j, $1::jsonpath)->>0)::int as "x", (jsonb_path_query_first(j, $2::jsonpath)->>0)::int as "y" from jsonb_path_query(cast($3 as jsonb), $4::jsonpath) with ordinality as t(j, o))

BTW: I see you are emulating the missing JSON_TABLE function by using jsonb_path_query/_first. I guess you are following
https://commitfest.postgresql.org/28/1473/ already? 😉

[lukaseder]

In the jOOQ example on the right side the double quotes are missing:

Thanks! Fixed.

Second:

Can you please create a new issue with steps to reproduce this?

[mkurz]

Can you please create a new issue with steps to reproduce this?

#11014