SQLAlchemy IN_查询含前导零字符串报错问题求助

使用SQLAlchemy（版本^1.4.36）编写查询时，使用了如下过滤子句：

.filter( some_model.some_field[2].in_(['item1', 'item2']) )

其中some_field是PostgreSQL的jsonb类型字段，数据库中该字段的存储格式示例：

• ["something","something","123"]
• ["something","something","0123"]
some_field[2]对应的始终是仅包含数字的字符串（带双引号），有时带前导零，有时不带。

当in_子句传入无前导零的字符串（如['123', '345']）时查询正常，但传入含前导零的字符串（如['0123', '0345']）时会触发报错，错误信息如下：

cursor.execute(statement, parameters)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type json
LINE 3: ...d_on) = 2 AND (app_cache.value_metadata -> 2) IN ('0123'
^
DETAIL: Token "0123" is invalid.

目前已改用Python代码在查询后对结果进行过滤，想明确为何含前导零的字符串在IN_子句中会报错。

原因分析 #

这是PostgreSQL处理jsonb类型的规则导致的：

• 用->操作符提取jsonb数组元素时，返回的是JSON类型的值，而非普通字符串。
• 在IN子句中，PostgreSQL会尝试将你传入的'0123'解析为JSON值，但JSON规范中数字类型不允许存在前导零（除了单独的0），因此0123会被判定为无效的JSON Token，触发语法错误。
• 无前导零的123能被正常解析为JSON数字，和字段中存储的"123"（JSON字符串）比较时，PostgreSQL会做隐式类型转换，因此可以匹配成功。

可选解决方案 #

如果希望回到数据库层面完成过滤，有两种可行方案：

• 方案一：使用->>操作符（或SQLAlchemy的.astext属性）提取文本字符串，让IN子句按普通字符串规则比较：

  # 使用astext属性的写法
  .filter( some_model.some_field[2].astext.in_(['0123', '0345']) )
  
  # 等价的op写法
  .filter( some_model.some_field.op('->>')(2).in_(['0123', '0345']) )
  

• 方案二：将IN子句中的值包裹为JSON字符串格式，让PostgreSQL解析为合法的JSON字符串值：

  .filter( some_model.some_field[2].in_(['"0123"', '"0345"']) )
  

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