CriteriaQuery DISTINCT values in an aggregation function

Question

Introduction

Consider a query like the following:

SELECT
    building.name                                 AS building_name,
    STRING_AGG(DISTINCT visit.visitor_name, ', ') AS visitors,
    COUNT(visit.id)                               AS total_visits
FROM building
    LEFT JOIN visit
        ON building.id = visit.building_id
GROUP BY building.id

Note the DISTINCT keyword used in STRING_AGG aggregation function. The results could look something like the following:

+----------------------+-------------+--------------+
|    building_name     |  visitors   | total_visits |
+----------------------+-------------+--------------+
| Skyline Residence    | Edgar, John |            6 |
| Forbidden Residence  |             |            0 |
| Cloud Nine Residence | John        |            1 |
+----------------------+-------------+--------------+

The important part is that if John and Edgar have visited Skyline Residence 6 times, then the visitors column should not display their names multiple times like John, Edgar, Edgar, Edgar, John, John.

JPA CriteriaQuery Code

This is my JPA CriteriaQuery code so far, without DISTINCT:

CriteriaBuilder builder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<ResultDto> query = builder.createQuery(ResultDto.class);

Root<Building> building = query.from(Building.class);
Join<Building, Visit> visit = building.join("visits", JoinType.LEFT);

query.select(builder.construct(
    ResultDto.class,
    building.get("name"),
    builder.function("STRING_AGG", String.class, visit.get("visitorName"), builder.literal(", ")),
    builder.count(visit.get("id")) //            ^ I need DISTINCT here ^
));

query.groupBy(building.get("id"));

final TypedQuery<ResultDto> typedQuery = getEntityManager().createQuery(query);
final List<ResultDto> resultList = typedQuery.getResultList();

How could I modify this code to generate the above SQL query, with DISTINCT?

Note: Do not answer with Hibernate's deprecated Criteria API solutions. It should be done using JPA's CriteriaQuery.

Answer 1

You can write a template for string_agg using MetadataBuilderContributor

public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {

@Override
public void contribute(MetadataBuilder metadataBuilder) {
    metadataBuilder.applySqlFunction(
            "string_agg_distinct",
            new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(distinct ?1, '; ')")
    );
}

}

And add in the properties:

spring.jpa.properties.hibernate.metadata_builder_contributor=<your-package>.SqlFunctionsMetadataBuilderContributor

And then use "string_agg_distinct" instead of "STRING_AGG" in function