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.
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.
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"));