Filter JSON column from MySQL table in spring boot-CodePudding

Below is my query to filter the json column in MySQL table ,

    public interface MpaCustomizationRepository extends
        JpaRepository<MpaCustomization, Serializable> {

@Query(nativeQuery = true, value = "select * from MPA_CUSTOMIZATION where json_contains(domain_based_properties, '{\"id\" : ?1}')")
MpaCustomization findByDomainBaseId(String domainId);

}

above method throws below SQL error,

but if I hard code the variable and run the query like below its works fine,

@Query(nativeQuery = true, value = "select * from MPA_CUSTOMIZATION where json_contains(domain_based_properties, '{\"id\" : 2}')")
    MpaCustomization findByDomainBaseId(String domainId);

How to fix this issue?

CodePudding user response：

Use $.id for values when using native query, as SQL accepts like it only. Other way, you may use :id and bind parameter {\"id\":\"" <value> "\"} to the query.

CodePudding user response：

Maybe the following will work.

@Query(nativeQuery = true, value = "select * from MPA_CUSTOMIZATION where json_contains(domain_based_properties, ?1 , '$.id'")
MpaCustomization findByDomainBaseId(String domainId);

Source