Spring data criteria query not returning results, but executing it manually does

Viewed 35

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"}]');
0 Answers
Related