我有一个Postgres SQL查询,它将在jsonb type列内定义的键的值数组中执行搜索操作.
SELECT o.id
FROM customer c INNER JOIN order o
on c.id = o.c_id where EXISTS (
SELECT 1
FROM jsonb_array_elements_text(c.customer_data->'sid') AS value
WHERE value = '3456' OR value='89110'
);
在这里,sid是我的键,它有一组值.给定的查询将在值数组中执行深度搜索,如果键与这些值中的任何一个匹配,则返回记录.
我希望使用Criteria Builder和Criteria Query在Spring data JPA中生成等效项,以返回相同的结果.对此有什么建议会有帮助吗?
我的两个实体都是一对多关系的连接,我正在努力实现上面的查询结果:
public List<Long> findCustomerIds(EntityManager entityManager,String key, List<String> sidValues) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<Customer> cRoot = cq.from(Customer.class);
// Join between Customer and Order
Join<Customer, Order> oJoin = cRoot.join("orders");
// Subquery to handle JSONB array elements condition
Subquery<String> subquery = cq.subquery(String.class);
Root<Customer> subRoot = subquery.from(Customer.class);
Path<Object> subCustomerDataPath = subRoot.get("customerData");
Path<String> subSidPath = cb.function("jsonb_array_elements_text", String.class, subCustomerDataPath.get(key));
subquery.select(subRoot.get("id"))
.where(cb.or(
sidValues.stream()
.map(sid -> cb.equal(cb.upper(subSidPath), sid.toUpperCase()))
.toArray(Predicate[]::new)
));
cq.select(cRoot.get("id"))
.where(cb.equal(oJoin.get("customer"), cRoot), cb.exists(subquery));
return entityManager.createQuery(cq).getResultList();
}
该问题存在于给定的代码部分中:
// Subquery to handle JSONB array elements condition
Subquery<String> subquery = cq.subquery(String.class);
Root<Customer> subRoot = subquery.from(Customer.class);
Path<Object> subCustomerDataPath = subRoot.get("customerData");
Path<String> subSidPath = cb.function("jsonb_array_elements_text", String.class, subCustomerDataPath.get(key));
subquery.select(subRoot.get("id"))
.where(cb.or(
sidValues.stream()
.map(sid -> cb.equal(cb.upper(subSidPath), sid.toUpperCase()))
.toArray(Predicate[]::new)
));
我在子查询部分得到了Illegal attempt to dereference path source [null.customerData]个错误.我不知道我的代码中有什么错误.
作为参考,我在Entity中以以下方式定义了jsonb字段,因为所有键和值都以以下方式存储:
@Type(type = "jsonb")
@Column(name = "customer_data",columnDefinition = "jsonb")
private Map<String,List<String>> customerData = new HashMap<>();
如果你对此有任何替代的方法或建议,将是有帮助的.首先要感谢您的帮助.