0

I have this query:

select settings.customer_id, current::jsonb->'alarms' as alarms, from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
where res.alarms is not null;

which returns

+------------------+
|customer_id|alarms|
|1          |{...} |
|2          |{...} |
|3          |{...} |
+------------------+

Then I have another entity registration, which fields customer_id and date, where date is of type date and I store the values in format YYYY-MM-DD.

What I want to achieve is to join the registration entity on customer_id and get a count on the registration records for a given single date, f.e. '2018-11-20'. But (here's the catchy part) I want to retrieve it in a way so if there are no registration records for the date, then return 0, otherwise return the actual number.

With the solutions I've tried so far, I would get only records that have the alarms AND have registrations as well, as if I have this:

select settings.customer_id, current::jsonb->'alarms' as alarms, coalesce(regs.count, 0) from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
join (select customer_id, count(customer_id) from registration where date='2018-11-20' group by customer_id) as regs on settings.customer_id=reg.customer_id
where res.alarms is not null;

or

select settings.customer_id, current::jsonb->'alarms' as alarms, coalesce(count(regs.customer_id), 0) from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
join registration as regs on settings.customer_id=regs.customer_id
where res.alarms is not null and regs.date='2018-10-10'
group by settings.customer_id, settings.current;

If I would put it in other words: get records which have alarms object and also get the number of their registrations for the given date, no matter if its 0 or > 0.

1

LEFT JOIN is the key, like ypercube commented.

And you probably don't need the self-join - assuming settings.customer_id is unique not null.

If your predicate is selective (more than a few rows without alarms), I suggest to count in a LATERAL subquery:

SELECT s.customer_id, s.current::jsonb->'alarms' AS alarms
     , coalesce(r.reg_ct, 0) AS reg_ct
FROM   settings s
LEFT   JOIN LATERAL (
   SELECT count(*) AS reg_ct
   FROM   registration
   WHERE  customer_id = s.customer_id
   AND    date = '2018-10-10'
   ) r ON true
WHERE  s.current::jsonb->'alarms' IS NOT NULL;

Else, a plain subquery should be faster:

SELECT s.customer_id, s.current::jsonb->'alarms' AS alarms
     , coalesce(r.reg_ct, 0) AS reg_ct
FROM   settings s
LEFT   JOIN (
   SELECT customer_id, count(*) AS reg_ct
   FROM   registration
   WHERE  date = '2018-10-10'
   GROUP  BY 1
   ) r USING (customer_id)
WHERE  s.current::jsonb->'alarms' IS NOT NULL;

Either way, it's typically faster and less error prone to aggregate before the join.
Related:

  • 1
    Woah, thanks for the explanation! :) Btw, on the LATERAL query I get ERROR: aggregate functions are not allowed in GROUP BY, so I removed the GROUP BY and it works. :) And also for me the LATERAL query executes on average 5 times faster than the plain subquery. – Milkncookiez Nov 21 '18 at 7:49
  • 1
    @Milkncookiez: Yes, that GROUP BY 1 was a code artifact, you did the right thing by just removing it. – Erwin Brandstetter Nov 21 '18 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.