Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →

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

share|improve this question
up vote 1 down vote accepted

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)
share|improve this answer
    
Perfectly illustrated what I'm asking for. Thanks a ton. – weevisss Aug 5 at 18:41

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.