I'm trying to filter users by a field which is stored as a JSONB and mapped as a class. In order to achieve this, I created a query using a CriteriaBuilder (shown below). The result though is empty, but if I retrieve the executed query from the logs and run it manually on the database, it does retrieve the expected user.
Field mapping:
@Type(type = "JSONB")
@Column(name = "authentication_providers", nullable = false, columnDefinition = "JSONB NOT NULL")
private Set<AuthenticationProvider> authenticationProviders;
Query building:
final Expression<?> provider = criteriaBuilder.function(
"JSONB",
String.class,
criteriaBuilder.literal(authenticationProvider.toJsonString()) // authenticationProvider is a function argument with the info to filter with
);
return criteriaBuilder.isTrue(criteriaBuilder.function(
"jsonb_contains",
Boolean.class,
from.get(User_.authenticationProviders),
provider
));
the toJsonString() method:
public String toJsonString() {
return "{\"provider_name\":\"" + name.name() + "\", \"id\":\"" + id + "\"}";
}
Executed query:
select user0_.id as id1_4_, ... from public.users user0_ where jsonb_contains(user0_.authentication_providers,JSONB(?))=true
and the according to the logs, the binding is:
binding parameter [1] as [VARCHAR] - [{"provider_name":"GOOGLE", "id":"somenumber"}]
Manually executed query:
select user0_.id as id1_4_, ... from public.users user0_ where jsonb_contains(user0_.authentication_providers, JSONB('[{"provider_name":"GOOGLE", "id":"somenumber"}]')) = true;
Any clues of what I'm doing wrong?
Note: The '...' in the query represent several other fields that I did not include to make the code more readable.
Edit:
I did also attempt a different approach using @Query and I got the same problem, the query does not return any results, but manually it does work.
repository:
@Query(nativeQuery = true, value = "SELECT * FROM users WHERE authentication_providers @> JSONB(:provider)")
Optional<User> findByAuthenticationProvider(@Param("provider") final String authenticationProvider);
Executed query:
SELECT * FROM users WHERE authentication_providers @> JSONB(?)
Binding:
binding parameter [1] as [VARCHAR] - [{"provider_name":"GOOGLE", "id":"somenumber"}]
Manually executed query:
SELECT * FROM users WHERE authentication_providers @> JSONB('[{"provider_name":"GOOGLE", "id":"somenumber"}]');