Блог

В настоящее время у меня есть столбец jsonb в таблице PostgreSQL, в которой хранится объект json с форматом

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

В документе может быть любое количество подписок1 / 2 / и т.д., все с разными именами. Я пытаюсь выяснить, содержит ли документ 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 раз, что в нашей производственной среде составляет более 60 запросов, каждый из которых работает с более чем 45 миллионами записей.

Я надеюсь, что есть какой-то способ создать запрос 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)