python - SqlAlchemy 是否支持 SQL/JSON

Question

我很感兴趣，sqlalchemy 1.3 是否支持这些 Postgresql-12 函数？

• jsonb_path_exists(jsonb, jsonpath[, jsonb, bool])
• jsonb_path_match(jsonb, jsonpath[, jsonb, bool])
• jsonb_path_query(jsonb, jsonpath[, jsonb, bool])
• jsonb_path_query_array(jsonb, jsonpath[, jsonb, bool])
• jsonb_path_query_first(jsonb, jsonpath[, jsonb, bool])

Answer 1

是的——您可以使用func 生成几乎任何函数表达式:https://docs.sqlalchemy.org/en/13/core/functions.html .例如:

import json

from sqlalchemy import func

# Usually this'd be a column in your model
target = '{"a":[1,2,3,4,5]}'
path = '$.a[*] ? (@ >= $min && @ <= $max)'
# Use json.dumps to produce a valid json(b) literal from Python
vars_ = json.dumps({"min": 2, "max": 4})

engine.execute(select([func.jsonb_path_query(target, path, vars_)]))