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))

CodePudding user response：

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()