QueryDSL数组重叠子查询函数
QueryDSL数组重叠子查询函数
提问于 2020-01-28 15:07:13
有一个带有jsonb列的表，其中包含数组 of objects，因此我设计了一个查询，该查询使用至少匹配一个参数的任何元素查找行：
SELECT uuid, data FROM product p
WHERE arrayoverlap(array(SELECT jsonb_array_elements(data->'codes')->>'value'), array['57060279', '57627120']);数据如下所示：
{"name": "Peppermint", "codes": [{"type": "EAN-8", "value": "57627120"}, {"type": "EAN-8", "value": "57060279"}], "number": "000000000000002136"]}
{"name": "AnotherNameForPeppermint", "codes": [{"type": "EAN-8", "value": "57060279"}], "number": "000000000000009571"}是否有一种使用QueryDSL运行这些程序的方法？到目前为止，我已经成功地运行了一些基本函数，这些函数允许我匹配单个值，但是对于数组，我不知道如何匹配。
import com.querydsl.core.types.Expression;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.core.types.dsl.ComparablePath;
import com.querydsl.core.types.dsl.EntityPathBase;
import com.querydsl.core.types.dsl.StringExpression;
import static com.querydsl.core.types.dsl.Expressions.booleanTemplate;
import static com.querydsl.core.types.dsl.Expressions.stringTemplate;
public class QuerydslUtil {
public static BooleanExpression jsonbContains(StringExpression haystack, Expression<String> needle) {
return booleanTemplate("FUNCTION('jsonb_contains', {0}, {1}) = true", haystack, needle);
}
public static StringExpression jsonbExtractPath(Object data, String propertyName) {
return getStringExpression("jsonb_extract_path", data, propertyName);
}
public static StringExpression jsonbExtractPathText(Object data, String propertyName) {
return getStringExpression("jsonb_extract_path_text", data, propertyName);
}
public static StringExpression getStringExpression(String function, Object data, String propertyName) {
return stringTemplate("FUNCTION('" + function + "', {0}, {1})", data, propertyName);
}
}回答 1
Stack Overflow用户
发布于 2020-04-21 09:08:22
找到了一种方法:新的助手方法：
public class QuerydslUtil {
public static BooleanExpression jsonbExistsAny(StringExpression arrayProperty, StringExpression array) {
return booleanTemplate("FUNCTION('jsonb_exists_any', {0}, {1}) = true", arrayProperty, array);
}
public static BooleanExpression jsonbExistsAny(StringExpression arrayProperty, Collection<?> array) {
String string = array.stream().map(Object::toString).collect(joining(","));
return jsonbExistsAny(arrayProperty, stringToArray(string));
}
public static StringExpression stringToArray(String string) {
return stringTemplate("FUNCTION('string_to_array', {0}, {1})", constant(string), constant(","));
}
}然后：
StringExpression codesPath = jsonbExtractPath($.data, "codes");
StringExpression codesValues = jsonbExtractPathText(codesPath, "value");
// executor is a Spring Data QuerydslPredicateExecutor
// codes is a Set<String>
Iterable<Product> products = executor.findAll(jsonbExistsAny(codesValues , codes));页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59951650
复制相关文章
相似问题
腾讯云开发者
