时间戳

首页 > 解决方案 > 在 Postgresql 上的 SQLAlchemy 中索引 JSON

postgresql - 在 Postgresql 上的 SQLAlchemy 中索引 JSON

问题描述

我在我的 SQLAlchemy DB 模型中使用 JSON：

from sqlalchemy.dialects.postgresql import JSON

class Customer(db.Model):
  id = db.Column(db.Integer, primary_key=True, autoincrement=True)
  custcontext_json = db.Column(JSON, default=lambda: {})

现在我的查询变得太慢了：

customers = Customer.query.filter(
  Customer.clientid == clientid,
  or_(
    func.lower(Customer.custcontext_json['cinfo', 'userName'].astext.cast(Unicode)).contains(searchterm.lower()),
    func.lower(Customer.custcontext_json['cinfo', 'home', 'sign'].astext.cast(Unicode)).contains(searchterm.lower())
  ),

  or_(
    Customer.custcontext_json['cinfo', 'home', 'status'].astext == 'pre',
    Customer.custcontext_json['cinfo', 'home', 'status'].astext == 'during',
    Customer.custcontext_json['cinfo', 'lastChange'].astext.cast(DateTime) > pendulum.now('UTC').subtract(days=14)
  )
  ).all()

是否可以为此查询索引表。或者至少是其中的一部分？

标签： postgresqlsqlalchemyflask-sqlalchemy

解决方案

我会使用JSONB作为密度更高的格式。但这不是答案。

请看这里。PostgreSQL 中 JSON 类型的运算符，您可以从这里获得。我想得到你的创建表，在我身边重现一些东西。反正。我会从这样的事情开始：

create table customer (clientid serial, custcontext_json jsonb);

create index on customer (
  (CAST((custcontext_json->'cinfo'->'userName') AS TEXT)),
  (CAST((custcontext_json->'cinfo'->'home'->'sign') AS TEXT)),
  (CAST((custcontext_json->'cinfo'->'home'->'status') AS TEXT)));

推荐阅读