将此查询转换为 Spring Data Repository 的正确语法(JPA、Spring Data 或 SpEL)是什么nativeQuery
?
SELECT *
FROM mytable
WHERE f_jsonb_arr_lower(myjsonb -> 'myArray', 'subItem', 'email')
@> '"[email protected] /cdn-cgi/l/email-protection"';
我想使用输入参数而不是硬编码"[email protected] /cdn-cgi/l/email-protection"
.
我的模型:PostgresmyTable
带有 JSONB 列myJsonb
:
{
"myArray": [
{
"subItem": {
"email": "[email protected] /cdn-cgi/l/email-protection"
}
},
{
"subItem": {
"email": "[email protected] /cdn-cgi/l/email-protection"
}
}
]
}
索引描述here https://stackoverflow.com/questions/47822254/create-postgres-jsonb-index-on-array-sub-object.
硬编码版本的工作原理:
@Query(value =
"SELECT m.* " +
" FROM mytable AS m " +
" WHERE f_jsonb_arr_lower(myjsonb -> 'myArray' ,'subItem', 'email') " +
" @> '\"[email protected] /cdn-cgi/l/email-protection\"' " +
" ORDER BY ?#{#pageable} ",
// Spring Data nativeQueries with Pageable require a separate countQuery:
countQuery =
"SELECT count(m.id) " +
" FROM mytable AS m " +
" WHERE f_jsonb_arr_lower(myjsonb -> 'myArray' ,'subItem', 'email') " +
" @> '\"[email protected] /cdn-cgi/l/email-protection\"' ",
nativeQuery = true)
Page<MyTableEntity> findAllHardcodedPageable(Pageable pageable);
但试图利用lowercaseEmailAddress
Spring 数据存储库中的参数 nativeQuery 不起作用:
@Query(value =
"SELECT m.* " +
" FROM mytable AS m " +
" WHERE f_jsonb_arr_lower(myjsonb -> 'myArray' ,'subItem', 'email') " +
" @> '\"?{lowercaseEmailAddress}\"' " +
" ORDER BY ?#{#pageable} ",
countQuery =
"SELECT count(m.id) " +
" FROM mytable AS m " +
" WHERE f_jsonb_arr_lower(myjsonb -> 'myArray' ,'subItem', 'email') " +
" @> '\"?{lowercaseEmailAddress}\"' ",
nativeQuery = true)
Page<MyTableEntity> findAllByEmailPageable
(String lowercaseEmailAddress, Pageable pageable);
在我的 Postgres 查询日志记录中,我可以看到lowercaseEmailAddress
参数从未设置:
LOG: execute S_2: COMMIT
LOG: execute S_3: BEGIN
LOG: execute <unnamed>: SELECT count(m.id) FROM mytable
AS m WHERE f_jsonb_arr_lower(myjsonb -> 'myArray',
'subitem', 'email') @> '"?1"'
LOG: execute S_11: ROLLBACK