title: How to assign whole table with column and add active flag in postgresql
body: <p>I have two tables - cards and colors.</p>
  
  <pre><code>CREATE TABLE cards(
    id serial PRIMARY KEY,
    text text NOT NULL CHECK (text &lt;&gt; ''),
    colors integer[] DEFAULT array[]::integer[]
  );
  CREATE TABLE colors(
    id serial PRIMARY KEY,
    color text NOT NULL
  );
  </code></pre>
  
  <p>Colors table contains all available colors. <br/>Colors column, in cards table, contain ids of colors, related to particular card.</p>
  
  <p>I need select all cards, where each card should contain array with all available colors and each color should have <code>active</code> property, which tells has card this color or not<br/><br/>
  For example if cards and colors tables have these entries:</p>
  
  <pre><code>cards:
  
   id |  text  | colors 
  ----+--------+--------
   1  | card 1 | {1,2}
   2  | card 2 | {}
  
  colors:
   id | color 
  ----+-------
    1 | red
    2 | green
    3 | blue
  </code></pre>
  
  <p>Needed result should be:</p>
  
  <pre><code>[{
    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,
    }]
  }]
  </code></pre>
  
  <p>To assign all available colors with <code>colors</code> column in cards table, I used this query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But how I can add <code>active</code> flag to each color?<br/><br/>
  I tried to use <code>select</code> inside of join:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But it returns an error - <code>invalid reference to FROM-clause entry for table "c"</code><br/><br/>
  As I understand, I can't have access to <code>c</code> reference in query inside of join.<br/>
  So, how to make this code correct? Thanks.</p>