MySQL JSON type Value search with SpringBoot + Spring JPA

Overview

I investigated the Value value search of JSON type column using JPA, so as a share. Case to search by Specification.

Conclusion

Use MySQL functions.

JSON_EXTRACT

SELECT * FROM ATABLE WHERE JSON_EXTRACT("column_name", "$.key") = value;

So, when using Spring JPA Specification,

public Specification<T> jsonSearch(String key, String val) {
	return  (root, query, cb) -> {
	    return cb.like(cb.function("JSON_EXTRACT", String.class, root.get("names"), cb.literal("$." + key)),
		    "%" + val + "%");
	};
}

reference

Custom expression in JPA CriteriaBuilder

Recommended Posts

MySQL JSON type Value search with SpringBoot + Spring JPA
OR search with Spring Data Jpa Specification
Search JPQL for tables with JSON type columns
Dynamically generate queries with Spring Data JPA (multi-word search) (paging support)
Dealing with Mysql2 :: Error: Incorrect string value error
Implement CRUD with Spring Boot + Thymeleaf + MySQL
Jackson is unable to JSON serialize hibernateLazyInitializer in Spring Data JPA with error
Register request parameter type conversion with SpringBoot + MVC
Create a simple search app with Spring Boot
[spring-boot] Complement application.yml with IntelliJ spring assistant plugin
Sort by Spring Data JPA (with compound key sort)
Creating a common repository with Spring Data JPA