How I can filter a array_agg value in postgresql?

Question

I have the following table named jobs:

id PK Serial
model String
model_id UNSIGNED Integer
status String
created_at timestamp
prosessed_at timestamp

And foreach model Id I record each status in an array:

select
 model_id,
 model,
 array_agg(id) as jobs,
 array_agg(statuses) as statuses
from jobs
group by model,model_id

And I use that as a subquery in order to detect any malfucntion in prosessed jobs:

select
 *
from (
 select
 model_id,
 model
 array_agg(id) as jobs,
 array_agg(statuses) as statuses
 from jobs
 group by model,model_id
) as jobs
where
 'aborted' in statuses
and
 'pending' not in statuses
and
 'failed' not in statuses
and
 'processed' not in statuses;

But in the following line:

 'aborted' in statuses

Has some sort syntax error:

SQL Error [42601]: ERROR: syntax error at or near "statuses"
 Position: 312

Do you have any idea why?

• McNets: Please have a look at: Minimal, complete and verifiable example

Answer 1

You have a number of syntax errors in your query:

• Missing comma after model
• The base column is called status not statuses
• You can't write in statuses, you need to unnest the array like this:
  in (select * from unnest(statuses))
  Or this:
  in any (unnest(statuses))

So your query becomes:

select
 *
from (
 select
 model_id,
 model,
 array_agg(id) as jobs,
 array_agg(status) as statuses
 from jobs
 group by model,model_id
) as jobs
where
 'aborted' in any (unnest(statuses))
and
 'pending' not in any (unnest(statuses))
and
 'failed' not in any (unnest(statuses))
and
 'processed' not in any (unnest(statuses));

However, there is a much easier way of writing this.

You can use conditional counts in a having clause:

select
 model_id,
 model,
 array_agg(id) as jobs,
 array_agg(status) as statuses
from jobs
group by model, model_id
having
 count(*) filter (where status = 'aborted') > 0
and
 count(*) filter (where status = 'pending') = 0
and
 count(*) filter (where status = 'failed') = 0
and
 count(*) filter (where status = 'processed') = 0;

• user1822: 'aborted' in (select * from unnest(statuses)) can be simplified to 'aborted' = any(statuses)
• Charlieface: @a_horse_with_no_name Didn't know that works in Postgres, you can't do that with SQL Server
• user1822: You can't do unnest() in SQL Server either ;) or use arrays.

Answer 2

The parentheses around the right-hand-side of the IN argument are mandatory. However, what goes in those parentheses must be a list. An array doesn't work (unless the thing on the left-hand-side is also an array, then you are testing for an array in a list-of-arrays). To test array membership, you can use the 'aborted'=ANY(...) construct instead of IN. But does any of this have to do with json_agg, which you are not using?