JPA criteria builder equal method is not working as expected

Question

In my case I'm trying to fetch some data by extracting a value from a json column in db. My code is as follows,

criteriaBuilder.equal(criteriaBuilder.function("JSON_EXTRACT", Boolean.class, root.get("result"), criteriaBuilder.literal("$.matched")), false);

Above code gives me an empty set of data. Also this is working fine in query console.

But,

criteriaBuilder.between(criteriaBuilder.function("JSON_EXTRACT", Double.class, root.get("result"), criteriaBuilder.literal("$.streaming_threshold")), 0.1, 0.9);

this between method is working fine. What could be the mistake here?

UPDATE

Boolean values are the values that I couldn't read. NOT INTEGERS. My JSON structure,

{
    "status": "SUCCESS",
    "request_id": "request_id",
    "time_taken": 8454,
    "matched": false,
    "streaming_threshold": 0.5
}

Answer 1

Default JPA convert boolean to 0/1, so if serialize boolean to 0/1 into database, the equal query will be ok.

Answer 2

I was not able to get it working with the raw boolean field. I converted the Boolean property on the object to String and vice-versa using @JsonSerialize and @JsonDeserialize and then persisted that as json and followed the same approach you did but now searching String.class as false instead of Boolean. My solution is as below:

Entity

@Entity
@Table(name = "json_container")
public class JsonContainer {

    @Id
    @GeneratedValue
    @Type(type = "uuid-char")
    private UUID id;

    @Column(columnDefinition = "json", name = "json_data")
    private String jsonData;

    public UUID getId() {
        return id;
    }

    public String getJsonData() {
        return jsonData;
    }

    public void setJsonData(String jsonData) {
        this.jsonData = jsonData;
    }

    public static class SampleDetails {
        private String status;
        private String requestId;
        private Integer timeTaken;
        @JsonSerialize(using = StringBooleanJsonSerializer.class)
        @JsonDeserialize(using = StringBooleanJsonDeserializer.class)
        private Boolean matched;
        private Double streamingThreshold;

        public SampleDetails() {
        }

        public SampleDetails(String status, String requestId, Integer timeTaken, Boolean matched, Double streamingThreshold) {
            this.status = status;
            this.requestId = requestId;
            this.timeTaken = timeTaken;
            this.matched = matched;
            this.streamingThreshold = streamingThreshold;
        }

        public String getStatus() {
            return status;
        }

        public String getRequestId() {
            return requestId;
        }

        public Integer getTimeTaken() {
            return timeTaken;
        }

        public Double getStreamingThreshold() {
            return streamingThreshold;
        }

        public Boolean getMatched() {
            return matched;
        }


        static class StringBooleanJsonSerializer extends JsonSerializer<Boolean> {

            @Override
            public void serialize(Boolean value, JsonGenerator gen, SerializerProvider serializers) throws IOException {
                gen.writeString(value != null && value ? value.toString() : "false");
            }
        }

        static class StringBooleanJsonDeserializer extends JsonDeserializer<Boolean> {

            @Override
            public Boolean deserialize(JsonParser p, DeserializationContext ctxt) throws IOException, JsonProcessingException {
                try {
                    return Boolean.parseBoolean(p.getText());
                } catch (RuntimeException e) {
                    return Boolean.FALSE;
                }
            }
        }
    }

Test Class

class MySQLJsonConverterTest {

    @Autowired
    private EntityManager entityManager;


    @Rollback(false)
    @Test
    void testCustomJsonConverter() throws JsonProcessingException {
        JsonContainer jsonContainer = new JsonContainer();
        jsonContainer.setJsonData(
                getAsJson(new JsonContainer.SampleDetails("success", "12344567", 8454, false, 0.1)));
        entityManager.persist(jsonContainer);
        Assertions.assertNotNull(jsonContainer.getId());

        jsonContainer = new JsonContainer();
        jsonContainer.setJsonData(
                getAsJson(new JsonContainer.SampleDetails("success", "8989", 121, true, 0.5)));
        entityManager.persist(jsonContainer);
        Assertions.assertNotNull(jsonContainer.getId());

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<JsonContainer> criteriaQuery = criteriaBuilder.createQuery(JsonContainer.class);
        Root<JsonContainer> from = criteriaQuery.from(JsonContainer.class);

        criteriaQuery.where(criteriaBuilder.equal(criteriaBuilder.function("JSON_EXTRACT", String.class, from.get("jsonData"),
                criteriaBuilder.literal("$.matched")), "false"));

        TypedQuery<JsonContainer> typedQuery = entityManager.createQuery(criteriaQuery);
        List<JsonContainer> resultList = typedQuery.getResultList();
        Assertions.assertEquals(1, resultList.size());
    }

    private String getAsJson(JsonContainer.SampleDetails sampleDetails) throws JsonProcessingException {
        //var created so debugging is ez
        String json = new ObjectMapper().writeValueAsString(sampleDetails);
        return json;
    }
}