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

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

  •  2
  • edbras  · 技术社区  · 6 年前

    我有一个带有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),但都不起作用。我试过:

    1. 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.
      
    2. 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.
      
    3. 密钥路径:

      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 回复  |  直到 6 年前
        1
  •  1
  •   Ilja Everilä    4 年前

    您几乎可以使用 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')
    

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

    1. 你不应该直接访问比较仪。它提供了类型的运算符。此外,你通过了 contains() 表达式的结果

       ('subscriptions', 'external_id') == payment_subscription_id
      

      哪个是假的,最有可能(取决于什么 payment_subscription_id 是的)。也就是说,它是经过评估的 在Python中

    2. There is no json_contains() function 在Postgresql中(与MySQL不同)。使用 @&燃气轮机; 运算符或SQL/JSON路径函数,例如 jsonb_path_exists()

    3. 你走错了路。 User.payment_info['subscriptions', 'external_id'].astext 将匹配以下内容 {"subscriptions": {"external_id": "foo"}} ,但在您的数据中 subscriptions 引用数组。