CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyjsonb
SivolcC
SivolcC

Reputation: 3608

SQLAlchemy has_any()

I cannot get the SQLAlchemy's has_any() function to work. I simply want to filter my result on a list contained in a jsonb column:

db.session.query(models.Record.id).\
    filter(models.Record.record_metadata["teams"].has_any(team_ids))

models.Record.record_metadata["teams"] being a list of ids in a jsonb column (record_metadata) that i want to compare with team_ids that is also a list of ids

The query generated is :

SELECT record.id AS record_id
FROM record
WHERE ((record.record_metadata -> 'teams')) ?| '["id1", "id2"]';

That gives the error

DataError: (psycopg2.DataError) malformed array literal: "["id1", "id2"]"
LINE 3: WHERE ((record.record_metadata -> 'teams')) ?| '["id1", "id2"]'
                                                       ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.

But regarding Table 9.44. Additional jsonb Operators on Postgres official documentation, the |? operator should be followed by an array[]

Indeed, when i manually write the query : 

SELECT record.id AS record_id
FROM record
WHERE ((record.record_metadata -> 'teams')) ?| array['id1', 'id2'];

It works just fine.

SQLAlchemy's documentation about this function is pretty poor and I can't find any examples.

So is it me doing something wrong with this function? Should I use something else for this case? Or is it has_any() that is broken?

EDIT : 

SELECT record.id AS record_id
FROM record
WHERE ((record.record_metadata -> 'teams')) ?| '{"id1", "id2"}';

Works fine as well, and I found out that has_any() accepts strings as well.

So it s a (very) nasty fix, but if I format a string with the desired ids :

db.session.query(models.Record.id).\
    filter(models.Record.record_metadata["teams"].has_any(
        str(team_ids).
        replace('[', '{').
        replace(']', '}').
        replace('\'', '\"')))

It works...

Upvotes: 3

Views: 2028

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52949

It'd seem that by default the passed value is JSON encoded, if it is a list or a dict. You can work around that by passing an explicit array literal:

In [15]: from sqlalchemy.dialects.postgresql import array

In [16]: session.query(Foo).\
    ...:     filter(Foo.data['test'].has_any(array(['1', '2']))).all()
2018-06-18 09:07:20,780 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-06-18 09:07:20,780 INFO sqlalchemy.engine.base.Engine SELECT foo.id AS foo_id, foo.data AS foo_data 
FROM foo 
WHERE ((foo.data -> %(data_1)s)) ?| ARRAY[%(param_1)s, %(param_2)s]
2018-06-18 09:07:20,781 INFO sqlalchemy.engine.base.Engine {'data_1': 'test', 'param_1': '1', 'param_2': '2'}
Out[16]: []

This way you do not need to manually format the array literal from the string representation of a Python list, which is probably rather error prone.

The error itself is the result of Postgresql trying to parse the passed literal – containing JSON – as an array. As the error states, the text representation of an array may start with a dimension decoration that specifies the array's subscript ranges:

'[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[]

Upvotes: 4

Related Questions