current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 5 down vote favorite
1

Let's say I have a Postgres database (9.3) and there is a table called Resources. In the Resources table I have the fields id which is an int and data which is a JSON type.

Let's say I have the following records in said table.

  • 1, {'firstname':'Dave', 'lastname':'Gallant'}
  • 2, {'firstname':'John', 'lastname':'Doe'}

What I want to do is write a query that would return all the records in which the data column has a json element with the lastname equal to "Doe"

I tried to write something like this:

records = db_session.query(Resource).filter(Resources.data->>'lastname' == "Doe").all()

Pycharm however is giving me a compile error on the "->>"

Does anyone know how I would write the filter clause to do what I need?

share|improve this question
    
Try using astext like: ...filter(Resources.c.data["lastname"].astext == "Doe").all() and see if it works – Anzel Apr 30 '15 at 18:23
    
That seems to work, though I had to remove the ".c" and just go "Resources.data" directly. Thanks! – Dave Gallant Apr 30 '15 at 18:30
    
Ah... yes, I usually go with .c for column name... glad you have it solved. I will post above as an answer then :-) – Anzel Apr 30 '15 at 18:31
up vote 7 down vote accepted

Try using astext

records = db_session.query(Resource).filter(
              Resources.data["lastname"].astext == "Doe"
          ).all()
share|improve this answer
1  
Great answer. In addition, the link you gave also provides some other information on how to query nested json (eg query.filter(Resources.data[("lastname", "whatever-other-nested-json-under-lastname")].astext == "Whatever Value").all() ), and how to change data types. – Josh Brown Jun 8 '16 at 21:10
up vote 2 down vote

Also you could explicitly cast string to JSON (see Postgres JSON type doc). 

from sqlalchemy.dialects.postgres import JSON
from sqlalchemy.sql.expression import cast
db_session.query(Resource).filter(
    Resources.data["lastname"] == cast("Doe", JSON)
).all()
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.