Supabase中如何查询包含指定标签列表的JSONB数组列？

问题分析 #

你遇到的BadRequestException:invalid input syntax for type json错误，本质是传递给contains函数的参数格式不符合PostgreSQL JSONB的要求。PostgreSQL的JSONB @>（包含）操作符需要接收标准JSON格式的参数，而直接传入Kotlin列表会被转成非JSON格式的字符串（比如[tag]而非["tag"]），导致JSON解析失败。

多标签查询实现 #

根据需求（查询包含所有指定标签的行，或包含任意一个指定标签的行），提供两种实现方案：

方案1：查询包含所有指定标签的行 #

使用PostgreSQL JSONB的@>操作符，确保行的tags数组包含传入的所有标签：

import kotlinx.serialization.json.Json

suspend fun getFormsByTags(tagsToFind: List<String>): List<FormDto> {
    val tagsJson = Json.encodeToString(tagsToFind)
    val result = supabase
        .from(SUPABASE_NAME_KEY)
        .select() {
            filter {
                // 直接使用raw执行SQL操作符，确保参数格式正确
                raw("tags @> ?", tagsJson)
                // 也可以用客户端的contains函数，需传入正确的JSON字符串
                // contains("tags", tagsJson)
            }
        }
    return result.decodeList()
}

方案2：查询包含任意一个指定标签的行 #

使用PostgreSQL的?|操作符，匹配tags数组中存在至少一个指定标签的行：

suspend fun getFormsByTags(tagsToFind: List<String>): List<FormDto> {
    val tagsArrayStr = tagsToFind.joinToString(separator = ", ") { "'$it'" }
    val result = supabase
        .from(SUPABASE_NAME_KEY)
        .select() {
            filter {
                raw("tags ?| array[$tagsArrayStr]")
            }
        }
    return result.decodeList()
}

关键修正点 #

• 参数类型调整：将原函数的tagsToFind: String改为tagsToFind: List<String>，直接接收多个标签，避免手动拆分字符串的麻烦。
• JSON格式规范：确保传递给PostgreSQL的参数是标准JSON字符串（比如["tag1", "tag2"]），通过Json.encodeToString实现Kotlin列表到JSON的转换。
• 操作符选择：根据实际业务需求选择@>（包含所有）或?|（包含任意）操作符。

验证FormDto序列化 #

你的FormDto序列化配置是正确的，@SerialName和List<String>类型可以正确映射PostgreSQL的JSONB数组，无需额外修改：

@Serializable
data class FormDto(
    @SerialName("title")
    val title: String,
    @SerialName("description")
    val description: String,
    @SerialName("tags")
    val tags: List<String>
)

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