Question 273289 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – JOOQ How to produce unique list when joining multiple tables

I have three tables: SchoolTable, SchoolOrgTable, and SchoolDetailsTable. The relationships between these tables are as follows:

  • SchoolTable to SchoolOrgTable is a one-to-many relationship.
  • SchoolOrgTable to SchoolDetailsTable is a many-to-one relationship.

I’m currently retrieving data from these tables using a JOOQ query like this:

SelectJoinStep<Record> result = dsl.select()
    .from(SchoolTable)
    .join(SchoolOrgTable)
    .on(SchoolTable.A_ID.eq(SchoolOrgTable.ATest_ID))
    .leftJoin(SchoolOrgTable)
    .on(SchoolTable.A_ID.eq(SchoolOrgTable.B_Id))
    .leftJoin(SchoolDetailsTable)
    .on(SchoolDetailsTable.C_ID.eq(SchoolOrgTable.B_ID));

However, the result I’m getting is duplicating the schoolId for each associated SchoolDetails. Here’s an example of what I’m currently getting:

[
  {
    "schoolId": 1,
    "schoolName": "JaySchool",
    "isActive": true,
    "SchoolDetails": [
      {
        "detailsID": 1,
        "detailsName": "Test",
        "details": "schoolIsGood"
      }
    ]
  },
  {
    "schoolId": 1,
    "schoolName": "JaySchool",
    "isActive": true,
    "SchoolDetails": [
      {
        "detailsID": 2,
        "detailsName": "Test1",
        "details": "awesome"
      }
    ]
  },
  {
    "schoolId": 2,
    "schoolName": "TermSchool",
    "isActive": true,
    "SchoolDetails": [
      {
        "detailsID": 3,
        "detailsName": "Test",
        "details": "Nice"
      }
    ]
  }
]

I want the result to be structured like this, with each schoolId associated with its SchoolDetails:
Note: I should be able to groupBy schoolName,detailsName

[
  {
    "schoolId": 1,
    "schoolName": "JaySchool",
    "isActive": true,
    "SchoolDetails": [
      {
        "detailsID": 1,
        "detailsName": "Test",
        "details": "schoolIsGood"
      },
      {
        "detailsID": 2,
        "detailsName": "Test1",
        "details": "awesome"
      }
    ]
  },
  {
    "schoolId": 2,
    "schoolName": "TermSchool",
    "isActive": true,
    "SchoolDetails": [
      {
        "detailsID": 3,
        "detailsName": "test3",
        "details": "Nice"
      }
    ]
  }
]
Tags:

2

Answers


  1. 0

    The simplest way to get nested JSON documents is by using jOOQ’s SQL/JSON support, e.g. JSON_ARRAYAGG. For example:

    dsl.select(jsonObject(
        key("schoolId").value(SCHOOL.ID),
        key("schoolName").value(SCHOOL.NAME),
        key("isActive").value(SCHOOL.ACTIVE),
        key("SchoolDetails").value(jsonArrayAgg(jsonObject(
            key("detailsID").value(SCHOOL_DETAILS.ID),
            key("detailsName").value(SCHOOL_DETAILS.NAME),
            key("details").value(SCHOOL_DETAILS.DETAILS)
        )).orderBy(SCHOOL_DETAILS.NAME))
    ))
    .from(SCHOOL)
    .leftJoin(SCHOOL_DETAILS)
    .on(SCHOOL_DETAILS.SCHOOL_ID.eq(SCHOOL.ID))
    .groupBy(SCHOOL.ID) // Assuming there's a primary key here
    .orderBy(SCHOOL.NAME, SCHOOL.ACTIVE)
    .fetch();
    Login or Signup to reply.
  2. 0

    You can use multiple ways to solve your issue

    1. JSONObject (as in the other answer):

    It’s like native query

    dsl.select(jsonObject(
        key("schoolId").value(SCHOOL.ID),
        key("schoolName").value(SCHOOL.NAME),
        key("isActive").value(SCHOOL.ACTIVE),
        key("SchoolDetails").value(jsonArrayAgg(jsonObject(
            key("detailsID").value(SCHOOL_DETAILS.ID),
            key("detailsName").value(SCHOOL_DETAILS.NAME),
            key("details").value(SCHOOL_DETAILS.DETAILS)
        )).orderBy(SCHOOL_DETAILS.NAME))
    ))
    .from(SCHOOL)
    .leftJoin(SCHOOL_DETAILS)
    .on(SCHOOL_DETAILS.SCHOOL_ID.eq(SCHOOL.ID))
    .groupBy(SCHOOL.ID) // Assuming there's a primary key here
    .orderBy(SCHOOL.NAME, SCHOOL.ACTIVE)
    .fetch();
    1. multisetAgg:

    Docs: https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/aggregate-functions/multiset-agg-function/

    multisetAgg operates on the same principle as directly specifying JSONObject (from first option), but you don’t have to prescribe everything explicitly

    In this situation, this option is unlikely to work, but I am obliged to mention it. When executing the code, there will most likely be an incorrect alias binding. Perhaps this has been fixed in new versions

    dsl.select(
        SCHOOL.ID.as("schoolId"),
        SCHOOL.NAME.as("schoolName"),
        SCHOOL.ACTIVE.as("isActive"),
        multisetAgg( // aliases inside multisetAgg may not work
            SCHOOL_DETAILS.ID.as("detailsID"),
            SCHOOL_DETAILS.NAME.as("detailsName"),
            SCHOOL_DETAILS.DETAILS.as("details")
        ).orderBy(SCHOOL_DETAILS.NAME)).as("SchoolDetails")
    )
    .from(SCHOOL)
    .leftJoin(SCHOOL_DETAILS).on(SCHOOL_DETAILS.SCHOOL_ID.eq(SCHOOL.ID))
    .groupBy(SCHOOL.ID, SCHOOL.NAME, SCHOOL.ACTIVE)
    .orderBy(SCHOOL.NAME, SCHOOL.ACTIVE)
    .fetch();
    1. multiset:

    Docs: https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/multiset-value-constructor/

    Just like multisetAgg, multiset operates on the same principle as directly specifying JSONObject (from first option), but you don’t have to prescribe everything explicitly

    Briefly about multiset: a subquery is used inside the construction, which can refer to one or more values from an external query. In this case, the internal request refers to SCHOOL.ID for filtering values. This option is the most flexible

    dsl.select(
        SCHOOL.ID.as("schoolId"),
        SCHOOL.NAME.as("schoolName"),
        SCHOOL.ACTIVE.as("isActive"),
        multiset(
            dsl.select(
                SCHOOL_DETAILS.ID.as("detailsID"),
                SCHOOL_DETAILS.NAME.as("detailsName"),
                SCHOOL_DETAILS.DETAILS.as("details")
            )
                .from(SCHOOL_DETAILS)
                .where(SCHOOL_DETAILS.SCHOOL_ID.eq(SCHOOL.ID))
                .orderBy(SCHOOL_DETAILS.NAME)
        ).as("SchoolDetails")
    )
    .from(SCHOOL)
    .groupBy(SCHOOL.ID, SCHOOL.NAME, SCHOOL.ACTIVE)
    .orderBy(SCHOOL.NAME, SCHOOL.ACTIVE)
    .fetch();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search