Here's a simplified version of a query I run. I've removed a bunch of key/value pairs for simplicity. The constraint is that I can't change anything outside of the
ARRAY function; but the
ARRAY function and everything inside is fair game. I want to remove the subqueries if at all possible; and if not speed up the query as much as possible.
SELECT json_build_object('last', max(day), 'missing_days', ARRAY( SELECT distinct to_char(dates, 'YYYY-MM-DD') FROM generate_series('2009-12-20', CURRENT_DATE, interval '1 day') AS dates WHERE dates NOT IN (SELECT day FROM records)) ) AS jsobj FROM records;
I thought I remembered from my database design classes that you should be able to use columns from the records table in the sub-query but I could be mistaken, or that might be database dependent, or I could have been trying the wrong syntax; anyway it didn't work when I tried it.