The query statement which need to be executed is

dslContext.select(
            jsonObject(
                key("id").value(ENTITY.ID),
                key("name").value(ENTITY.NAME),
                key("attributes").value(
                    coalesce(
                        select(
                            jsonArrayAgg(
                                jsonObject(
                                    key("id").value(ATTRIBUTE.ID),
                                    key("name").value(ATTRIBUTE.NAME),
                                    key("indexValue").value(ATTRIBUTE.INDEX_VALUE)
                                )
                            )
                        ).from(ATTRIBUTE)
                            .where(ATTRIBUTE.ENTITY_ID.eq(ENTITY.ID))
                            .orderBy(ATTRIBUTE.INDEX_VALUE.asc()),
                        jsonArray()
                    )
                )
            )
        ).from(ENTITY).fetchInto(EntityDto.class)

Response for the above query:

[
    {
        "id": 2,
        "name": "Address",
        "attributes": [
            {
                "id": 3,
                "name": "Pincode",
                "indexValue": 4
            },
            {
                "id": 4,
                "name": "Country",
                "indexValue": 3
            },
            {
                "id": 5,
                "name": "City",
                "indexValue": 2
            },
            {
                "id": 6,
                "name": "Address",
                "indexValue": 1
            }
        ]
    }
]

The attributes are not sorting in ascending order with respect to indexValue. How to make the attributes sort in the ascending order?

CodePudding user response：

Use the ORDER BY clause on JSON_ARRAYAGG:

jsonArrayAgg(...).orderBy(...)