json - 使用postgresql从Flask-SQLAlchemy中的JSON字段查询值

Question

我将 Flask、SQLAlchemy 和 postgresql 与 JSON 字段类型一起使用。我在使用 SQLAlchemy 查询数据时遇到问题

这是我的模型示例:

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

这是我提交给数据库的数据集的示例

{
    "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
}

我在查询时遇到问题 - 试图从这样定义的路由中找到“WorkQueue['files'][0]['file_id']”:

@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

在 psql 中它会是这样的:

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

似乎无法在 SQLAlchemy 中复制它

Answer 1

像这样

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)