jooq multiset jsonb column fails with Cannot construct instance of `org.jooq.JSONB`

i’m having my first hands on experience with jooqs new multiset feature. There is a product table, each product can be assigned a variable number of storages. Each storage may have a storage_coordinate_instance, whereas a storage_coordinate_instance has a column hierarchy which holds the resolved recursive representation of the storage location. (Updates are very rare, and the stored hierarchy avoids resolving a recursive query each time the storage location is needed).

All went fine until i tried to add the hierarchy column to the multiset.

Here’s the query:

    List<ProductFilterItem> items =
        dslContext
            .select(
                PRODUCT.ID,
                PRODUCT.NAME,
                PRODUCT.ARTICLE_NUMBER,
                PRODUCT.PHYSICAL,
                multiset(
                    select(
                        PRODUCT_STORAGE.ID,
                        PRODUCT_STORAGE.PRODUCT_ID,
                        PRODUCT_STORAGE.STOCK,
                        PRODUCT_STORAGE.COORDINATE_INSTANCE_ID,
                        STORAGE_COORDINATE_INSTANCE.HIERARCHY
                    )
                        .from(PRODUCT_STORAGE)
                        .leftOuterJoin(STORAGE_COORDINATE_INSTANCE).on(STORAGE_COORDINATE_INSTANCE.ID.eq(PRODUCT_STORAGE.COORDINATE_INSTANCE_ID))
                        .where(PRODUCT_STORAGE.PRODUCT_ID.eq(PRODUCT.ID))
                ).as("storage").convertFrom(r -> r.into(ProductStorageItem.class))
            )
            .from(PRODUCT)
            .where(queryCondition)
            .fetchInto(ProductItem.class)

and thats the class the storage items are mapped to:

  private static class ProductStorageItem {

    private final UUID id;
    private final UUID productId;
    private final Double stock;
    private final UUID coordinateInstanceId;
    private final JSONB hierarchy;

    public ProductStorageItem(UUID id, UUID productId, Double stock, UUID coordinateInstanceId, JSONB hierarchy) {
      this.id = id;
      this.productId = productId;
      this.stock = stock;
      this.coordinateInstanceId = coordinateInstanceId;
      this.hierarchy = hierarchy;
    }

The query performs without errors in case the current filtering does not return products with assigned storage_coordinate_instance.

Otherwise it yields

com.fasterxml.jackson.databind.exc.MismatchedInputException: Cannot construct instance of `org.jooq.JSONB` (although at least one Creator exists): cannot deserialize from Object value (no delegate- or property-based Creator)
 at [Source: (String)"{"606f4346-292e-4479-a2a1-39bb49e44873":"2","8d86347f-fc31-4f88-92c4-6e4f5fd12626":"2","d2bbbce0-1487-4f87-b458-07c4e8a54065":"2"}"; line: 1, column: 2]
    at com.fasterxml.jackson.databind.exc.MismatchedInputException.from(MismatchedInputException.java:63) ~[jackson-databind-2.12.3.jar:2.12.3]
    at com.fasterxml.jackson.databind.DeserializationContext.reportInputMismatch(DeserializationContext.java:1588) ~[jackson-databind-2.12.3.jar:2.12.3]
    at com.fasterxml.jackson.databind.DeserializationContext.handleMissingInstantiator(DeserializationContext.java:1213) ~[jackson-databind-2.12.3.jar:2.12.3]
    at com.fasterxml.jackson.databind.deser.BeanDeserializerBase.deserializeFromObjectUsingNonDefault(BeanDeserializerBase.java:1415) ~[jackson-databind-2.12.3.jar:2.12.3]
    at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserializeFromObject(BeanDeserializer.java:362) ~[jackson-databind-2.12.3.jar:2.12.3]
    at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserialize(BeanDeserializer.java:195) ~[jackson-databind-2.12.3.jar:2.12.3]
    at com.fasterxml.jackson.databind.deser.DefaultDeserializationContext.readRootValue(DefaultDeserializationContext.java:322) ~[jackson-databind-2.12.3.jar:2.12.3]
    at com.fasterxml.jackson.databind.ObjectMapper._readMapAndClose(ObjectMapper.java:4593) ~[jackson-databind-2.12.3.jar:2.12.3]
    at com.fasterxml.jackson.databind.ObjectMapper.readValue(ObjectMapper.java:3548) ~[jackson-databind-2.12.3.jar:2.12.3]
    at com.fasterxml.jackson.databind.ObjectMapper.readValue(ObjectMapper.java:3516) ~[jackson-databind-2.12.3.jar:2.12.3]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
    at org.jooq.impl.Convert$ConvertAll.from(Convert.java:1139) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.Convert.convert0(Convert.java:426) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.Convert.convert(Convert.java:501) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.AbstractDataType.convert(AbstractDataType.java:538) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.DefaultDataType.convert(DefaultDataType.java:97) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.ConvertedDataType.convert(ConvertedDataType.java:224) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.Tools.setValue(Tools.java:3068) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.DefaultRecordUnmapper$IterableUnmapper.unmap(DefaultRecordUnmapper.java:189) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.DefaultRecordUnmapper.unmap(DefaultRecordUnmapper.java:102) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.AbstractRecord.from0(AbstractRecord.java:911) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.AbstractRecord.from(AbstractRecord.java:941) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.JSONReader.lambda$read$1(JSONReader.java:201) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:143) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.JSONReader.read(JSONReader.java:200) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.JSONReader.read(JSONReader.java:110) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.DefaultBinding$DefaultResultBinding.readMultiset(DefaultBinding.java:3829) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.DefaultBinding$DefaultResultBinding.get0(DefaultBinding.java:3808) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.DefaultBinding$DefaultResultBinding.get0(DefaultBinding.java:3786) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.DefaultBinding$AbstractBinding.get(DefaultBinding.java:946) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1551) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.apply(CursorImpl.java:1500) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.apply(CursorImpl.java:1459) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:143) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1424) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1400) ~[jooq-3.15.3.jar:na]
    at java.base/java.util.Iterator.forEachRemaining(Iterator.java:132) ~[na:na]
    at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[na:na]
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:na]
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[na:na]
    at org.jooq.impl.AbstractCursor.collect(AbstractCursor.java:78) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.ResultQueryTrait.collect(ResultQueryTrait.java:358) ~[jooq-3.15.3.jar:na]
    at org.jooq.impl.ResultQueryTrait.fetchInto(ResultQueryTrait.java:1423) ~[jooq-3.15.3.jar:na]
    at package.MyProductDao.filterProducts(MyProductDao.java:129) ~[classes/:na]

Are there problems in the query? Or could it be a problem related to jooq’s multiset emulation for postgres based on JSON?

Java 11 Jooq 3.15.3 Postgres driver 42.2.24

Thanks for your help!

Kind regards, Andreas

Small Edit: How to order the result set by the size of of the multiset? Either by size actually, or by pseudo: put those with at least one storage first and those with none behind.

Answer

There seems to be a bug in jOOQ 3.15.3 that tries to map JSON data to JSON or JSONB via Jackson, instead of going through jOOQ’s own serialiser, internally, see:

• https://github.com/jOOQ/jOOQ/issues/12508 (The issue related to Jackson)
• https://github.com/jOOQ/jOOQ/issues/12509 (The capability of DefaultConverterProvider to offer such a mapping)

The bug will be fixed in 3.16.0 and 3.15.4. As a workaround, you could cast your JSONB document to TEXT for the time being.