关于Spring Data jpa 如何实现jsonb 的条件查询

一、背景

需求类似于要存储key value类型的数据。也就是对应json格式。在先天性不允许更换或者是拓展nosql数据库时。在存储不确定的列值时，我们往往会显得很惆怅。业界常用的手段可以是转行为列。多表映射等，但是这些手段无非就是通过增加冗余，来实现需求，有时这样子做先不谈效率什么的，很多程序员都会用猥琐一词来修饰。当然如果可以使用Mongdb，ES这些非关系型数据库是最好的，但是往往现实中会出现某位程序员的话语权并没有那么高，又或者是快才是某个产品的特色这种情况。那我们该怎么办呢？猥琐都是猥琐的，在大环境下面，我们要做的是追求不那么猥琐。存储json。
现在关系型数据库里面，我所知道的Mysql实际上是可以存储json的（我没有用过）。甚至redis好像也放大招对json格式有所支持吗，听说效能上还不错。而我项目使用的是**Postgresql,**
Postgresql是对json有所支持的，其对应的数据库中的两种类型Json和Jsonb,关于Json和jsonb的区别网上有很多博客介绍的，这不是本文的核心部分。项目中所使用的Orm框架是Spring Data Jpa。

1.1、要点

对于存储json格式实际上并不难。也不是本次讨论的重点。关于Spring Data Jpa如何整合映射jsonb类型网上亦有很多介绍。本文主要是介绍如何使用Spring Data Jpa 实现对 Jsonb类型的查询。

二、Postgresql对于jsonb上的查询。

2.1、方案.

在经过对应的查询官网资料，以及各方百度。找到两种方式。如图。

• 方式一使用@>操作符

官网截图

看起来像是在json列中判断键值对是否存在

• 方法二 通过对应的函数获取对应的value值，再进行业务逻辑判断。

官网截图

实际操作

实际上就是通过函数将对应的value以text形式拿出来然后进行业务操作。

2.2、分析

两种方式从看法上而言是有着本质上的区别（ Postgresql底层优化是否是这样我还没有研究过。后续有时间研究再更新文章）。关于第一种使用**@>**如果我们使用的是1 Mybatis框架实际上并没有什么难点。当然使用Spring Jpa在repository中定义sql也是可以实现的（jpa实际上并不希望你这样子做）。但是对于多条件，的复杂条件又或者在已经写好的查询接口中拓展jsonb的查询，显然在上述定义sql的方式并不友好。而我们普遍使用的jpa底层采用的是hibernate。
以下只是我的猜测，由于jpa想要的是极致化的orm效果，这也是他不推荐手写sql的原因之一，而@>操作符实际上只存在于特定的 关系型数据库，jpa要想追求极致化orm，就不会提供开发者自定义比较操作符的缺口，从他的业务设计上来说，也不允许提供。从这点上来说jpa确实没有mybatis灵活，但是这也不能说是jpa的缺点。以上都是我的意淫，如果有读者发现jpa提供了自定义比较操作符的方式。可以在评论里面发出来，大家进行深入交流。

从这个角度分析，我们只能从二种方式入手，也就是通过函数的方式。

三、实现

3.1、Spring data jpa

在实际开发中，我们往往会引入工具库来配合Spring Data jpa。这里针对业界比较流行的两种进行实现。**com.github.wenhao**， 和**querydls，**以下是两种的Maven依赖

<dependency>
  <groupId>com.github.wenhao</groupId>
  <artifactId>jpa-spec</artifactId>
</dependency>

<dependency>
   <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
</dependency>
 <dependency>
     <groupId>com.querydsl</groupId>
     <artifactId>querydsl-apt</artifactId>
 </dependency>

(1) wenhao

这个工具类只不过是简化了Spring data Jpa 中自带的Predicate的用法而已，实际上如果你熟悉jpa的话，自带的也可以实现。

package com.hcmsp.bill.architecture.repository.customize;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.util.ArrayUtil;
import org.springframework.data.jpa.domain.Specification;

import javax.annotation.Nullable;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author 罗富晓 [295006967@qq.com]
 * @date 2022/8/17 15:24
 */
public class JsonbMapAndSpecification<T> implements Specification<T> {

    private Map<String, String> targetMap;

    /**
     * 应用场景 相同一个key对应多个value 多个value为Or的关系。 不同key为and的关系
     */
    private Map<String, List<String>> combinationMap;

    private String jsonKey;
    
    public JsonbMapAndSpecification() {

    }

    public JsonbMapAndSpecification(String jsonKey, Map<String, String> targetMap) {
        this.targetMap = targetMap;
        this.jsonKey = jsonKey;
    }

    public JsonbMapAndSpecification(Map<String, String> targetMap, Map<String, List<String>> combinationMap, String jsonKey) {
        this.targetMap = targetMap;
        this.combinationMap = combinationMap;
        this.jsonKey = jsonKey;
    }

    private JsonbMapAndSpecification(Builder<T> builder) {
        targetMap = builder.targetMap;
        combinationMap = builder.combinationMap;
        jsonKey = builder.jsonKey;
    }
    
    @Override
    public Predicate toPredicate(@Nullable Root<T> root, @Nullable CriteriaQuery<?> query, @Nullable CriteriaBuilder criteriaBuilder) {
        assert criteriaBuilder != null;
        assert root != null;
        assert query != null;
        Map<String, List<String>> simpleCombinationValueOrPredicate = new HashMap<>(0);
        if(CollectionUtil.isNotEmpty(this.targetMap)) {
            this.targetMap.keySet().forEach(key -> simpleCombinationValueOrPredicate.put(key, ListUtil.toList(this.targetMap.get(key))));
        }
        List<Predicate> simpleCombinationPredicateList = this.getCombinationPredicate(root, criteriaBuilder, simpleCombinationValueOrPredicate);
        List<Predicate> combinationValueOrPredicate = this.getCombinationPredicate(root, criteriaBuilder, this.combinationMap);
        return criteriaBuilder.and(ArrayUtil.toArray(CollectionUtil.addAll(simpleCombinationPredicateList, combinationValueOrPredicate), Predicate.class));
    }

    private List<Predicate> getCombinationPredicate(Root<T> root, CriteriaBuilder criteriaBuilder, Map<String, List<String>> combinationMap) {
        if(CollectionUtil.isEmpty(combinationMap)) {
            return new ArrayList<>();
        }
        return this.combinationMap.keySet()
                .stream()
                .filter(key -> CollectionUtil.isNotEmpty(combinationMap.get(key)))
                .map(key -> {
                    List<String> valueList = combinationMap.get(key);
                    List<Predicate> valuePredicateList = valueList.stream().distinct()
                            .map(value -> criteriaBuilder
                                    .equal(criteriaBuilder.function("jsonb_extract_path_text", String.class, root.get(jsonKey),
                                            criteriaBuilder.literal(key)), value))
                            .collect(Collectors.toList());
                    return criteriaBuilder.or(ArrayUtil.toArray(valuePredicateList, Predicate.class));
                }).collect(Collectors.toList());
    }

    public static final class Builder<T> {
        private Map<String, String> targetMap;
        private Map<String, List<String>> combinationMap;
        private String jsonKey;

        public Builder() {
        }

        public Builder<T> targetMap(@Nullable Map<String, String> val) {
            targetMap = val;
            return this;
        }

        public Builder<T> combinationMap(@Nullable Map<String, List<String>> val) {
            combinationMap = val;
            return this;
        }

        public Builder<T> jsonKey(String val) {
            jsonKey = val;
            return this;
        }

        public JsonbMapAndSpecification<T> build() {
            return new JsonbMapAndSpecification<>(this);
        }
    }
}

上面只是做了一个简单的实现。实际上。如果我们对Json查询的应用场景很多的话。可以针对于对应的操作，以及场景进行业务拆分，或者为了迎合Specifications，新增对应的创建者模式。都是可以的。这里只是做一个简单的实现，入参为字段实体字段，**Map**集合。**value**层面做**or**查询** key**层面做**and**** 查询。**

• 实际使用。

awsResourceMonthAnalyseBillRepository.findAll(
                Specifications.<AwsResourceMonthAnalyseBill>and()
                        .predicate(new JsonbMapAndSpecification("jsonProperties", new HashMap<>()))
                        .build()
        );

(2) querydls

关于querydls如何使用并不是本文讨论的重点。有需求的可以去百度一下。
其中对于querydls的where使用如下代码

SQLQuery<Tuple> queryByBillDate = sqlQueryFactory.select(billDate, itemUnBlendedCostSum)
                .from(table)
                .where(predicate)
                .groupBy(billDate)
                .orderBy(billDate.asc());

注意where中的predicate和上述的Specifications的部署同一个东西。如下图所示，这是querydls自带的predicate。因此我们可以采用针对他使用创建对应的工具类。

• 针对于上面的Specifications的Map入参我也写了个简易的工具类。

package com.hcmsp.bill.util;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.querydsl.core.types.ExpressionUtils;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.core.types.dsl.StringPath;
import com.querydsl.core.types.dsl.StringTemplate;
import lombok.experimental.UtilityClass;

import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author 罗富晓 [295006967@qq.com]
 * @date 2022/8/18 14:32
 */
@UtilityClass
public class MyQslUtil {

    private final String LIKE_TEMPLATE = "%{}%";

    public Predicate getJsonPredicate(StringPath propertyStringPath, String key, String value) {
        StringTemplate stringTemplate = Expressions.stringTemplate("jsonb_extract_path_text({0}, {1})", propertyStringPath, key);
        return stringTemplate.like(StrUtil.format(LIKE_TEMPLATE, value));
    }

    public Predicate getJsonPredicate(StringPath propertyStringPath, Map<String, String> jsonMap) {
        List<Predicate> allPredicateList = jsonMap.keySet().stream()
                .map(key -> getJsonPredicate(propertyStringPath, key, jsonMap.get(key))).collect(Collectors.toList());
        return ExpressionUtils.allOf(allPredicateList);
    }

    /**
     * value层面or查询 key层面 and 查询
     * @param propertyStringPath 入参
     * @param jsonMap 参数
     * @return predicate
     */
    public Predicate getCombinationJsonPredicate(StringPath propertyStringPath, Map<String, List<String>> jsonMap) {
        List<Predicate> predicateList = jsonMap.keySet().stream().filter(key -> CollectionUtil.isNotEmpty(jsonMap.get(key))).map(key -> {
            List<String> valueList = jsonMap.get(key);
            List<Predicate> orExpressionList = valueList.stream().map(value -> {
                StringTemplate stringTemplate = Expressions.stringTemplate("jsonb_extract_path_text({0}, {1})", propertyStringPath, key);
                return stringTemplate.eq(value);
            }).collect(Collectors.toList());
            return ExpressionUtils.anyOf(orExpressionList);
        }).collect(Collectors.toList());
        return ExpressionUtils.allOf(predicateList);
    }
}

• 时间使用如下

SQLQuery<Tuple> queryByBillDate = sqlQueryFactory.select(billDate, itemUnBlendedCostSum)
                .from(table)
                .where(MyQslUtil.getCombinationJsonPredicate(QAwsCommonBillConstant.resourceTags, resourceTagQueryMap)))
                .groupBy(billDate)
                .orderBy(billDate.asc());

四、结语

实际上通过jpa实现对应的jsonb的查询我们已经结束了。通过对应的思想，以及参考对应postgresql。我们可以整合出更多对应的函数+jpa的使用的花样。不仅仅局限于普通的key-value的json格式。还可以针对对应层级更深的json格式。此外。postgresql还支持其他一些如ip,坐标等类型，通过对应的思想实际上都是可以实现的。