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

我有两张桌子-卡片和颜色。

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

颜色表包含所有可用的颜色。cards表中的Colors列包含与特定卡相关的颜色id。
我需要选择所有卡，其中每个卡都应该包含所有可用颜色的数组，并且每个颜色都应该具有active属性，该属性告诉has card是否具有此颜色
例如，如果卡片和颜色表有以下条目：

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,
  }]
}]

要使用cards表中的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

但它返回一个错误-invalid reference to FROM-clause entry for table "c"
据我所知，我不能在join的查询中访问c引用。
那么，如何使代码正确？谢谢。

最佳答案

您需要从表中的数据构建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()直到你完成。由于不能嵌套两个聚合函数，因此需要一个子查询。

关于sql - 如何在列中分配整个表并在PostgreSQL中添加事件标志，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37857203/