title: Aggregate key column and multiple value columns to JSON object
body: <p>I have this table:</p>
  
  <pre><code>CREATE TABLE my_table (
      id uuid PRIMARY KEY,
      name text NOT NULL,
      key text NOT NULL,
      x int NOT NULL,
      y int NOT NULL,
      UNIQUE (name, key)
  );
  </code></pre>
  
  <p>With this data:</p>
  
  <pre>
  id                                   name key x y
  12345678-abcd-1234-abcd-123456789000 foo  a   1 2
  12345678-abcd-1234-abcd-123456789001 foo  b   3 4
  12345678-abcd-1234-abcd-123456789002 foo  c   5 6
  12345678-abcd-1234-abcd-123456789003 foo  d   7 8
  12345678-abcd-1234-abcd-123456789004 bar  v   0 0
  12345678-abcd-1234-abcd-123456789005 bar  w   1 1
  12345678-abcd-1234-abcd-123456789006 bar  z   2 2
  12345678-abcd-1234-abcd-123456789007 baz  a   8 7
  12345678-abcd-1234-abcd-123456789008 baz  b   6 5
  12345678-abcd-1234-abcd-123456789009 baz  c   4 3
  12345678-abcd-1234-abcd-123456789010 baz  d   2 1
  </pre>
  
  <p>I have this query:</p>
  
  <pre><code>SELECT name, json_build_object(key, json_build_object('x', x, 'y', y))
  FROM my_table;
  </code></pre>
  
  <p>With this result:</p>
  
  <pre>
  name json_build_object
  foo  {"a" : {"x" : 1, "y" : 2}}
  foo  {"b" : {"x" : 3, "y" : 4}}
  foo  {"c" : {"x" : 5, "y" : 6}}
  foo  {"d" : {"x" : 7, "y" : 8}}
  bar  {"v" : {"x" : 0, "y" : 0}}
  bar  {"w" : {"x" : 1, "y" : 1}}
  bar  {"z" : {"x" : 2, "y" : 2}}
  baz  {"a" : {"x" : 8, "y" : 7}}
  baz  {"b" : {"x" : 6, "y" : 5}}
  baz  {"c" : {"x" : 4, "y" : 3}}
  baz  {"d" : {"x" : 2, "y" : 1}}
  </pre>
  
  <p>What I'd like to have:</p>
  
  <pre>
  foo {"a" : {"x" : 1, "y" : 2}, "b" : {"x" : 3, "y" : 4}, "c" : {"x" : 5, "y" : 6}, "d" : {"x" : 7, "y" : 8}}
  bar {"v" : {"x" : 0, "y" : 0}, "w" : {"x" : 1, "y" : 1}, "z" : {"x" : 2, "y" : 2}}
  baz {"a" : {"x" : 8, "y" : 7}, "b" : {"x" : 6, "y" : 5}, "c" : {"x" : 4, "y" : 3}, "d" : {"x" : 2, "y" : 1}}
  </pre>
  
  <p>I know I need to <code>GROUP BY name</code> and <a href="https://www.postgresql.org/docs/9.6/functions-aggregate.html" rel="nofollow noreferrer">aggregate</a> the other columns, but couldn't find a suitable combination of <a href="https://www.postgresql.org/docs/9.6/functions-json.html" rel="nofollow noreferrer"><code>functions</code></a>. Is this even possible with a single query?</p>