Spring Boot中MySQL JSON列的JPA查询：匹配指定store_id列表

你原来的查询存在核心问题：JSON_EXTRACT(m, '$.store_ids.store_id') 会返回一个JSON数组，无法直接和IN里的字符串值做匹配——两者类型不兼容，导致查询无法生效。下面提供几种可行的解决方式：

方式1：使用MySQL原生JSON函数（无需修改实体映射） #

适用于不想调整实体类结构，直接操作JSON字段的场景。假设你的实体类中对应JSON列的字段名为storeData（需用@Column(columnDefinition = "json")标记该字段）：

如果是固定的store_id列表，可直接写死查询：

@Query("SELECT m FROM MerchantManager m WHERE " +
       "JSON_CONTAINS(m.storeData, JSON_OBJECT('store_id', 'SSC10000020')) OR " +
       "JSON_CONTAINS(m.storeData, JSON_OBJECT('store_id', 'SSC10000022'))")
List<MerchantManager> findByStoreId();

如果需要动态传入store_id列表，改用原生查询并绑定参数更灵活：

@Query(nativeQuery = true, value = "SELECT * FROM merchant_manager m " +
       "WHERE EXISTS (" +
       "  SELECT 1 FROM JSON_TABLE(m.store_data, '$.store_ids[*]' COLUMNS(store_id VARCHAR(255) PATH '$.store_id')) jt " +
       "  WHERE jt.store_id IN (:storeIds)" +
       ")")
List<MerchantManager> findByStoreIds(@Param("storeIds") List<String> storeIds);

方式2：映射JSON数组为实体类集合（推荐） #

如果使用Hibernate 6.x及以上版本，可以直接将JSON数组映射为Java集合，用标准JPQL查询，无需写原生SQL：

1. 先定义嵌入式类对应单个store对象：

@Embeddable
public class Store {
    private String name;
    private String storeId;

    // 生成getter、setter方法
}

2. 在MerchantManager实体类中映射JSON字段：

@Entity
@Table(name = "merchant_manager")
public class MerchantManager {
    // 其他字段...

    @Column(columnDefinition = "json")
    @JsonType
    private List<Store> storeIds;

    // 生成getter、setter方法
}

3. 编写JPQL查询：

@Query("SELECT m FROM MerchantManager m JOIN m.storeIds s WHERE s.storeId IN (:storeIds)")
List<MerchantManager> findByStoreIds(@Param("storeIds") List<String> storeIds);

这种方式完全贴合JPA的面向对象风格，代码可读性和维护性都更高。

内容的提问来源于stack exchange，提问作者Vivek Singh Dhankhar