Optimizing Postgresql Query for a Subquery

Question

I'm pretty new to postgres and this is probably a novice question. How can I improve this query?

Three tables, campaigns 1-->M threads 1-->M messages. I have the campaign ID and I want all the threads with their total individual opens, clicks and replies counts of all the related messages where opens and clicks are in a JSONB field named extra under messages and replies are the messages that have column "direction"='received'.

select "threads".*, 
count("messages"."extra"->'opens') as opens, 
count("messages"."extra"->'clicks') as clicks, 
(
    select count("messages"."id")
    from "messages" 
    where "messages"."thread_id" = "threads"."id" 
    and "messages"."direction"='received'
) as replies
from "threads" 
inner join "messages" 
on "messages"."thread_id" = "threads"."id" 
where "threads"."campaign_id" 
in ('campaign_uuid') 
group by "threads"."id"

GMB · Accepted Answer · 2020-03-18 10:52:34Z

1

I think that you can just do a conditional count:

select 
    t.id, 
    sum(jsonb_array_length(m.extra->'opens')) as opens, 
    sum(jsonb_array_length(m.extra->'clicks')) as clicks, 
    count(*) filter(where m.direction='received') as replies
from threads t
inner join messages m on m.thread_id = t.id 
where t.campaign_id = 'campaign_uuid'
group by t.id

Notes:

if opens and clicks are jsonb arrays, you can use jsonb_array_length() to compute their lengths
you need to enumerate in the group by clause all non-aggregated columns from the select clause - so select * and grup by do not go along well together; I modified the query to only select threads.id; if you want more columns from threads then you should add them in both the select and group by clauses.
do not use double quotes around identifiers unless they are really needed (which does not seem to be the case in your query)
table aliases make the query easier to read and write

edited Mar 18, 2020 at 10:52

answered Mar 18, 2020 at 10:07

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rocky Over a year ago

Thanks for the detailed explanation. I just figured out my query is wrong. clicks and opens are array. count(m.extra->'opens') counts as one even if there are multiple items in the array. How do I get total counts? Think I needed SUM.

Gordon Linoff Over a year ago

@Rocky . . . This answers the question that you originally asked. If you have a different question, you should ask as a new question. I would also suggest that you include sample data and desired results as well.

GMB Over a year ago

@Rocky: you can use jsonb_array_length() to compute the number of items in an array.

Rocky Over a year ago

I have all the answers now. Thank you for your help (y)

Collectives™ on Stack Overflow

Optimizing Postgresql Query for a Subquery

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related