Spring JPA：PostgreSQL JSONB字段分页排序失效问题

使用Criteria API Specification结合PageRequest查询记录时，按实体直接属性（如division）排序正常，但按JSONB嵌套属性publicationSchedule.deadlineDate排序时抛出异常。

实体类定义 #

public class Publication {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "book_price")
    private String bookPrice;

    @Column(name = "division")
    private String division;

    @Column(columnDefinition = "JSONB", name = "publication_schedule")
    @Type(JsonBinaryType.class)
    PublicationSchedule publicationSchedule;
}

// PublicationSchedule为JSON嵌套对象，非实体类
public class PublicationSchedule{

    @JsonSerialize(using = TimeStampSerializer.class)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date deadlineDate;

    @JsonSerialize(using = TimeStampSerializer.class)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date publicationDate;
}

异常场景 #

当排序字段设置为division时查询正常；设置为publicationSchedule.deadlineDate时，执行以下代码抛出异常：

Specification<Publication> specification = Specification.where(null);
// <<Specification代码生成逻辑>>
Page<Publication> searchResults = publicationRepository.findAll(specification, pageRequest);

异常信息 #

ERROR org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet].log(175) - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: Attribute 'com.elsevier.rpc.service.bff.entity.Publication#publicationSchedule(BASIC)' is not joinable] with root cause
org.hibernate.query.SemanticException: Attribute 'com.elsevier.rpc.service.bff.entity.Publication#publicationSchedule(BASIC)' is not joinable
    at org.hibernate.query.sqm.tree.domain.AbstractSqmFrom.buildSingularJoin(AbstractSqmFrom.java:762) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.sqm.tree.domain.AbstractSqmFrom.buildJoin(AbstractSqmFrom.java:697) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.sqm.tree.domain.AbstractSqmFrom.join(AbstractSqmFrom.java:410) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.sqm.tree.domain.AbstractSqmFrom.join(AbstractSqmFrom.java:69) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.springframework.data.jpa.repository.query.QueryUtils.getOrCreateJoin(QueryUtils.java:903) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.QueryUtils.toExpressionRecursively(QueryUtils.java:799) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.QueryUtils.toExpressionRecursively(QueryUtils.java:769) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.QueryUtils.toExpressionRecursively(QueryUtils.java:765) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.QueryUtils.toJpaOrder(QueryUtils.java:754) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.QueryUtils.toOrders(QueryUtils.java:706) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:753) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:710) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:447) ~[spring-data-jpa-3.2.4.jar:3.2.4]

问题根源 #

Spring Data JPA的QueryUtils会尝试对嵌套属性路径（如publicationSchedule.deadlineDate）执行JOIN操作，但publicationSchedule是JSONB类型的基本属性（BASIC），并非关联实体，无法进行JOIN，因此触发异常。

解决方案 #

方案1：使用JpaSort.unsafe直接指定JSONB排序表达式 #

直接通过数据库的JSONB语法构建排序规则，绕过默认的属性路径解析：

// 构建JSONB字段的排序规则（PostgreSQL语法）
// 若需按日期类型排序，需将JSON字符串转为日期
Sort sort = JpaSort.unsafe(Sort.Direction.DESC, 
    "to_timestamp(publication_schedule->>'deadlineDate', 'yyyy-MM-dd HH:mm:ss')");

// 构建带自定义排序的PageRequest
PageRequest pageRequest = PageRequest.of(pageNum, pageSize, sort);

// 执行查询
Page<Publication> searchResults = publicationRepository.findAll(specification, pageRequest);

方案2：在Specification中手动添加排序逻辑 #

通过CriteriaBuilder调用数据库函数提取JSON嵌套属性，手动构建排序条件：

Specification<Publication> specification = (root, query, cb) -> {
    // 提取JSONB中的deadlineDate并转为日期类型，添加排序
    Expression<Date> deadlineDateExpr = cb.function(
        "to_timestamp", 
        Date.class, 
        cb.function("jsonb_extract_path_text", String.class, root.get("publicationSchedule"), cb.literal("deadlineDate")),
        cb.literal("yyyy-MM-dd HH:mm:ss")
    );
    query.orderBy(cb.desc(deadlineDateExpr));
    
    // 原有查询条件逻辑
    return null;
};

// 此处PageRequest无需携带排序
PageRequest pageRequest = PageRequest.of(pageNum, pageSize);
Page<Publication> searchResults = publicationRepository.findAll(specification, pageRequest);

说明 #

• 两种方案均针对PostgreSQL的JSONB语法，若使用其他支持JSON的数据库，需调整对应的函数或路径表达式。
• 方案1更简洁，适合快速实现；方案2更符合Criteria API的面向对象风格，便于扩展复杂查询逻辑。

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