主页 / dba / 问题 / 296166
Accepted
Dimitrios Desyllas
Asked: 2021-07-27 04:24:25 +0800 CST

如何在 postgresql 中过滤 array_agg 值？

  • 772

我有下表命名jobs：

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

对于每个模型 ID，我将每个状态记录在一个数组中：

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

我将其用作子查询，以检测已处理作业中的任何故障：

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;

但在以下行中：

    'aborted' in statuses

有某种语法错误：

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

你知道为什么吗？

postgresql

2 个回答

  1. Best Answer

    您的查询中有许多语法错误：

    • 后面缺少逗号model
    • 基列称为status不statuses
    • 你不能写in statuses，你需要这样unnest的数组：
      in (select * from unnest(statuses))
      或者这样：
      in any (unnest(statuses))

    所以你的查询变成：

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

    但是，有一种更简单的方法来编写它。

    having您可以在子句中使用条件计数：

    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;
    • 2

  2. IN 参数右侧的括号是强制性的。但是，这些括号中的内容必须是一个列表。数组不起作用（除非左侧的东西也是数组，那么您正在测试数组列表中的数组）。要测试数组成员资格，您可以使用'aborted'=ANY(...)构造而不是IN. 但这是否与您没有使用的 json_agg 有关？

    • 0

相关问题