JOOQ arrayAgg array_agg fields as object

Question

I have 2 entities:

record Customer(String name, List<CustomerContact > contactHistory) {}

record CustomerContact(LocalDateTime contactAt, Contact.Type type) {
  public enum Type {
    TEXT_MESSAGE, EMAIL
  }
}

These are persisted in a schema with 2 tables:

CREATE TABLE customer(
  "id".   BIGSERIAL PRIMARY KEY,
  "name"  TEXT      NOT NULL
);

CREATE TABLE customer_contact(
  "customer_id" BIGINT REFERENCES "customer" (ID) NOT NULL,
  "type"        TEXT                           NOT NULL,
  "contact_at"  TIMESTAMPTZ                    NOT NULL DEFAULT (now() AT TIME ZONE 'utc')
);

I want to retrieve the details of my Customers with a single query, and use the arrayAgg method to add the contactHistory to each customer. I have a query like this:

//pseudo code

DSL.select(field("customer.name"))
   .select(arrayAgg(field("customer_contact.contact_at")) //TODO How to aggregate both fields into a CustomerContact object
   .from(table("customer"))
   .join(table("customer_contact")).on(field("customer_contact.customer_id").eq("customer.id"))
   .groupBy(field("customer_contact.customer_id"))
   .fetchOptional()
   .map(asCustomer());

The problem I have with this is that arrayAgg will only work with a single field. I want to use 2 fields, and bind them into a single object (CustomerContact) then use that as the basis for the arrayAgg

Apologies if I have not explained this clearly! Any help much appreciated.

Lukas Eder · Accepted Answer · 2022-04-27 11:46:42Z

2

Rather than using ARRAY_AGG, how about using the much more powerful MULTISET_AGG or MULTISET to get the most out of jOOQ's type safe capabilities? Combine that with ad-hoc conversion for type safe mapping to your Java records, as shown also in this article. Your query would then look like this:

Using `MULTISET_AGG`

List<Customer> customers =
ctx.select(
        CUSTOMER.NAME,
        multisetAgg(CUSTOMER_CONTACT.CONTACT_AT, CUSTOMER_CONTACT.TYPE)
            .convertFrom(r -> r.map(Records.mapping(CustomerContact::new))))
   .from(CUSTOMER)
   .join(CUSTOMER_CONTACT).on(CUSTOMER_CONTACT.CUSTOMER_ID.eq(CUSTOMER.ID))
   .groupBy(CUSTOMER_CONTACT.CUSTOMER_ID)
   .fetch(Records.mapping(Customer::new));

Note that the entire query type checks. If you change anything about the query or about your records, it won't compile anymore, giving you additional type safety. This is assuming that youre Type enum is either:

Generated from a PostgreSQL ENUM type
Converted automatically using an enum converter, attached to generated code

Depending on your tastes, using implicit joins could slightly simplify the query for you?

List<Customer> customers =
ctx.select(
        CUSTOMER_CONTACT.customer().NAME,
        multisetAgg(CUSTOMER_CONTACT.CONTACT_AT, CUSTOMER_CONTACT.TYPE)
            .convertFrom(r -> r.map(Records.mapping(CustomerContact::new))))
   .from(CUSTOMER_CONTACT)
   .groupBy(CUSTOMER_CONTACT.CUSTOMER_ID)
   .fetch(Records.mapping(Customer::new));

It's not a big deal in this query, but in a more complex query, it can reduce complexity.

Using `MULTISET`

An alterantive is to nest your query instead of aggregating, like this:

List<Customer> customers =
ctx.select(
        CUSTOMER.NAME,
        multiset(
            select(CUSTOMER_CONTACT.CONTACT_AT, CUSTOMER_CONTACT.TYPE)
            .from(CUSTOMER_CONTACT)
            .where(CUSTOMER_CONTACT.CUSTOMER_ID.eq(CUSTOMER.ID))
        ).convertFrom(r -> r.map(Records.mapping(CustomerContact::new))))
   .from(CUSTOMER)
   .fetch(Records.mapping(Customer::new));

Code generation

For this answer, I was assuming you're using the code generator (you should!), as it would greatly contribute to this code being type safe, and make this answer more readable.

Much of the above can be done without code generation (except implicit joins), but I'm sure this answer could nicely demonstrate the benefits it terms of type safety.

answered Apr 27, 2022 at 11:46

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

robjwilkins Over a year ago

Thanks for the reply. These multiset methods are only available with JOOQ 3.15+? I think we're using 3.14 unfortunately.

Lukas Eder Over a year ago

@robjwilkins: Time to upgrade

robjwilkins Over a year ago

Think we're restricted due to Springboot not supporting Jooq above v3.14? Possible to get this working by calling the multiset using the Jooq function method?

Lukas Eder Over a year ago

@robjwilkins: Spring Boot does not restrict you in any way from this upgrade! You can't get my answer to work on jOOQ 3.14, this is a massive new feature from jOOQ 3.15.

Andrey Minogin Nov 27 at 12:30

@Lukas Eder Multiset uses json_array_agg internally? The problem is that json_array_agg is much slower than array_agg. Is there a possibility to avoid using json in this case?

Lukas Eder Nov 27 at 13:13

I'm not convinced that you're correct with this high level assessment, check out this significant flaw of using arrays of rows: postgresql.org/message-id/…. You can't currently work with array_agg() to emulate MULTISET in PostgreSQL, but you can use arrayAgg() or array(), of course!

wipallen · Accepted Answer · 2023-02-27 22:03:48Z

If upgrade is not an options, one way to do this is:

using the .arrayAgg(field()) but instead of "customer_contact.contact_at" use json_build_object as in

arrayAgg(field(fieldsToJson(customer_contact.contact_at, customer_contact.FIELD_TWO))).as(CUSTOMER_CONTACT_LIST))

    private String fieldsToJson(List<TableField<?,?>> fields) {
        StringBuilder sb = new StringBuilder();
        sb.append("json_build_object(");
        for (Field<?> field : fields) {
            sb.append("'").append(CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, field.getName())).append("'").append(",").append(field).append(",");
        }
        sb.replace(sb.length() - 1, sb.length(), ")");
        return sb.toString();
    }

Then in your mapper use an ObjectMapper in turn the JSON into your CustomerContact object.

Object[] contacts = record.get(CUSTOMER_CONTACT_LIST, Object[].class);

then for each:

objectMapper.readValue(contactObject.toString(), CustomerContact.class);

Collectives™ on Stack Overflow

JOOQ arrayAgg array_agg fields as object

2 Answers 2

Using `MULTISET_AGG`

Using `MULTISET`

Code generation

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Using MULTISET_AGG

Using MULTISET

Code generation

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Using `MULTISET_AGG`

Using `MULTISET`