CODEWITHSUNDEEP
javapostgresqljooq
robjwilkins
robjwilkins

Reputation: 5652

JOOQ arrayAgg array_agg fields as object

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.

Upvotes: 2

Views: 1172

Answers (2)

wipallen
wipallen

Reputation: 154

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

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220952

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:

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.

Upvotes: 2

Related Questions