How to join tables and get the json output using jooq

Question

dslContext.select(
                    jsonObject(
                            key("id").value(CATEGORY.ID),
                            key("courses").value(
                                    jsonArrayAgg(
                                            jsonObject(
                                                    Arrays.stream(COURSE.fields())
                                                            .map(i -> key(CamelcaseConverter.snakeToCamel(i.getName())).value(
                                                                    i))
                                                            .collect(
                                                                    Collectors.toList())
                                            )
                                    )
                            )
                    )
            ).from(CATEGORY)
            .leftJoin(COURSE_CATEGORY).on(CATEGORY.ID.eq(COURSE_CATEGORY.CATEGORY_ID))
            .leftJoin(COURSE).on(COURSE.ID.eq(COURSE_CATEGORY.COURSE_ID)).fetchInto(JSONObject.class)

Output I got:

[
{
    "courses": [
        {
            "id": 19
        },
        {
            "id": null
        }
    ],
    "name": "Exam1",
    "id": 1,
}

]

The required output is

[
{
    "courses": [
        {
            "id": 19
        }
    ],
    "name": "Exam1",
    "id": 1
},
{
    "courses":[],
    "name": "Exam2",
    "id": 2
}

]

The query which need to be executed is "select * from category left outer join course_category on category.id = course_category.category_id left outer join course on course_category.course_id = course.id"

how do I implement it?

Answer 1

You forgot to group by:

.groupBy(CATEGORY.ID, CATEGORY.NAME)

If you have a primary (or unique) key on CATEGORY.ID, then in MySQL, it will be sufficient to group by that

.groupBy(CATEGORY.ID)