时间戳

首页 > 解决方案 > 有没有办法对 jsonb 对象中的值列表应用条件？

sql - 有没有办法对 jsonb 对象中的值列表应用条件？

问题描述

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

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

可以有任意数量的订阅1/2/等。在文档中，都具有不同的名称。我试图找出文档是否包含我拥有的每种订阅类型的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',etc] 将 = true针对每个输入列表应用上述条件行，然后以以下示例格式返回结果：

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

任何意见，将不胜感激！

标签： sqlpostgresqlkotlin

解决方案

只要您的库允许您将数组传递给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)

推荐阅读