代码之家 › 专栏 › 技术社区 › edbras

在Postgresql中的WHERE中使用JSON数组进行查询

jsonb sqlalchemy postgresql python

edbras · 技术社区 · 7 年前

我有一个带有payment\u info JSONB列的用户模型,其中包含以下json示例:

{
    "customer_id": "cst_K5gCsCkKAU",
    "subscriptions": [
        {
            "status": "active",
            "external_id": "sub_3Q9Q4bP2zW"
        }
    ]
}

我是一个JSON查询的新手,但针对Postgres(PG)db创建了以下看起来有效的内容,即:我搜索所有具有特定external\u id值的用户:

SELECT payment_info->'subscriptions' as Subscriptions
    FROM public."user"
    , jsonb_array_elements(payment_info->'subscriptions') as subs
    where (subs->>'external_id')::text = 'sub_3Q9Q4bP2zW'

如何在SQLAlchemy中执行相同的操作?我尝试了几次在网上找到的想法(SO),但都不起作用。我试过:

JSONB Comparator

query = misc.setup_query(db_session, User).filter(
    User.payment_info.comparator.contains(
        ('subscriptions', 'external_id') == payment_subscription_id))

这将导致以下错误:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: jsonb @> boolean
LINE 3: WHERE "user".payment_info @> false
                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

json_contains 功能:

from sqlalchemy import func
query = misc.setup_query(db_session, User).filter(
    func.json_contains(User.payment_info,
                       payment_subscription_id,
                       ['subscriptions', 'external_id']))

这导致:

LINE 3: WHERE json_contains("user".payment_info, 'sub_QxyMEmU', ARRA...
           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

密钥路径:

query = misc.setup_query(db_session, User).filter(
    User.payment_info['subscriptions', 'external_id'].astext == payment_subscription_id)

结果为空,查询如下:

SELECT *
FROM "user" 
WHERE ("user".payment_info #>> %(payment_info_1)s) = %(param_1)s

我做错了什么,我怎样才能让它起作用?顺便问一下:我需要在 external_id ?(尚未出现)

1 回复 | 直到 7 年前

Ilja Everilä 5 年前

您几乎可以使用 aliased function expression :

misc.setup_query(db_session, User).\
    select_from(
        User,
        func.jsonb_array_elements(User.payment_info['subscriptions']).
            alias('subs')).\
    filter(column('subs', type_=JSONB)['external_id'].astext == 'sub_3Q9Q4bP2zW')

编译为

SELECT "user".id AS user_id, "user".payment_info AS user_payment_info 
FROM "user", jsonb_array_elements("user".payment_info -> %(payment_info_1)s) AS subs 
WHERE (subs ->> %(subs_1)s) = %(param_1)s

另一方面,您可以使用 containment operator :

misc.setup_query(db_session, User).\
    filter(User.payment_info['subscriptions'].contains(
        [{'external_id': 'sub_3Q9Q4bP2zW'}]))

请注意,最外面的列表是必需的,因为它是要检查的“路径”的一部分。使用相同的逻辑,您可以省略提取数组:

misc.setup_query(db_session, User).\
    filter(User.payment_info.contains(
        {'subscriptions': [{'external_id': 'sub_3Q9Q4bP2zW'}]}))

以上 @> 使用方法可以使用 GIN index 。第一个需要函数索引,因为它首先提取数组:

CREATE INDEX user_payment_info_subscriptions_idx ON "user"
USING GIN ((payment_info -> 'subscriptions'));

第二个需要索引整个 payment_info jsonb列。创建GIN索引可以在SQLAlchemy模型定义中使用 Postgresql-specific index options :

class User(Base):
    ...

Index('user_payment_info_subscriptions_idx',
      User.payment_info['subscriptions'],
      postgresql_using='gin')

至于为什么各种尝试都没有成功:

你不应该直接访问比较仪。它提供了类型的运算符。此外,你通过了 contains() 表达式的结果
```
 ('subscriptions', 'external_id') == payment_subscription_id
```
哪个是假的,最有可能(取决于什么 payment_subscription_id 是的)。也就是说,它是经过评估的 在Python中 。
There is no json_contains() function 在Postgresql中(与MySQL不同)。使用 @&燃气轮机; 运算符或SQL/JSON路径函数,例如 jsonb_path_exists() 。
你走错了路。 User.payment_info['subscriptions', 'external_id'].astext 将匹配以下内容 {"subscriptions": {"external_id": "foo"}} ,但在您的数据中 subscriptions 引用数组。