Cannot Query Array of Integers in Postgres

Question

Migrated column type from HSTORE to JSONB and am using this snippet of code...

from sqlalchemy.dialects.postgresql import ARRAY, JSONB

if employment_type:
        base = base.filter(Candidate.bio["employment_type"].cast(ARRAY).contains(employment_type))

and am getting this error...

127.0.0.1 - - [28/Mar/2016 12:25:13] "GET /candidate_filter/?employment_type_3=true HTTP/1.1" 500 -
Traceback (most recent call last):
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/surajkapoor/Desktop/lhv-talenttracker/app/views.py", line 660, in investor_filter
    base = base.filter(Candidate.bio["employment_type"].cast(ARRAY).contains(employment_type))
  File "/Library/Python/2.7/site-packages/sqlalchemy/dialects/postgresql/json.py", line 93, in cast
    return self.astext.cast(type_)
  File "/Library/Python/2.7/site-packages/sqlalchemy/dialects/postgresql/json.py", line 95, in cast
    return sql.cast(self, type_)
  File "<string>", line 2, in cast

  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/elements.py", line 2314, in __init__
    self.type = type_api.to_instance(type_)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/type_api.py", line 1142, in to_instance
    return typeobj(*arg, **kw)
TypeError: __init__() takes at least 2 arguments (1 given)

Candidate.bio["employment_type"] is an array of integers and I'm simply trying to query all the rows that contain a specific integer in them.

Also, .cast() works perfectly on the same column when calling Integer...

if internship:
    base = base.filter(Candidate.bio["internship"].cast(Integer) == 1)

Did you try without the cast i.e. Candidate.bio["employment_type"].contains(employment_type) ? From the docs, contains should work for arrays too - "Test if keys (or array) are a superset of/contained the keys of the argument jsonb expression." — Lyman Zerga
– Lyman Zerga, Commented Mar 30, 2016 at 23:15
@LymanZerga Yup, tried without the cast... I get DataError: (psycopg2.DataError) invalid input syntax for integer: "%" LINE 3: ...!= 0 AND ((candidate.bio -> 'location_work') LIKE '%' + 2 ||... — Suraj Kapoor
– Suraj Kapoor, Commented Mar 31, 2016 at 13:56

Haleemur Ali · Accepted Answer · 2016-03-31 16:47:37Z

1

+50

SqlAlchemy is probably having difficulty constructing the where clause because it can't figure out what type bio->'employment_type' is.

If the contains method is called from a String object, it would generate a LIKE clause, but for JSONB or ARRAY it would need to generate the @> operator.

To give SqlAlchemy the necessary hints, use explicit casting everywhere, i.e. write your query like

from sqlalchemy import cast

if employment_type:
    casted_field = Candidate.bio['employment_type'].cast(JSONB)
    casted_values = cast(employment_type, JSONB)
    stmt = base.filter(casted_field.contains(casted_values))

answered Mar 31, 2016 at 16:47

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RazerM Over a year ago

@Suraj Kapoor You don't need to cast employment_type to JSONB.

Haleemur Ali Over a year ago

@SurajKapoor, thanks. I just tried, and on my machine, no cast was necessary if a list was passed to contains, but an integer required casting. What version of sqlalchemy / python are you using? I'm on python 3.4.3 sqlalchemy 1.0.11

Suraj Kapoor Over a year ago

Tried without casting and does't work. I'm on Python 2.7 and Sqlalchemy 0.9.8

Suraj Kapoor Over a year ago

@HaleemurAli ps I think cast is necessary because this column was migrated from type hstore

RazerM · Accepted Answer · 2016-03-31 16:46:16Z

In my example, I have a JSONB column named bio with the following data:

{"employment_type": [1, 2, 3]}

Edit: Casting to JSONB works:

>>> from sqlalchemy.dialects.postgresql import JSONB
>>> employment_type = 2

>>> query = (
...     session.query(Candidate)
...     .filter(Candidate.bio['employment_type'].cast(JSONB).contains(employment_type)))

>>> query.one().bio
{"employment_type": [1, 2, 3]}

Original answer:

I couldn't get .contains to work on Candidate.bio['employment_type'], but we can do the equivalent of the following SQL:

SELECT * FROM candidate WHERE candidate.bio @> '{"employment_type": [2]}';

like this:

>>> employment_type = 2
>>> test = {'employment_type': [employment_type]}

>>> query = (
...     session.query(Candidate)
...     .filter(Candidate.bio.contains(test)))

>>> query.one().bio
{"employment_type": [1, 2, 3]}

Collectives™ on Stack Overflow

Cannot Query Array of Integers in Postgres

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related