title: Convert array of records to JSON in Postgresql
body: <p>I'm having problems with converting an array of records into JSON with Postgresql.</p>
  
  <p><strong>Version:</strong> psql (PostgreSQL) 9.5.3</p>
  
  <p>Current query:</p>
  
  <pre><code>SELECT c.id, (select array(
          select (cp.id,cp.position)
          from contactposition cp
          where cp.contact_id_id = c.id  -- join on the two tables
          )
        ) as contactpositions
  from contacts c;
  </code></pre>
  
  <p>Contact from table <code>contacts</code> can have many positions assigned from <code>contactposition</code> table.</p>
  
  <p>Result is something like this:</p>
  
  <pre><code>| id (integer) | contactpositions (record[])                                          |
  |--------------|----------------------------------------------------------------------|
  | 5            | {"(21171326,\"Software Developer\")","(21171325,Contractor)" (...)"} |
  </code></pre>
  
  <p>But I would like it to be something like this:</p>
  
  <pre><code>| id (integer) | contactpositions (record[])                                          |
  |--------------|----------------------------------------------------------------------|
  | 5            | [{"id": 21171326, "position": "Software Developer", "id": 21171325, "position": "Contractor", (...)] |
  </code></pre>
  
  <p>I am aware of several helper functions like <code>array_to_json</code>, but I just can't get it to work.</p>
  
  <p>I've tried:</p>
  
  <pre><code>SELECT c.id, array_to_json(select array(
              select (cp.id,cp.position)
              from contactposition cp
              where cp.contact_id_id = c.id
              )
            ) as contactpositions
  from contacts c;
  </code></pre>
  
  <p>But it throws: <code>ERROR: syntax error at or near "select"</code>, so obviously I'm not using it right.</p>
  
  <p>I would appreciate any tips, thanks!</p>