How I can filter a array_agg value in postgresql?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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;

Method 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?

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply