Querying value from JSON field in Flask-SQLAlchemy with postgresql

Question

I am using Flask, SQLAlchemy, and postgresql with JSON field types. I am having issues querying data out using SQLAlchemy

Here is my model example:

class WorkQueue(db.Model):
    __tablename__ = "workQueue"

    id = db.Column(db.Integer, primary_key=True)
    field_data = db.Column(JSON)

    def __init__(self, field_data = None):
        self.field_data = field_data

Here is an example of the dataset I am committing to the database

{
    "files": 
    [
        {
            "added": 1470248644.093014, 
            "totalbytes": 1109630458,  
            "filename": "/home/me/project/static/uploads/file.ext", 
            "finished": false,
            "basefilename": "file.ext",
            "file_id": 21, 
            "numsegments": 2792
        }
     ],
     "numfiles": 1,
     "success": true
}

I am having issues querying - trying to find "WorkQueue['files'][0]['file_id']" from a route defined as such:

@page.route('/remove/<int:id>', methods=['GET', 'POST'])
def file_remove(id):
    to_remove = id

    # here is where I would query and delete, but I can't get this right

From psql it would be something like this:

select * from "workQueue" t, json_array_elements(t.field_data->'files') as files WHERE CAST(files->>'file_id' AS INTEGER) = 1;

Just cannot seem to replicate that in SQLAlchemy

Answer 1

something like this

from sqlalchemy import func, Integer

files_subquery = session.query(func.json_array_elements(WorkQueue.field_data['files']).label('files')) \
                        .subquery()
query = session.query(WorkQueue.id, WorkQueue.field_data, files_subquery.c.files) \
               .filter(files_subquery.c.files.op('->>')('file_id').cast(Integer) == 1)