如何使用SqlAlchemy在Postgres中查询JSON数组？ [英] How to query JSON Array in Postgres with SqlAlchemy?

问题描述

我定义了一个SqlAlchemy模型

I have a SqlAlchemy model defined

from sqlalchemy.dialects.postgresql import JSONB

class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True)
    nickname = db.Column(db.String(255), nullable=False)
    city = db.Column(db.String(255))
    contact_list = db.Column(JSONB)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

def add_user():
    user = User(nickname="Mike")
    user.contact_list = [{"name": "Sam", "phone": ["123456", "654321"]}, 
                         {"name": "John", "phone": ["159753"]},
                         {"name": "Joe", "phone": ["147889", "98741"]}]
    db.session.add(user)
    db.session.commit()

if __name__ == "__main__":
    add_user()

如何检索名称从我的 contact_list 中使用电话？例如，我有 147889 ，如何获取 Joe ？

How can I retrieve the name from my contact_list using phone? For example, I have the 147889, how can I retrieve Joe?

我已经尝试过

User.query.filter（User.contact_list.contains（{ phone：[ 147889]}）））。all（）

但是，它返回了一个空列表， []

But, it returns me an empty list, []

我该怎么做？

推荐答案

您只是忘记了JSON路径应包括最外层数组：

You just forgot that your JSON path should include the outermost array as well:

User.query.filter(User.contact_list.contains([{"phone": ["147889"]}])).all()

将返回您要查找的用户。如果您的JSON包含一个键为 phone等的对象，则原始查询将匹配。请注意，这将返回所讨论的 User 对象，而不是返回的特定对象/名称。 JSON结构。如果您希望这样做（这似乎是最终目标），则可以扩展每个用户的数组元素，根据结果记录进行过滤，然后选择名称：

will return the user you are looking for. The original query would match, if your JSON contained an object with key "phone" etc. Note that this returns the User object in question, not the specific object/name from the JSON structure. If you want that, as seems to be the end goal, you could expand the array elements of each user, filter based on the resulting records, and select the name:

val = db.column('value', type_=JSONB)
db.session.query(val['name'].astext).\
    select_from(User,
                db.func.jsonb_array_elements(User.contact_list).alias()).\
    filter(val.contains({"phone": ["147889"]})).\
    all()

另一方面，上述查询的索引友好性不如第一个是，因为它必须在过滤之前扩展所有数组，因此首先在子查询或CTE的联系人列表中找到包含电话的用户，然后进行扩展和过滤可能是有益的。

On the other hand the above query is not as index friendly as the first one can be, because it has to expand all the arrays before filtering, so it might be beneficial to first find the users that contain the phone in their contact list in a subquery or CTE, and then expand and filter.

这篇关于如何使用SqlAlchemy在Postgres中查询JSON数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！