sql - 如何为整个表分配列并在postgresql中添加事件标志

Question

我有两个表 - 卡片和颜色。

CREATE TABLE cards(
  id serial PRIMARY KEY,
  text text NOT NULL CHECK (text <> ''),
  colors integer[] DEFAULT array[]::integer[]
);
CREATE TABLE colors(
  id serial PRIMARY KEY,
  color text NOT NULL
);

Colors 表包含所有可用的颜色。
卡片表中的颜色列包含与特定卡片相关的颜色 ID。

我需要选择所有卡片，其中每张卡片都应包含具有所有可用颜色的数组，并且每种颜色都应具有 active 属性，它告诉卡片是否有这种颜色

例如，如果卡片和颜色表有这些条目:

cards:

 id |  text  | colors 
----+--------+--------
 1  | card 1 | {1,2}
 2  | card 2 | {}

colors:
 id | color 
----+-------
  1 | red
  2 | green
  3 | blue

需要的结果应该是:

[{
  id: 1,
  text: 'card 1',
  colors: [{
    id: 1,
    color: 'red',
    active: true,
  }, {
    id: 2,
    color: 'green',
    active: true,
  }, {
    id: 3,
    color: 'blue',
    active: false,
  }]
}, {
  id: 2,
  text: 'card 2',
  colors: [{
    id: 1,
    color: 'red',
    active: false,
  }, {
    id: 2,
    color: 'green',
    active: false,
  }, {
    id: 3,
    color: 'blue',
    active: false,
  }]
}]

要为卡片表中的 colors 列分配所有可用颜色，我使用了以下查询:

SELECT c.id, c.text, json_agg(cl) AS colors FROM cards AS c
LEFT JOIN colors AS cl ON (cl.id IS NOT NULL)
GROUP BY c.id

但是如何为每种颜色添加 active 标志？

我尝试在 join 中使用 select:

SELECT c.id, c.text, json_agg(cl) AS colors FROM cards AS c
LEFT JOIN (
  SELECT cl.id, cl.color, cl.id = ANY(c.colors) AS active
  FROM colors AS cl
) AS cl ON (cl.id IS NOT NULL)
GROUP BY c.id

但它返回错误 - 对表“c”的 FROM 子句条目的引用无效

据我了解，我无法访问连接内查询中的 c 引用。
那么，如何使这段代码正确呢？谢谢。

Answer 1

您需要根据表中的数据构建的 json 对象的嵌套聚合。只要继续应用简单的逻辑，您就会到达那里:

SELECT json_agg(jsons)
FROM (
    SELECT json_build_object(
             'id', cards.id, 
             'text', cards.text,
             'colors', json_agg(json_build_object(
                                  'id', colors.id,
                                  'color', colors.color,
                                  'active', colors.id = ANY (cards.colors)
                                                 )
                               )
                            ) jsons
    FROM cards
    CROSS JOIN colors
    GROUP BY cards.id
    ORDER BY cards.id) sub;

这里的技巧是 CROSS JOIN 产生:

id |   text   | colors  | id | color
---+----------+---------+----+--------
 1 | 'card 1' | '{1,2}' |  1 | 'red'
 1 | 'card 1' | '{1,2}' |  2 | 'green'
 1 | 'card 1' | '{1,2}' |  3 | 'blue'
 2 | 'card 2' | '{}'    |  1 | 'red'
 2 | 'card 2' | '{}'    |  2 | 'green'
 2 | 'card 2' | '{}'    |  3 | 'blue'

active 列然后由 colors.id = ANY (cards.colors) 简单构造。从那时起就是 json_build_object() 和 json_agg() 直到你完成。由于您不能嵌套两个聚合函数，因此您需要一个子查询。