基于单个字符串的Postgres JSON列表对象上的Flask SQLAlchemy过滤器

from sqlalchemy import func
from sqlalchemy.dialects.postgresql import JSON
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class MyModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    json_list = db.Column(JSON)

from flask import Flask

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'your_database_uri'
db.init_app(app)

def filter_by_json_list(json_list, value):
    return MyModel.query.filter(
        func.json_array_length(MyModel.json_list) > 0,
        func.json_array_elements_text(MyModel.json_list).ilike(f'%{value}%')
    ).all()

@app.route('/search/<value>')
def search(value):
    results = filter_by_json_list(MyModel.json_list, value)
    # 处理查询结果
    return ...