将PostgreSQL JSONB查询转为Spring JPA原生查询遇参数传递问题

问题场景 #

原PostgreSQL查询可正常匹配jsonb列中包含指定引用项的数据：

SELECT * FROM references 
WHERE json_data -> 'References' @> 
any(cast(ARRAY['[{"Id": "001", "Type": "Type-1"}]', '[{"Id": "004", "Type": "Type-4"}]'] as jsonb[]))

对应的jsonb列数据结构：

{
    "CorrelationId": "1234-123-123",
    "References": [
        {
            "Id": "001",
            "Type": "Type-1"
        },
        {
            "Id": "002",
            "Type": "Type-2"
        },
        {
            "Id": "003",
            "Type": "Type-3"
        }
    ]
}

但转换为Spring JPA原生查询后无法正常工作：

@Query(
    value = """
    SELECT * FROM references 
    WHERE json_data -> 'References' @> 
    any(cast(ARRAY[:ids] as jsonb[]))
    """,
    nativeQuery = true
)
fun findAllByReferences(@Param("ids") ids: List<String>)

调用代码：

repository.findAllByReferences("[{\"Id\": \"001\", \"Type\": \"Type-1\"}]", "[{\"Id\": \"004\", \"Type\": \"Type-4\"}]")

问题原因 #

直接使用ARRAY[:ids]时，Spring JPA无法正确将List<String>转换为PostgreSQL的字符串数组，后续的jsonb[]类型转换会出现参数绑定错误。

解决方案 #

方案1：直接传递数组参数并转换类型 #

修改查询方法，使用Array<String>接收参数，直接通过::jsonb[]完成类型转换：

@Query(
    value = """
    SELECT * FROM references 
    WHERE json_data -> 'References' @> ANY(:ids::jsonb[])
    """,
    nativeQuery = true
)
fun findAllByReferences(@Param("ids") ids: Array<String>)

调用示例：

val targetReferences = arrayOf(
    """[{"Id": "001", "Type": "Type-1"}]""",
    """[{"Id": "004", "Type": "Type-4"}]"""
)
repository.findAllByReferences(targetReferences)

方案2：使用jsonb_build_array构造JSON数组 #

如果偏好使用List<String>或可变参数，可借助PostgreSQL的jsonb_build_array函数构造数组：

@Query(
    value = """
    SELECT * FROM references 
    WHERE json_data -> 'References' @> ANY(jsonb_build_array(:ids)::jsonb[])
    """,
    nativeQuery = true
)
fun findAllByReferences(@Param("ids") ids: List<String>)

或使用可变参数简化调用：

@Query(
    value = """
    SELECT * FROM references 
    WHERE json_data -> 'References' @> ANY(jsonb_build_array(?1, ?2)::jsonb[])
    """,
    nativeQuery = true
)
fun findAllByReferences(vararg ids: String)

调用示例：

repository.findAllByReferences(
    """[{"Id": "001", "Type": "Type-1"}]""",
    """[{"Id": "004", "Type": "Type-4"}]"""
)

注意事项 #

• 确保传入的每个字符串都是合法的JSON数组格式（比如[{"Id": "001", "Type": "Type-1"}]），而非单个对象的JSON字符串，否则@>包含运算符无法匹配。
• 若使用Kotlin，字符串中的双引号可通过三重引号(""")避免转义，提升可读性。

内容的提问来源于stack exchange，提问作者user2686282