Add support for standard JSON_TABLE's FORMAT JSON clause or SQL Server's OPENJSON's AS JSON clause

AnghelLeonard commented 3 years ago

Use case:

Let's consider a JSON snippet in a table named MANAGER (column, MANAGER_DETAIL):

{
  "firstName": "Joana",
  "lastName": "Nimar",   
  "phoneNumber": [
    {
      "type": "home",
      "number": [
        "212 555-1234",
        "212 543-0933"
      ]
    }
  ]
}

And, the following jOOQ code:

Result<Record> result = ctx.select(table("t").asterisk())
                .from(MANAGER).crossApply(
                        jsonTable(MANAGER.MANAGER_DETAIL.coerce(JSON.class), val("$"))
                        .column("id").forOrdinality()
                        .column("firstName", VARCHAR(10)) 
                        .column("lastName", VARCHAR(10))
                        .column("phoneNumber", NVARCHAR) // null 
                        .as("t"))
                .fetch();

The generated SQL produced NULL for phoneNumber.

Possible solution you'd like to see:

In place of NULL, an output as: [{"type":"home","number":["212 555-1234","212 543-0933"]}].

Please describe a possible solution you'd like to see in jOOQ, ideally with example code.

The generated SQL:

...
with(
        [firstName] varchar(10), 
        [lastName] varchar(10),                 
        [phoneNumber] nvarchar(max)
      ) [t]
...

to become:

with(
        [firstName] varchar(10), 
        [lastName] varchar(10),                 
        [phoneNumber] nvarchar(max)  as json
      ) [t]

So, in jOOQ, probably, something as:

.column("phoneNumber", NVARCHAR).asJson()
// or, 
.column("phoneNumber", NVARCHAR).path("...").asJson()

Versions:

jOOQ: 3.14.4
Java: 14
Database (include vendor): SQLServer 2017
OS: Windows 10
JDBC Driver (include name if inofficial driver): mssql-jdbc-7.4.1

lukaseder commented 3 years ago

Thanks a lot for your suggestion. I think that's what Oracle and the standard (which was mostly written by Oracle) refer to as the FORMAT JSON syntax, which is occasionally generated by jOOQ internally when needed.

See their example from the docs: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/JSON_TABLE.html#GUID-3C8E63B5-0B94-4E86-A2D3-3D4831B67C62

SELECT *
FROM JSON_TABLE('[1,2,["a","b"]]', '$'
COLUMNS (outer_value_0 NUMBER PATH '$[0]',
         outer_value_1 NUMBER PATH '$[1]', 
         outer_value_2 VARCHAR2(20) FORMAT JSON PATH '$[2]'));

Result:

OUTER_VALUE_0 OUTER_VALUE_1 OUTER_VALUE_2
------------- ------------- --------------------
            1             2 ["a","b"]

Can you confirm this is the same idea as what you had in mind?

AnghelLeonard commented 3 years ago

Yes, that's the idea.

lukaseder commented 3 years ago

Thanks for the feedback. Sure, that would be a very valuable addition. Should be doable for jOOQ 3.15

lukaseder commented 3 years ago

jOOQ / jOOQ