postgresql 如何使用 spring data jpa 查询 jsonb 列？

How do I use spring data jpa to query jsonb column?

提问by Josh C.

I'm having a problem getting this native query right against a postgres 9.4 instance.

我在针对 postgres 9.4 实例正确获取此本机查询时遇到问题。

My repository has a method:

我的存储库有一个方法：

 @Query(value = "SELECT t.* " +
            "FROM my_table t " +
            "WHERE t.field_1 = ?1 " +
            "AND t.field_2 = 1 " +
            "AND t.field_3 IN ?2 " +
            "AND t.jsonb_field #>> '{key,subkey}' = ?3",
            nativeQuery = true)
    List<Entity> getEntities(String field1Value,
                                   Collection<Integer> field3Values,
                                   String jsonbFieldValue);

But the logs show this:

但日志显示：

SELECT t.* FROM my_table t 
WHERE t.field_1 = ?1 
  AND t.field_2 = 1 
  AND t.field_3 IN ?2 
  AND t.jsonb_field ? '{key,subkey}' = ?3

And I get this exception:

我得到这个例外：

Internal Exception: org.postgresql.util.PSQLException: No value specified for parameter 2.

内部异常：org.postgresql.util.PSQLException：没有为参数 2 指定值。

I logged the parameters directly before method invocation, and they are all supplied.

我在方法调用之前直接记录了参数，它们都是提供的。

I'm not sure why #>>shows ?in the log. Do I need to escape #>>? Do I need to format the collection for IN? Do I need to escape the json path?

我不确定为什么会在日志中#>>显示?。我需要逃跑#>>吗？我需要格式化集合IN吗？我需要转义 json 路径吗？

When I execute the query directly against the db, it works. Example:

当我直接对数据库执行查询时，它可以工作。例子：

SELECT *
FROM my_table t
WHERE t.field_1 = 'xxxx'
  AND t.field_2 = 1
  AND t.field_3 IN (13)
  AND t.jsonb_field #>> '{key,subkey}' = 'value'

回答by George Siggouroglou

I found very helpful the Specificationapi from spring data.
Let's say we have an entity with name Productand a property with name titleof type JSON(B).
I assume that this property contains the title of the Product in different languages. An example could be: {"EN":"Multicolor LED light", "EL":"Πολ?χρωμο LED φ??"}.
The source code below finds a (or more in case it is not a unique field) product by title and locale passed as arguments.

我发现来自 spring 数据的规范api非常有用。
假设我们有一个Product名称title为 JSON(B)的实体和一个名称为 JSON(B)的属性。
我假设此属性包含不同语言的产品标题。一个例子可能是：{"EN":"Multicolor LED light", "EL":"Πολ?χρωμο LED φ??"}。
下面的源代码通过作为参数传递的标题和语言环境找到一个（或更多，以防它不是唯一字段）产品。

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product> {
}


public class ProductSpecification implements Specification<Product> {

    private String locale;
    private String titleToSearch;

    public ProductSpecification(String locale, String titleToSearch) {
        this.locale = locale;
        this.titleToSearch = titleToSearch;
    }

    @Override
    public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.<String>get("title"), builder.literal(this.locale)), this.titleToSearch);
    }
}


@Service
public class ProductService {

    @Autowired
    private ProductRepository productRepository;

    public List<Product> findByTitle(String locale, String titleToSearch) {
        ProductSpecification cs = new ProductSpecification(locale, titleToSearch);
        return productRepository.find(cs);
        // Or using lambda expression - without the need of ProductSpecification class.
//      return productRepository.find((Root<ProductCategory> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
//          return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.<String>get("title"), builder.literal(locale)), titleToSearch);
//      });
    }
}

You can find another answer about the way you should use the Spring Data here.
Hope that helps.

您可以在此处找到有关使用 Spring Data 的方式的另一个答案。
希望有帮助。

回答by Arman Tumanyan

Maybe this is an old topic, but I'm putting here search in jsonb by field using spring specification.

也许这是一个老话题，但我将使用 spring 规范按字段在 jsonb 中搜索。

If you want to search with "LIKE" you need to create like disjunction with the following code:

如果要使用“LIKE”进行搜索，则需要使用以下代码创建类似析取：

final Predicate likeSearch = cb.disjunction();

After that, let's assume u have jsonb field in your object which is address, and address has 5 fields. To search in all these fields you need to add "LIKE" expression for all fields:

之后，让我们假设您的对象中有 jsonb 字段，即地址，地址有 5 个字段。要在所有这些字段中搜索，您需要为所有字段添加“LIKE”表达式：

for (String field : ADDRESS_SEARCH_FIELDS) {
                likeSearch.getExpressions().add(cb.like(cb.lower(cb.function("json_extract_path_text", String.class,
                        root.get("address"), cb.literal(field))), %searchKey%));
            }

Where cb is the same criteriaBuilder. %searchKey% is what you want to search in address fields.

其中 cb 是相同的criteriaBuilder。%searchKey% 是您要在地址字段中搜索的内容。

Hope this helps.

希望这可以帮助。

回答by coladict

If the operator is being converted to a question mark for one reason or another, then you should try using the function instead. You can find the corresponding function using \doS+ #>>in the psql console. It tells us the function called is jsonb_extract_path_text. This would make your query:

如果由于某种原因将运算符转换为问号，那么您应该尝试使用该函数。您可以\doS+ #>>在 psql 控制台中找到相应的函数 using 。它告诉我们调用的函数是jsonb_extract_path_text。这将使您的查询：

@Query(value = "SELECT t.* " +
        "FROM my_table t " +
        "WHERE t.field_1 = ?1 " +
        "AND t.field_2 = 1 " +
        "AND t.field_3 IN ?2 " +
        "AND jsonb_extract_path_text(t.jsonb_field, '{key,subkey}') = ?3",
        nativeQuery = true)

回答by George Siggouroglou

You can also use the FUCTJPQL keywork for calling custom functions and not use a native query.
Something like this,

您还可以使用FUCTJPQL 关键字来调用自定义函数，而不是使用本机查询。
像这样的东西，

@Query(value = "SELECT t FROM my_table t "
        + "WHERE t.field_1=:field_1 AND t.field_2=1 AND t.field_3 IN :field_3 "
        + "AND FUNC('jsonb_extract_path_text', 'key', 'subkey')=:value")
List<Entity> getEntities(@Param("field_1") String field_1, @Param("field_3") Collection<Integer> field_3, @Param("value") String value);

回答by kensai

I suggest not following this way, I prefer to follow generic CRUD way (also working on advanced auto generated DAO methods in way of StrongLoop Loopback does, for Spring Data Rest maven plugin, but it is experimental in the moment only). But with this JSON, now what to do... I am looking for something similar to MongoDB JSON processing in Spring Data via @Document annotation, however this is not yet available. But there are other ways :-)

我建议不要遵循这种方式，我更喜欢遵循通用的 CRUD 方式（对于 Spring Data Rest maven 插件，也以 StrongLoop Loopback 的方式处理高级自动生成的 DAO 方法，但目前仅处于实验阶段）。但是有了这个 JSON，现在该怎么办……我正在通过 @Document 注释在 Spring Data 中寻找类似于 MongoDB JSON 处理的东西，但是这还不可用。但还有其他方法:-)

In general it is about implementing your JSON user type (UserType interface):

一般来说，它是关于实现你的 JSON 用户类型（UserType 接口）：

public class YourJSONBType implements UserType {

Finally you need to enhance your JPA classes with specification of your implemented user type:

最后，您需要使用实现的用户类型规范来增强 JPA 类：

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@TypeDef(name = "JsonbType", typeClass = YourJSONBType.class)
public class Person {
    @Id
    @GeneratedValue
    private Long id;

    @Column(columnDefinition = "jsonb")
    @Type(type = "JsonbType")
    private Map<String,Object> info;
}

look at another related articles here: Mapping PostgreSQL JSON column to Hibernate value type

在此处查看另一篇相关文章：将 PostgreSQL JSON 列映射到 Hibernate 值类型

The full implementation example is available here:

完整的实现示例可在此处获得：

• https://github.com/nzhong/spring-data-jpa-postgresql-json
• https://github.com/mariusneo/postgres-json-jpa

• https://github.com/nzhong/spring-data-jpa-postgresql-json
• https://github.com/mariusneo/postgres-json-jpa

Similar, but little different example is available here: http://www.wisely.top/2017/06/27/spring-data-jpa-postgresql-jsonb/?d=1

类似但几乎没有不同的例子在这里可用：http: //www.wisely.top/2017/06/27/spring-data-jpa-postgresql-jsonb/?d=1