Postgres中如何利用已索引jsonb字段高效执行IN数组查询？

直接沿用你单值查询时的JSONB比较逻辑，别用jsonb_extract_path_text，就能用上已有的索引，优化后的查询如下：

@Query(
    nativeQuery = true,
    value = """
        select mt.id
         from my_table mt
        where mt.data -> 'indexedId' in (select cast('"' || val || '"' as jsonb) from unnest(:stringValues) val)
    """
)
fun findByIndexedIdIn(@Param("stringValues") stringValues: Collection<String>): List<Long>?

为什么这么改？ #

• 你原来的慢查询是因为jsonb_extract_path_text把JSONB字段转成了普通字符串，而你的索引是基于mt.data -> 'indexedId'这个JSONB类型值创建的，函数转换后索引就无法被查询优化器识别利用了。
• 新查询把集合里的每个字符串都转成JSONB格式（添加双引号再转换类型），然后直接和mt.data -> 'indexedId'做IN匹配，这样查询优化器能直接调用已建的索引提升速度。
• 另外建议把返回类型从Long?改成List<Long>?，毕竟IN查询大概率返回多条结果，单值返回不符合实际场景。

还有一种等价写法，提前把集合转成JSON数组字符串再传入：

@Query(
    nativeQuery = true,
    value = """
        select mt.id
         from my_table mt
        where mt.data -> 'indexedId' = any(cast(:jsonbArray as jsonb))
    """
)
fun findByIndexedIdIn(@Param("jsonbArray") jsonbArray: String): List<Long>?

这种写法需要你在调用方法前，把字符串集合转成类似["id1", "id2", "id3"]的JSON数组字符串，cast(:jsonbArray as jsonb)会转成JSONB数组，= any()会逐个匹配数组元素，同样能用上索引。

内容的提问来源于stack exchange，提问作者João Matos