How to create where statement based on result of multiset

Question

So, i would like to filter my query by exact match in result of multiset. Any ideas how to do it in JOOQ?

Example:

val result = dsl.select(
        PLANT_PROTECTION_REGISTRATION.ID,
        PLANT_PROTECTION_REGISTRATION.REGISTRATION_NUMBER,
        PLANT_PROTECTION_REGISTRATION.PLANT_PROTECTION_ID,
        multiset(
            select(
                PLANT_PROTECTION_APPLICATION.ORGANISM_ID,
                PLANT_PROTECTION_APPLICATION.ORGANISM_TEXT
            ).from(PLANT_PROTECTION_APPLICATION)
                .where(PLANT_PROTECTION_APPLICATION.REGISTRATION_ID.eq(PLANT_PROTECTION_REGISTRATION.ID))
        ).`as`("organisms")
    ).from(PLANT_PROTECTION_REGISTRATION)
    // here i would like to filter my result only for records that their organisms contain specific 
    // organism id        
   .where("organisms.organism_id".contains(organismId))

Answer 1

I've explained the following answer more in depth in this blog post

About the MULTISET value constructor

The MULTISET value constructor operator is so powerful, we'd like to use it everywhere :) But the way it works is that it creates a correlated subquery, which produces a nested data structure, which is hard to further process in the same SQL statement. It's not impossible. You could create a derived table and then unnest the MULTISET again, but that would probably be quite unwieldy. I've shown an example using native PostgreSQL in that blog post

Alternative using MULTISET_AGG

If you're not nesting things much more deeply, how about using the lesser known and lesser hyped MULTISET_AGG alternative, instead? In your particular case, you could do:

// Using aliases to make things a bit more readable
val ppa = PLANT_PROTECTION_APPLICATION.as("ppa");

// Also, implicit join helps keep things more simple
val ppr = ppa.plantProtectionRegistration().as("ppr");

dsl.select(
        ppr.ID,
        ppr.REGISTRATION_NUMBER,
        ppr.PLANT_PROTECTION_ID,
        multisetAgg(ppa.ORGANISM_ID, ppa.ORGANISM_TEXT).`as`("organisms"))
   .from(ppa)
   .groupBy(
        ppr.ID,
        ppr.REGISTRATION_NUMBER,
        ppr.PLANT_PROTECTION_ID)

   // Retain only those groups which contain the desired ORGANISM_ID
   .having(
        boolOr(trueCondition()) 
        .filterWhere(ppa.ORGANISM_ID.eq(organismId)))
   .fetch()