hibernatecriteria-api

Criteria Builder: How to use label for JSON_OBJECT


I have a requirement where jsonobject needs to be created at runtime using criteria builder. Let me know how I can achieve the same. I use Mysql as database.

user_projects table

Intended sql: select project_id, json_arrayagg(json_object('name', project_name, 'description', description)) from user_projects group by project_id

Code so far ..

        CriteriaQuery<Tuple> criteriaQuery = cBuilder.createTupleQuery();

        Root<UserProjects> userProjectsRoot = criteriaQuery.from(UserProjects.class);

         // NOT SURE HOW TO INSERT LABELS i.e., 'name' and 'description' in json_object function.. 
        criteriaQuery.multiselect(userProjectsRoot.get("projectId").alias("Id"),
                cBuilder.function("json_arrayagg", String.class,
                        cBuilder.function("json_object", String.class,
                                userProjectsRoot.get("projectName"),
                                userProjectsRoot.get("description"))).alias("json"));

        criteriaQuery.groupBy(userProjectsRoot.get("projectId"));

Any help would be appreciated.


Solution

  • Apparently, in your SQL the labels are just string literals, so just use a string literal here as well.

        criteriaQuery.multiselect(userProjectsRoot.get("projectId").alias("Id"),
                cBuilder.function("json_arrayagg", String.class,
                        cBuilder.function("json_object", String.class,
                                cBuilder.literal("name"), userProjectsRoot.get("projectName"),
                                cBuilder.literal("description"), userProjectsRoot.get("description"))).alias("json"));