CriteriaQuery search in jsonb postgres column

Viewed 132

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?

0 Answers
Related