I have table with jsonb column "opinion_cells", example data:
[{"isFinalOpinion": false, "assignedPersonId": null, "organizationUnitId": 2},
{"isFinalOpinion": false, "assignedPersonId": 12, "organizationUnitId": 3}]
I need to select records by "assignedPersonId" using criteria query.
I tried to write expression:
Expression<String> opinionCellAssignedPerson = criteriaBuilder.function(
"json_extract_path_text",
String.class,
internalLetterRoot.get(InternalLetter_.OPINION_CELLS),
criteriaBuilder.literal("assignedPersonId")
);
but in returns nothing, because of [] content is treated as an array. When I remove [] from column content then it works.
I also tried
Expression<String> opinionCellArrayToJson = criteriaBuilder.function(
"json_array_elements",
String.class,
internalLetterRoot.get(InternalLetter_.OPINION_CELLS)
);
and then:
Expression opinionCellAssignedPerson = criteriaBuilder.function( "json_extract_path_text", String.class, opinionCellArrayToJson , criteriaBuilder.literal("assignedPersonId") );
but it returns:
org.postgresql.util.PSQLException: ERROR: argument of OR must not return a set
My question is: It is possible using criteria query?