0

In my sqlalchemy ( sqlalchemy = "^1.4.36" ) query I have a clause:

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

where some_field is jsonb and the value in some_field value in the db formatted like this: ["something","something","123"] or ["something","something","0123"]

note: some_field[2] is always digits-only double-quoted string, sometimes with leading zeroes and sometimes without them.

The query works fine for cases like this:

.filter( some_model.some_field[2].in_(['123', '345']) )

and fails when the values in the in_ clause have leading zeroes:

e.g. .filter( some_model.some_field[2].in_(['0123', '0345']) ) fails.

The error it gives:

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

Again, in the case of '123' (or any string of digits without leading zero) instead of '0123' the error is not thrown.

What is wrong with having leading zeroes for the strings in the list of in_ clause? Thanks.

UPDATE: basically, sqlachemy's IN_ assumes int input and fails accordingly. There must be some reasoning behind this behavior, can't tell what it is. I removed that filter fromm the query and did the filtering of the ouput in python code afterwards.

SwissNavy
  • 619
  • 1
  • 12
  • 28

1 Answers1

0

The problem here is that the values in the IN clause are being interpreted by PostgreSQL as JSON representations of integers, and an integer with a leading zero is not valid JSON.

The IN clause has a value of type jsonb on the left hand side. The values on the right hand side are not explicitly typed, so Postgres tries to find the best match that will allow them to be compared with a jsonb value. This type is jsonb, so Postgres attempts to cast the values to jsonb. This works for values without a leading zero, because digits in single quotes without leading zeroes are valid representations of integers in JSON:

test# select '123'::jsonb;
 jsonb 
═══════
 123
(1 row)

but it doesn't work for values with leading zeroes, because they are not valid JSON:

test# select '0123'::jsonb;
ERROR:  invalid input syntax for type json
LINE 1: select '0123'::jsonb;
               ^
DETAIL:  Token "0123" is invalid.
CONTEXT:  JSON data, line 1: 0123

Assuming that you expect some_field[2].in_(['123', '345']) and some_field[2].in_(['0123', '345']) to match ["something","something","123"] and ["something","something","123"] respectively, you can either serialise the values to JSON yourself:

some_field[2].in_([json.dumps(x) for x in ['0123', '345']])

or use the contained_by operator (<@ in PostgreSQL), to test whether some_field[2] is present in the list of values:

some_field[2].contained_by(['0123', '345'])

or cast some_field[2] to text (that is, use the ->> operator) so that the values are compared as text, not JSON.

some_field[2].astext.in_(['0123', '345'])
snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
  • "The problem here is that the values in the IN clause are being interpreted as integers" - that seems to be the case (all the official docs examples use INT valueseven though they don't mention that other types are not welcome), but why IN_ assumes the list is made of INT? Maybe there is a way to tell it "take them as they are, no casting"? In the code they are str. – SwissNavy Feb 16 '23 at 08:41
  • I think the key is to consider that the values are interpreted as _JSON_, not integers or strings. I've amended the answer to clarify (let me know if it's still unclear). And I've added `.astext` as another way of querying, which is probably conceptually closest to what you want to do. – snakecharmerb Feb 16 '23 at 09:50
  • `some_field[2].contained_by(['0123', '345'])` gives again ` Token \"04264980014\" is invalid`. `some_field[2].astext.in_(['0123', '345'])` gives `Unhandled Exception ArgumentError IN expression list, SELECT construct, or bound parameter object expected, got '04264980014'.` I ended up filtering from python code, but thanks! – SwissNavy Feb 20 '23 at 16:02