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?

Please have a look at: Minimal, complete and verifiable example — McNets
– McNets, Commented Jul 26, 2021 at 13:24

Charlieface · Accepted Answer · 2021-07-26 15:56:36Z

3

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;

edited Jul 26, 2021 at 15:56

answered Jul 26, 2021 at 13:40

Charlieface

18.1k22 silver badges45 bronze badges

'aborted' in (select * from unnest(statuses)) can be simplified to 'aborted' = any(statuses)

user1822
– user1822

2021-07-26 15:31:16 +00:00
Commented Jul 26, 2021 at 15:31
@a_horse_with_no_name Didn't know that works in Postgres, you can't do that with SQL Server

Charlieface
– Charlieface

2021-07-26 15:55:33 +00:00
Commented Jul 26, 2021 at 15:55
You can't do unnest() in SQL Server either ;) or use arrays.

user1822
– user1822

2021-07-26 16:00:44 +00:00
Commented Jul 26, 2021 at 16:00

Add a comment |

jjanes · Accepted Answer · 2021-07-26 14:51:42Z

0

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?

answered Jul 26, 2021 at 14:51

jjanes

42.6k3 gold badges44 silver badges54 bronze badges

Add a comment |

Stack Exchange Network

How I can filter a array_agg value in postgresql?

2 Answers 2

Your Answer

Hot Network Questions

How I can filter a array_agg value in postgresql?

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions