Java使用QueryDSL实现PostgreSQL jsonb字段内部属性条件查询

现有项目上下文 #

项目基于Spring Data JPA + QueryDSL实现持久层操作，PostgreSQL中public.vw_user_site_role_permission视图包含jsonb类型的permission字段，目前普通字段的过滤、分页查询已正常运行，需要新增jsonb字段内部属性的过滤能力。

实体类定义 #

@Table(schema = "public", name = "vw_user_site_role_permission")
@Data
@Entity
@TypeDef(
    name = "json",
    typeClass = JsonType.class
)
public class ViewUserSiteRolePermission extends AuditableEntity{

    @Column(name = "user_site_id")
    private Long userSiteId;

    @Column(name = "user_role_id")
    private Long userRoleId;

    @Column(name = "user_id")
    private Long userId;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "site_id")
    private Long siteId;

    @Column(name = "site_name")
    private String siteName;

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

    @Column(name = "role_id")
    private Long roleId;

    @Column(name = "role_name")
    private String roleName;

    @Column(name = "permission_group_id")
    private Long permissionGroupId;

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

    @Type(type = "json")
    @Column(name = "permission", columnDefinition = "jsonb")
    private JsonNode permission;
}

Repository层定义 #

@Repository
public interface ViewUserSiteRolePermissionRepository extends 
JpaRepository<ViewUserSiteRolePermission, Long>, 
QuerydslPredicateExecutor<ViewUserSiteRolePermission> {
}

已实现的普通字段分页查询逻辑 #

@Override
public Page<ViewUserSiteRolePermission> getALLViewUserSiteRolePermission(List<Long> userIds, 
List<Long> userRoleIds, List<Long> siteIds, Long roleId, String search,String permissions, 
Pageable pageable) {
    BooleanBuilder filter = new BooleanBuilder();

    if (!ObjectUtils.isEmpty(userIds)) {
        filter.and(QViewUserSiteRolePermission.viewUserSiteRolePermission.userId.in(userIds));
    }

    if (!ObjectUtils.isEmpty(userRoleIds)) {
        filter.and(QViewUserSiteRolePermission.viewUserSiteRolePermission.userRoleId.in(userRoleIds));
    }

    if (!ObjectUtils.isEmpty(siteIds)) {
        filter.and(QViewUserSiteRolePermission.viewUserSiteRolePermission.siteId.in(siteIds));
    }

    if (!ObjectUtils.isEmpty(roleId)) {
        filter.and(QViewUserSiteRolePermission.viewUserSiteRolePermission.roleId.eq(roleId));
    }

    if (StringUtils.isNotBlank(search)) {
        BooleanBuilder booleanBuilder = new BooleanBuilder();
        Arrays.asList(search.split(" ")).forEach(nm ->
                booleanBuilder.or(QViewUserSiteRolePermission.viewUserSiteRolePermission.siteName.containsIgnoreCase(nm))
        );
        filter.and(booleanBuilder);
    }

    return viewUserSiteRolePermissionRepository.findAll(filter, pageable);
}

实现方案 #

QueryDSL未内置PostgreSQL jsonb专属操作符的支持，需要通过扩展方言注册原生函数、结合QueryDSL模板表达式拼接条件即可，具体操作如下：

• 自定义PostgreSQL方言，注册jsonb操作对应的原生函数

// 自定义方言类
public class CustomPostgreSQLDialect extends PostgreSQL10Dialect {
    public CustomPostgreSQLDialect() {
        super();
        // 注册jsonb路径取文本函数，对应PostgreSQL原生jsonb_extract_path_text方法，等价于->>操作符
        registerFunction("jsonb_extract_path_text", 
            new StandardSQLFunction("jsonb_extract_path_text", StandardBasicTypes.STRING));
        // 注册jsonb包含判断函数，对应@>操作符，用于判断jsonb是否包含指定json结构
        registerFunction("jsonb_contains", 
            new PostgreSQLAndOperatorFunction("@>", StandardBasicTypes.BOOLEAN));
    }
}

在项目配置中指定Hibernate使用该自定义方言，配置项为spring.jpa.properties.hibernate.dialect，值填写自定义方言类的全限定名。

• 在原有查询逻辑的BooleanBuilder中，通过Expressions.booleanTemplate调用注册的函数，拼接jsonb过滤条件即可，常用场景示例如下：
  1. 精确匹配jsonb指定key的文本值：例如查询permission字段下resourceType属性等于入参permissions值的记录

  if (StringUtils.isNotBlank(permissions)) {
      filter.and(
          Expressions.booleanTemplate(
              "jsonb_extract_path_text({0}, {1}) = {2}",
              QViewUserSiteRolePermission.viewUserSiteRolePermission.permission,
              "resourceType",
              permissions
          )
      );
  }
  

  2. 嵌套路径匹配：例如查询permission下meta.hidden属性为false的记录

  filter.and(
      Expressions.booleanTemplate(
          "jsonb_extract_path_text({0}, {1}, {2}) = {3}",
          QViewUserSiteRolePermission.viewUserSiteRolePermission.permission,
          "meta",
          "hidden",
          "false"
      )
  );
  

  3. jsonb包含查询：例如查询permission中包含{"read": true}结构的记录，适合数组、嵌套对象的包含判断

  filter.and(
      Expressions.booleanTemplate(
          "{0} @> {1}::jsonb",
          QViewUserSiteRolePermission.viewUserSiteRolePermission.permission,
          "{\"read\":true}"
      )
  );
  

注意：如果需要对jsonb内存储的数值、布尔类型做比较，可在模板中对提取出的文本值做显式类型转换，例如判断permission.sort数值大于10，模板内容写为cast(jsonb_extract_path_text({0}, 'sort') as integer) > 10即可。如果需要做模糊匹配，直接对jsonb_extract_path_text返回的字符串套用字符串匹配逻辑，和普通字段用法一致。

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