2

I am building graphql-java spring boot app. Lets say I have this schema:

type Foo {
id: ID! name: String bars: [Bar]
}
type Bar {
id: ID! name: String
}
query{
  foo(arg: String): [Foo]
}

On the BE I am using JOOQ pojos.

Currently all Foos are fetched and then for each foo all Bars are fetched with another query. I want to do do this with a single query:

select * from foo 
left join bar on foo.id = bar.foo_id;

Do you have any idea how to achieve this? I am a real newbie in GraphQL.

Tech Stack: Spring Boot JOOQ graphql-java graphql-java-tools

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
K.Kostadinov
  • 194
  • 10

1 Answers1

4

Classic SQL strategies to nest collections

In your simple example, a LEFT JOIN is definitely an option. This is what ORMs like JPA/Hibernate do as well behind the scenes when they fetch nested collections. The strategy has some disadvantages:

  • There is data duplication. For each BAR (the child table), you will duplicate the data of the matching FOO (the parent table). This can lead to a lot of overhead to transfer over the wire. This gets worse and worse the more joins (i.e. the more nesting) you require
  • There are cartesian products. You cannot nest more than one collection without producing cartesian products between the child tables, if you want to do it in one go. With a cartesian product, it will be difficult to "remember", which combinations are actually legitimate, and which ones are artifacts of your join tree. So, you'll be back to running several queries, one for each nesting tree branch

Using standard SQL JSON

But behold, there's a better way, starting from jOOQ 3.14. You can use XML or JSON, depending on what database dialect you're using. The key features here are XMLAGG and JSON_ARRAYAGG, which allow for aggregating data into an XML element or a JSON object. Since you're going to produce JSON documents with GraphQL, I guess you'll be using JSON.

Using standard SQL (e.g. as implemented by Oracle), your generated SQL query for your example could look like this:

SELECT
  JSON_ARRAYAGG(
    JSON_OBJECT(
      KEY "id" VALUE foo.id,
      KEY "name" VALUE foo.name,
      KEY "bars" VALUE (
        SELECT 
          JSON_ARRAYAGG(
            JSON_OBJECT(
              KEY "id" VALUE bar.id,
              KEY "name" VALUE bar.name
            )
          )
        FROM bar
        WHERE bar.foo_id = foo.id
      )
    )
  )
FROM foo

As it stands now, these dialects should be able to support jsonArrayAgg() and jsonObject() (or an emulation thereof, e.g. in PostgreSQL):

  • CockroachDB
  • Db2 LUW 11+
  • H2
  • MariaDB 10.2+
  • MySQL 5.7+
  • Oracle 12c+
  • PostgreSQL

More details in this blog post.

An emulation in SQL Server using FOR JSON might be possible in the future as well.

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

Out of the box solution

As a matter of fact, this has crossed my mind before, to provide this out of the box. We might do this in the near future: https://github.com/jOOQ/jOOQ/issues/10122

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509