PostgreSQL JSON字段查询：JPQL Repository中如何判断值是否存在

问题背景 #

PostgreSQL表中存在json类型的parts字段，需要判断指定值是否存在于该JSON数组中。原生SQL通过类型转换加模糊匹配可正常查询：

select * from elements we
where parts::text like '%"CHEST"%'

但直接在JPQL中照搬该逻辑会报错，无法正常运行。

问题分析 #

原JPQL代码存在两个核心问题：

1. 别名不匹配：JPQL中定义的实体别名是w，但WHERE子句中误用了we.parts，导致语法错误。
2. JPQL不支持PostgreSQL专属语法：::text是PostgreSQL的类型转换语法，JPQL作为跨数据库查询语言无法识别该写法。

可行解决方案 #

方案1：修正JPQL语法，兼容文本匹配 #

通过JPQL的FUNCTION函数调用PostgreSQL的cast方法实现类型转换，同时修正别名问题：

@Override
public List<ElementView> test() {
    return entityManager.createQuery(
                    "SELECT DISTINCT w FROM ElementView w " +
                            "WHERE FUNCTION('cast', w.parts, 'text') like '%\"CHEST\"%'", ElementView.class)
            .getResultList();
}

注意：需用\"转义双引号，避免误匹配到非数组元素的字符串（比如包含CHEST的其他字段值）。

方案2：使用PostgreSQL原生JSON函数（推荐） #

文本匹配存在误判风险（比如匹配到包含CHEST的长字符串），更可靠的方式是利用PostgreSQL专门的JSON数组查询函数：

方式A：使用json_array_elements_text展开数组

通过原生SQL查询，直接判断目标值是否在数组元素中：

@Override
public List<ElementView> test() {
    return entityManager.createNativeQuery(
                    "SELECT DISTINCT w.* FROM elements w WHERE 'CHEST' IN (SELECT json_array_elements_text(w.parts))",
                    ElementView.class)
            .getResultList();
}

方式B：使用jsonb_contains（适用于jsonb类型字段）

如果将parts字段改为jsonb类型（推荐，性能更优），可以直接用jsonb_contains函数判断：

@Override
public List<ElementView> test() {
    return entityManager.createNativeQuery(
                    "SELECT DISTINCT w.* FROM elements w WHERE jsonb_contains(w.parts, '\"CHEST\"')",
                    ElementView.class)
            .getResultList();
}

方式C：用Spring Data JPA的@Query注解简化代码

直接在Repository接口中定义原生查询：

@Query(value = "SELECT DISTINCT w.* FROM elements w WHERE 'CHEST' IN (SELECT json_array_elements_text(w.parts))", nativeQuery = true)
List<ElementView> findByPartsContainingChest();

内容的提问来源于stack exchange，提问作者pawello12