我有一个带有json列 contact_info的表,该列的结构通常是这样:
{ telephones:[ { telephone:54435345, type:座机}, { telephone:694823747, type: mobile},] }我想查找所有具有特定电话的行, 我在sqlalchemy中的json数组周围发现的唯一东西是这样的:
Table.contact_info [电话 ] [0] [电话] .astext.ilike(mask)但这仅搜索 0 个元素。
当前我的愚蠢解决方案是将电话转换为文本并执行 ilike ,但这当然是错误的...
Table._contact_info [ telephones]。astext.ilike(mask)解决方案
在具有JSONB的PostgreSQL中,您可以使用包含检查:
dict = {电话:[{电话: 54435345}]} 用户= cls.query.filter(your_table.contact_info.contains(dict))。first()在MySQL 中,可能可以使用 func 。 json_contains :
#JSON_CONTAINS返回0或1,未找到或未找到。不确定MySQL #是否喜欢WHERE中的整数值,为了安全起见添加== 1 session.query(Story).filter(func.json_contains(Story.section_ids,X)== 1) .all()
(您需要适应并尝试一下,当然是MySQL的方式,但也可能是PostgreSQL的方式)
I have a table with a json column "contact_info", the structure of this column is usually this:
{ "telephones":[ {"telephone":54435345,"type":"landline"}, {"telephone":694823747,"type":"mobile"}, ] }I want to find all the rows that have a specific telephone, The only thing I found around json arrays in sqlalchemy is something like this:
Table.contact_info["telephones"][0]["telephone"].astext.ilike(mask)But this searches only the 0th element.
Currently my stupid solution is to convert the "telephones" into text and do an ilike, but this is wrong of course...
Table._contact_info["telephones"].astext.ilike(mask)解决方案
In PostgreSQL with JSONB you can use the containment check:
dict = {"telephones": [{"telephone": "54435345"}]} user = cls.query.filter(your_table.contact_info.contains(dict)).first()In MySQL it might be possible to use func.json_contains:
from sqlalchemy import func # JSON_CONTAINS returns 0 or 1, not found or found. Not sure if MySQL # likes integer values in WHERE, added == 1 just to be safe session.query(Story).filter(func.json_contains(Story.section_ids, X) == 1).all()
(you need to adapt and try it out, certainly the MySQL way, but probably also the PostgreSQL one)
更多推荐
sqlalchemy,筛选包含数组的json列
发布评论