有没有一种方法可以对jsonb对象中的值列表应用条件？

debugcn 发表于 Dev

乔希

目前，我在PostgreSQL表中有一个jsonb列，该列存储格式为json的对象

{
  "subscriptions": {
    "subscription1": {
      "subscribed": boolean
    },
    "subscription2": {
      "subscribed": boolean
    }
  }
}

可以有任意数量的subscription1 / 2 / etc。在文档中，所有名称都不同。我正在尝试查找文档是否包含我具有的每种订阅类型的subscriptions.subscription1.subscribed == true等。

我有一个需要查询的订阅密钥列表，所以我的第一个直觉是遍历它们，并查询数据库以获取这些计数：

subscriptionsKeys.forEach { subscription ->
    // find number of users where `subscription` == true
    val queryResult = repository.getNumberOfUsersSubscribed(subscription)

    // queryResult contains the number of users subscribed to `subscription`
}

其中getNumberOfUsersSubscribed（）定义为：

@SqlQuery(
    """
    SELECT count(*) as number_of_users FROM table
    WHERE jsonb_extract_path_text(body,'subscriptions',:subscriptionKey,'subscribed') = 'true'
    """
)
fun getNumberOfUsersSubscribedToOptOutList(
    subscriptionKey: String
): Int

这很好用，但是对于大量订阅，这意味着我将在表上进行n次count（*），这在我们的生产环境中有60多个查询，每个查询处理4500万条以上的记录。

我希望可以通过某种方式构造一个SQL / jsonb查询，该查询允许我传递键的列表/数组['subscription1'，'subscription2'等”，从而将上述 = true条件应用于每个输入列表行，然后以以下示例格式返回结果：

+------------------------------------+
| subscription_key | number_of_users |
+------------------+-----------------+
| subscription1    | 6               |
| subscription2    | 59              |
| etc.             | n               |
+------------------+-----------------+

任何意见，将不胜感激！

迈克·欧根尼克

只要您的库允许您将数组传递到text[]占位符，那么它就应该起作用。虽然不会特别快，但是应该比您已经使用的方法快。

with yourtable (id, body) as (
  values (1, '{
  "subscriptions": {
    "subscription1": {
      "subscribed": true
    },
    "subscription2": {
      "subscribed": true
    }
  }
}'::jsonb)
), search_terms as (
  select * 
    from unnest(array['subscription1', 'subscription2']) as st(term)
)
select st.term, count(*) 
  from yourtable y
       cross join lateral jsonb_each(y.body->'subscriptions') as b(k, v)
       join search_terms st 
         on st.term = b.k
        and b.v->>'subscribed' = 'true'
 group by st.term
;

┌───────────────┬───────┐
│     term      │ count │
├───────────────┼───────┤
│ subscription1 │     1 │
│ subscription2 │     1 │
└───────────────┴───────┘
(2 rows)

根据您的评论，以防万一您希望统计未明确显示subscribed为的订阅false：

with yourtable (id, body) as (
  values (1, '{
  "subscriptions": {
    "subscription1": {
      "subscribed": true
    },
    "subscription2": {
      "subscribed": true
    },
    "subscription3": {
      "subscribed": false
    }
  }
}'::jsonb),
        (2, '{
  "subscriptions": {
    "subscription1": {
      "subscribed": true
    },
    "subscription2": {
      "subscribed": true
    },
    "subscription3": {
      "subscribed": false
    }
  }
}'::jsonb),
        (3, '{}'::jsonb)
), search_terms as (
  select * 
    from unnest(array['subscription1', 'subscription2', 'subscription3', 'subscription4']) as st(term)
)
select st.term, 
       count(*) 
         filter (where 
           coalesce(
             body->'subscriptions'
               ->st.term 
               ->>'subscribed', ''
           ) != 'false')
  from yourtable t
       cross join search_terms st
 group by st.term
 order by st.term
;

┌───────────────┬───────┐
│     term      │ count │
├───────────────┼───────┤
│ subscription1 │     3 │
│ subscription2 │     3 │
│ subscription3 │     1 │
│ subscription4 │     3 │
└───────────────┴───────┘
(4 rows)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。