Ecto `select/3` to get the last error from an ObanJob

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?

You may be looking for the unnest function, which would let you treat that jsonb[] as multiple rows with a jsonb column.

Oban.Job
|> where([j], j.state != "completed")
|> select([j],
  %{
    id: j.id,
    task: fragment("?->'task'", j.args),
    last_error: fragment("?[array_upper(?, 1)]", j.errors, j.errors)
  }
)

The main thing here is that errors column is jsonb[] that is to say an array of jsonb, not a jsonb array.

Thanks @benwilson512. This was one of those things I worked on then transitioned to something else, coming back to it now, that did the trick!

1 Like