subquery json deserialization vs generated record types

fkowal commented 4 years ago

Use case:

I'll describe my use case with an example. I was inspired somewhat by https://blog.jooq.org/stop-mapping-stuff-in-your-middleware-use-sqls-xml-or-json-operators-instead/ Having learned that Postgres has such nice support for JSON.

CREATE TABLE order(
id UUID,
-- ...  some more fields here
);

CREATE TABLE order_items(
id UUID,
order_id UUID,
-- some more item column here
)

CREATE VIEW orders_view AS SELECT o.*, (select array_to_json(array_agg(row_to_json(f.*))) as items from ORDER_ITEMS as f where f.order_id = o.id) from ORDER as o;

I generate the tables/records like ./gradlew generateOrderJooqSchemaSource

import com.fasterxml.jackson.module.kotlin.readValue
...

dsl.selectFrom(ORDERS_VIEW)
.fetch { record ->
  val itemList = objectMapper.readValue<List<OrderItemsRecord>>(record.items.data())
}

com.fasterxml.jackson.databind.JsonMappingException: Conflicting setter definitions for property "returning": org.jooq.StoreQuery#setReturning(1 params) vs org.jooq.StoreQuery#setReturning(1 params)

I tried using the generated record type OrderItemsRecord to deserialize the JSON column

Possible solution you'd like to see:

Make records jackson friendly (@JsonCreator on constrcutor ?)
Hide internal/inherited properties of generated records (transient ? not sure if this will fix the jackson issue)
Use composition. Generated records could include a single object/POJO with getters/setters and delegate to it

Versions:

jOOQ: 3.12.3
Java: any
Database (include vendor): postgres 9.6.x
OS: any
JDBC Driver (include name if inofficial driver):

lukaseder commented 4 years ago

Thank you very much for your suggestions. I'll comment on them individually:

Make records jackson friendly (@JsonCreator on constrcutor ?)

Unfortunately, we cannot have such a third party dependency. See the rationale here: https://blog.jooq.org/2016/08/11/all-libraries-should-follow-a-zero-dependency-policy/

However, you might be able to write your own converter or data type binding, and attach that to generated code? Some information can be found here: https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/

Hide internal/inherited properties of generated records (transient ? not sure if this will fix the jackson issue)

I'm not sure what you mean by this, would you mind elaborating?

Use composition. Generated records could include a single object/POJO with getters/setters and delegate to it

This can be done with a converter / binding, but see, the nature of schemaless JSON (or XML) means that we do not know the structure of your JSON content in the code generator, so we cannot generate that POJO for you.

In this particular case, we could perhaps use some vendor specific parser sophistication to figure out what the contents of your JSON object are, since you're storing them in a view. But in a table, it wouldn't work, nor if you didn't use the code generator.

However, you don't really seem to care about the JSON document itself, you just seem to want to nest collections in SQL. And we're hopefully going to be able to implement that by emulating the SQL standard MULTISET() operator in the jOOQ API at some point: https://github.com/jOOQ/jOOQ/issues/3884

The emulation of MULTISET would probably use XML or JSON behind the scenes, transparently to you. We would still be unable to produce a POJO from such a query, but we could produce a correct generic type, having nested Result<...> and RecordN<...> types.

lukaseder commented 4 years ago

Closing this as won't fix. There are numerous improvements suggested here, some of which I've rejected, others would depend on various additional features.

jOOQ / jOOQ