How to filter a array_agg value in postgresql

postgresql

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?

Best Answer

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;

Related Solutions

Postgresql – How to define alias in an ARRAY_AGG expression

You are forming an ad-hoc row type (effectively an anonymous record) with this expression:

(media_files.position, media_files.token, media_files.title)

in your aggregate function call:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)
          ORDER BY media_files.position) AS media_files

Arrays types can only be built upon well-known types. Your option is to announce such a type to the system and cast the record to it before forming the array. Create a well-known composite type:

CREATE TYPE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

I am guessing data types for lack of information here. Fill in your actual types.

Creating a table has the same effect: It announces a well known composite type to the system indirectly, as well. For this reason, you can (ab-)use a temporary table to register a composite type for the duration of the session:

CREATE TEMP TABLE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

Either way, you can then cast your record:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files

Then you can reference elements of the (now well-known) type by name:

SELECT media_files[1].position, media_files[1].token
FROM  (
     ...
   ,ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files
   ...
   FROM ....
   GROUP BY ...
  ) sub;

Now, Postgres can use these names for building a JSON value. Voilá.

Postgresql – Multiple to_json(array_agg), separate joins

The basic problem is this:

Two SQL LEFT JOINS produce incorrect result

Here is one way to avoid this problem and improve some other details while being at it:

SELECT m.*, u.users, r.replies
FROM   messages m
LEFT   JOIN LATERAL (
   SELECT json_agg(u) AS users
   FROM   (
      SELECT id, fullname, email, company
      FROM   users
      WHERE  id IN (m.user_id, m.to)
      -- no order by?
      ) u
   ) u ON TRUE
LEFT   JOIN LATERAL (
   SELECT json_agg(r) AS replies
   FROM   (
      SELECT *
      FROM   messages
      WHERE  message_id = m.id
      ORDER  BY created_at
      ) r
   ) r ON TRUE
WHERE  :to IN (m.user_id, m.to)
AND    m.to IS NOT NULL;

Use json_agg() instead of to_json(array_agg()). Simpler, faster.
Aggregate the subquery on users right away in a LATERAL join, thus avoiding the main problem. Related:
- Create smaller row from alias and preserve column names

This:

WHERE  m.to = :to OR
      (m.to IS NOT NULL AND m.user_id = :to)

can be expressed simpler:

WHERE  :to IN (m.user_id, m.to)
AND    m.to IS NOT NULL

This:

SELECT json_agg(r) AS replies
FROM   (
   SELECT *
   FROM   messages
   WHERE  message_id = m.id
   ORDER  BY created_at
   ) r

could be shortened to:

SELECT json_agg(r ORDER BY created_at) AS replies
FROM   messages r
WHERE  message_id = m.id

But a single ORDER BY in a subquery is typically faster than per-aggregate ORDER BY.

Best Answer

Related Solutions

Postgresql – How to define alias in an ARRAY_AGG expression

Postgresql – Multiple to_json(array_agg), separate joins

Related Question