How to Fetch Multiple Level One to many Nesting with Jooq

Question

Here is the Scenario, I have 5 tables having one to many relations with each other, I have to map result data in hierarchical manner in below given Pojos with Jooq.

DB Tables are a, b, c, d, e

// Here are response Pojo's
Class APojo {
  public string name;
  public List<BPojo> listOfB;

}

Class BPojo {
  public string name;
  public List<CPojo> listOfC;
}

Class CPojo {
  public string name;
  public List<DPojo> listOfD;

}

Class DPojo {
  public string name;
  public List<EPojo> listOfE;

}

Class EPojo {
  public string name;

}

Expected sample response

{
  "name":"A1",
  "list_of_b":[
    {
      "name":"A1B1",
      "list_of_c":[
        {
          "name":"A1B1C1",
          "list_of_d":[
            {
              "name":"A1B1C1D1",
              "list_of_e":[
                {
                  "name":"A1B1C1D1E1"
                },
                {
                  "name":"A1B1C1D1E2"
                }
              ]
            },
            {
              "name":"A1B1C1D2",
              "list_of_e":[
                {
                  "name":"A1B1C1D2E1"
                },
                {
                  "name":"A1B1C1D2E2"
                }
              ]
            }
          ]
        },
        {
          "name":"A1B1C2",
          "list_of_d":[
            {
              "name":"A1B1C2D1",
              "list_of_e":[
                {
                  "name":"A1B1C2D1E1"
                },
                {
                  "name":"A1B1C2D1E2"
                }
              ]
            },
            {
              "name":"A1B1C2D2",
              "list_of_e":[
                {
                  "name":"A1B1C2D2E1"
                },
                {
                  "name":"A1B1C2D2E2"
                }
              ]
            }
          ]
        }
      ]
    },
    {
      "name":"A1B2",
      "list_of_c":[
        {
          "name":"A1B2C1",
          "list_of_d":[
            {
              "name":"A1B2C1D1",
              "list_of_e":[
                {
                  "name":"A1B1C1D1"
                },
                {
                  "name":"A1B1C1D2"
                }
              ]
            },
            {
              "name":"A1B2C1D2",
              "list_of_e":[
                {
                  "name":"A1B1C1D1"
                },
                {
                  "name":"A1B1C1D2"
                }
              ]
            }
          ]
        },
        {
          "name":"A1B2C2",
          "list_of_d":[
            {
              "name":"A1B2C2D1",
              "list_of_e":[
                {
                  "name":"A1B1C1D1"
                },
                {
                  "name":"A1B1C1D2"
                }
              ]
            },
            {
              "name":"A1B2C2D2",
              "list_of_e":[
                {
                  "name":"A1B1C1D1"
                },
                {
                  "name":"A1B1C1D2"
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

I tried something like this first but It did not work because fetch groups only accepts 2 arguments

using(configuration()).select(A.fields())
    .select(B.fields())
    .select(C.fields())
    .select(D.fields())
    .select(E.fields())
    .from(A)
    .join(B).on(A.ID.eq(B.A_ID)
    .join(C).on(B.ID.eq(C.B_ID)
    .join(D).on(C.ID.eq(D.C_ID)
    .join(E).on(D.ID.eq(E.D_ID)
    .fetchGroups(
        r -> r.into(A).into(APojo.class),
        r -> r.into(B).into(BPojo.class),
        r -> r.into(C).into(CPojo.class),
        r -> r.into(D).into(DPojo.class),
        r -> r.into(E).into(EPojo.class)
     ); 

Then I got this post and tried it as given below and other 2 method given in the post, but this also did not worked because Collectors.toMap accepts only 2 arguments and I have to fetch 5 level hierarchical data.

using(configuration()).select(A.fields())
        .select(B.fields())
        .select(C.fields())
        .select(D.fields())
        .select(E.fields())
        .from(A)
        .join(B).on(A.ID.eq(B.A_ID)
        .join(C).on(B.ID.eq(C.B_ID)
        .join(D).on(C.ID.eq(D.C_ID)
        .join(E).on(D.ID.eq(E.D_ID)
        .collect(Collectors.groupingBy(
            r -> r.into(A).into(APojo.class),
            Collectors.toMap(
                r -> r.into(B).into(BPojo.class),
                r -> r.into(C).into(CPojo.class)
                r -> r.into(D).into(DPojo.class)
                r -> r.into(E).into(EPojo.class)
 )));

Answer 1

The JOIN approach

Historically, most ORMs attempted to nest collections in some way using joins, since joins have been the only widely supported way of "connecting" collections (but not nesting them) in SQL. The result is a flat, denormalised table, that is hard to normalise again. There are a lot of duplicates, and possibly even unwanted cartesian products, and it might not even be possible to be sure what nested collection belongs to a parent value. In your case, it would be possible, but very wasteful both on the server and on the client. The values of A would be repeated many many times.

Some workarounds have been implemented, including by third parties (for jOOQ). Alternatives include running several queries and connecting the values afterwards. All of them very tedious.

Luckily, jOOQ 3.14+ offers support for nesting collections out of the box!

A jOOQ 3.14 approach using SQL/JSON

The jOOQ 3.14 appraoch to nesting collections is using SQL/JSON behind the scenes (or SQL/XML, but in your case, JSON seems more appropriate).

From your question, I don't see why you need the POJO intermediate step, so perhaps you can bypass that and generate the JSON directly in the database. If not, see below.

Write this query:

ctx
  .select(
    // Optionally, wrap this level in jsonArrayAgg(jsonObject()) too, like the others
    A.NAME,
    field(
      select(jsonArrayAgg(jsonObject(
        key("name").value(B.NAME),
        key("list_of_c").value(
          select(jsonArrayAgg(jsonObject(
            key("name").value(C.NAME),
            key("list_of_d").value(
              select(jsonArrayAgg(jsonObject(
                key("name").value(D.NAME),
                key("list_of_e").value(
                  select(jsonArrayAgg(jsonObject(key("name").value(E.NAME))))
                  .from(E)
                  .where(E.D_ID.eq(D.ID))
                )
              )))
              .from(D)
              .where(D.C_ID.eq(C.ID))
            )
          )))
          .from(C)
          .where(C.B_ID.eq(B.ID))
        )
      )))
      .from(B)
      .where(B.A_ID.eq(A.ID))
    ).as("list_of_b")
  )
  .from(A)
  .fetch();

The usual static import is assumed:

import static ord.jooq.impl.DSL.*;

Since jOOQ is all about dynamic SQL, chances are, you can automate some of the nesting with dynamic SQL.

All of the above also works with JSONB in PostgreSQL, just use jsonbArrayAgg() and jsonbObject() instead.

Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that's a problem, use COALESCE()

Mapping the above into POJOs

If you have Jackson or Gson on your classpath, you can now just write fetchInto(APojo.class) at the end to map the resulting JSON tree. But you're probably just going to map the POJOs back into JSON again using either Jackson or Gson, so from a high level, I don't think you get a lot of value out of this step.

The jOOQ 3.15 approach to nesting collections

Starting from jOOQ 3.15, a number of improvements to type safe mapping and nesting collections will be implemented

• #3884 MULTISET and ARRAY constructor from subquery support (finally!)
• #7100 Ad-hoc Field data type conversion convenience
• #11804 Type safe mapping of Record[N] types to constructor references
• #11812 Support for nested ROW expressions in projections

With all of the above, in case you really need your POJO intermediary step (and assuming you will have the necessary "immutable constructors" on your POJOs, e.g. like canonical record constructors in Java 16+). E.g.

record EPojo (String name) {}
record DPojo (String name, EPojo[] listOfE) {}
record CPojo (String name, DPojo[] listOfD) {}
record BPojo (String name, CPojo[] listOfC) {}
record APojo (String name, BPojo[] listOfB) {}

In that case, you will be able to write something like this:

ctx
  .select(A.NAME, array(
     select(row(B.NAME, array(
       select(row(C.NAME, array(
         select(row(D.NAME, array(
           select(row(E.NAME).mapping(EPojo::new))
           .from(E)
           .where(E.D_ID.eq(D.ID))
         )).mapping(DPojo::new))
         .from(D)
         .where(D.C_ID.eq(C.ID))
       )).mapping(CPojo::new))
       .from(C)
       .where(C.B_ID.eq(B.ID))
     )).mapping(BPojo::new))
     .from(B)
     .where(B.A_ID.eq(A.ID))
  )
  .from(A)
  .fetch(Records.mapping(APojo::new));

If you prefer List<SomePojo> over SomePojo[], then you'd just have to use the new ad-hoc conversion from arrays to list on the array expressions, e.g.

array(select(...)).convertFrom(Arrays::asList)

I'll update this part of the answer, once the API has stabilised.

Further outlook

Since these types of "nested collection joins" will become very common in jOOQ, irrespective of whether SQL collections are nested in SQL, or JSON collections, or XML collections, future versions of jOOQ will probably offer a more convenient syntax, similar to the implicit join syntax for ordinary to-one joins.