I’m trying to reduce the amount of information that is returned from a query by using a select/3
statement.
In this particular case I’m working with Oban so the data structure should be familiar, where the oban_jobs
table has an errors
column that stores multiple errors, but I want to select the last one. This is some pseudo code I am struggling to implement:
Job
|> where([j], j.state != "completed")
|> select([j], %{
id: j.id,
task: fragment("?->'task'", j.args),
errors:
fragment("(SELECT errors FROM oban_jobs WHERE id = ? ORDER BY (errors->>'at')::timestamp DESC LIMIT 1)", j.id),
state: j.state
})
|> Repo.all()
If it’s possible, I feel like I’m close but the above pulls up an (undefined_function) operator does not exist: jsonb[] -> unknown
error.
What would the correct fragment be to get only the latest error?
Perhaps a more existential question here is, is it worth it? Will I save any computation by selecting only the latest error message rather than just returning all error messages when querying hundreds or thousands of jobs?