title: How to convert two rows into key-value json object in postgresql?
body: <p>I have a data model like the following which is simplified to show you only this problem (<strong>SQL Fiddle</strong> Link at the bottom):</p>
  
  <p><a href="https://i.stack.imgur.com/M96QV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/M96QV.png" alt="enter image description here"></a></p>
  
  <p>A person is represented in the database as a meta table row with a name and with multiple attributes which are stored in the data table as key-value pair (key and value are in separate columns).</p>
  
  <p><strong>Expected Result</strong></p>
  
  <p>Now I would like to retrieve all users with all their attributes. The attributes should be returned as json object in a separate column. For example:</p>
  
  <pre><code>name, data
  Florian, { "age":23, "color":"blue" }
  Markus, { "age":24, "color":"green" }
  </code></pre>
  
  <p><strong>My Approach</strong></p>
  
  <p>Now my problem is, that I couldn't find a way to create a key-value pair in postgres. I tried following:</p>
  
  <pre><code>SELECT
    name,
    array_to_json(array_agg(row(d.key, d.value))) AS data
  FROM meta AS m
  JOIN (
    SELECT d.fk_id, d.key, d.value AS value FROM data AS d
    ) AS d
  ON d.fk_id = m.id
  GROUP BY m.name;
  </code></pre>
  
  <p>But it returns this as data column:</p>
  
  <pre><code>[{"f1":"age","f2":24},{"f1":"color","f2":"blue"}]
  </code></pre>
  
  <p><strong>Other Solutions</strong></p>
  
  <p>I know there is the function <code>crosstab</code> which enables me to turn the data table into a key as column and value as row table. But this is not dynamic. And I don't know how many attributes a person has in the data table. So this is not an option.</p>
  
  <p>I could also create a json like string with the two row values and aggregate them. But maybe there is a <em>nicer</em> solution.</p>
  
  <p>And no, it is not possible to change the data-model because the real data model is already in use of multiple parties.</p>
  
  <p><strong>SQLFiddle</strong></p>
  
  <p>Check and test out the fiddle i've created for this question:
  <a href="http://sqlfiddle.com/#!15/bd579/14" rel="nofollow noreferrer">http://sqlfiddle.com/#!15/bd579/14</a></p>