Spring Data JPA执行PostgreSQL JSONB查询报错：操作符不存在

在PGAdmin中执行包含jsonb @>操作符的查询可以正常运行，但使用Spring Data JPA原生查询时，无论传入List<String>还是JSON格式字符串，都会抛出如下异常：

ERROR: operator does not exist: jsonb @> character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts

问题原因 #

PostgreSQL的@>（jsonb包含）操作符要求左右两边的操作数均为jsonb类型，但Spring Data JPA默认会将：

• List<String>参数转换为SQL数组类型（而非jsonb数组）
• JSON格式字符串参数转换为varchar类型

类型不匹配直接触发了上述报错。

解决方案 #

根据传入参数的类型，有两种可行的修改方式：

1. 传入JSON格式字符串时：显式转换参数为jsonb

直接在查询语句中用PostgreSQL的::jsonb语法对参数进行类型强制转换：

修改后的原生查询代码：

@Query(value = "select org.* from organic org " +
        "where (org.data ->> 'bfId') = :bfId " +
        "and ((org.data -> 'attributes') ->> 'orgType') = :orgType " +
        "and (org.data ->> 'instId') = :instId " +
        "and (org.data -> 'accs') @> :accsIds::jsonb " +
        "and ((org.data -> 'attributes') -> 'reg') @> :reg::jsonb " +
        "and ((org.data -> 'attributes') -> 'desk') @> :desk::jsonb " +
        "and ((org.data -> 'attributes') -> 'country') @> :countaries::jsonb", nativeQuery = true)
List<Organic> getData(String bfId, String orgType, String instId, 
                      String accsIds, String reg, String desk, String countaries);

调用时传入标准JSON数组字符串（如"[\"accs_id1\"]"）即可。

2. 传入List时：将SQL数组转换为jsonb数组

Spring会把List<String>转为SQL数组，我们可以用PostgreSQL的array_to_json()函数将其转为JSON数组，再强制转为jsonb：

修改后的原生查询代码：

@Query(value = "select org.* from organic org " +
        "where (org.data ->> 'bfId') = :bfId " +
        "and ((org.data -> 'attributes') ->> 'orgType') = :orgType " +
        "and (org.data ->> 'instId') = :instId " +
        "and (org.data -> 'accs') @> array_to_json(:accsIds)::jsonb " +
        "and ((org.data -> 'attributes') -> 'reg') @> array_to_json(:reg)::jsonb " +
        "and ((org.data -> 'attributes') -> 'desk') @> array_to_json(:desk)::jsonb " +
        "and ((org.data -> 'attributes') -> 'country') @> array_to_json(:countaries)::jsonb", nativeQuery = true)
List<Organic> getData(String bfId, String orgType, String instId, 
                      List<String> accsIds, List<String> reg, List<String> desk, List<String> countaries);

这种方式可以直接传入List<String>（如Arrays.asList("accs_id1")），无需手动拼接JSON字符串。

备选方案：使用jsonb_contains函数

也可以用PostgreSQL内置的jsonb_contains函数替代@>操作符，语义更清晰，同样需要保证参数为jsonb类型：

@Query(value = "select org.* from organic org " +
        "where (org.data ->> 'bfId') = :bfId " +
        "and ((org.data -> 'attributes') ->> 'orgType') = :orgType " +
        "and (org.data ->> 'instId') = :instId " +
        "and jsonb_contains(org.data -> 'accs', array_to_json(:accsIds)::jsonb) " +
        "and jsonb_contains((org.data -> 'attributes') -> 'reg', array_to_json(:reg)::jsonb) " +
        "and jsonb_contains((org.data -> 'attributes') -> 'desk', array_to_json(:desk)::jsonb) " +
        "and jsonb_contains((org.data -> 'attributes') -> 'country', array_to_json(:countaries)::jsonb)", nativeQuery = true)
List<Organic> getData(String bfId, String orgType, String instId, 
                      List<String> accsIds, List<String> reg, List<String> desk, List<String> countaries);

验证 #

修改后执行查询，Spring Data JPA会正确将参数转换为jsonb类型，匹配@>操作符的要求，即可正常返回结果。

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