Map a JSONB containing an array to a list of POJO with JOOQ

I've the following JSONB column containing this data:

'
[
{
"date": X1,
"value": "Y1"
},
{
"date": X2,
"value": "Y2"
},
{
"date": X3,
"value": "Y3"
}
]
'

And I want to fetch it and convert it to a list of Pojos, where the Pojo is:

public record Point(LocalDate date, Double value)

This is what I'm doing now:

public Mono<List<Point>> getPoints(int id) {
return Mono.from(
dsl.select(MY_TABLE.POINTS)
.from(MY_TABLE)
.where(MY_TABLE.ID.eq(id)))
.map(record -> {
try {
return Arrays.stream(objectMapper.readValue(record.component1().data(), Point[].class)).toList();
} catch (JsonProcessingException e) {
throw new RuntimeException(e);
}
});
}

I was wondering if there is a cleaner solution, maybe using the default Jooq mapper to map the record to a List of Points. I tried to create a Wrapper:

public record PointWrapper(List<Point> points)

and use:

.map(record -> record.into(PointWrapper.class))

but what I get is a List of LinkedHashMap which of course generate a ClassCastException at runtime when I extract an element from the list to be used as Point.

There are several ways of using jOOQ's out of the box Jackson support:

Attaching a Jackson converter to your column

jOOQ's code generator supports "Jackson converters", i.e. converters that are "Jackson aware", in order to convert between JSON(B) and custom data types.

Relying on jOOQ's DefaultConverterProvider

When converting between JSON(B) and a custom data type, and when jOOQ finds Jackson (or Gson) on your classpath, then it will attempt to map between the data types. See this section that explains it. This is probably why you already got a List<Map<...>>

Why it didn't work for you

Both of the above approaches are just syntax sugar for what you already did, namely to extract the String content from JSONB.data(), because Jackson doesn't know about jOOQ's JSONB data type. Once you're there, you have to follow the mapping rules by Jackson to be able to map between types.

Notice how in your explicit example, you're mapping between the JSON array and a Point[], which have an obvious correspondence. In your PointWrapper example, you're using List<Point>, and jOOQ's DefaultRecordMapper (which tries to map a Record1<T> to a single-argument constructor call), doesn't know what to do request from Jackson given the List (rawtype) other than just the default behaviour. If you had a PointWrapper(Point[]), then it would probably work again.

In any case, the DefaultRecordMapper using reflection has its limitations, including those imposed by type erasure in Java. Some things could be improved, but in general, it's often better to be explicit and type safe. I'd just attach a Converter of some sort to the column. That way, you can always fetch the user defined type, which you prefer, and you never have to think about data type conversion again, let alone some quirks caused by type-unsafe reflection.