title: How to create an empty JSON object in postgresql?
body: <h2>Datamodel</h2>
  
  <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).
  <a href="https://i.stack.imgur.com/UKPGA.png" rel="noreferrer"><img src="https://i.stack.imgur.com/UKPGA.png" alt="Datamodel"></a>
  <em>Simplified data-model</em></p>
  
  <p>Now there is a query to retrieve all users (<strong>name</strong>) with all their attributes (<strong>data</strong>). The attributes are returned as JSON object in a separate column. Here is an example:</p>
  
  <pre class="lang-json prettyprint-override"><code>name      data
  Florian   { "age":25 }
  Markus    { "age":25, "color":"blue" }
  Thomas    {}
  </code></pre>
  
  <p>The SQL command looks like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
    name,
    json_object_agg(d.key, d.value) AS data,
  FROM meta AS m
  JOIN (
    JOIN 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>
  
  <h2>Problem</h2>
  
  <p>Now the problem I am facing is, that users like <strong>Thomas</strong> which do not have any attributes stored in the <strong>key-value</strong> table, are not shown with my select function. This is because it does only a <code>JOIN</code> and no <code>LEFT OUTER JOIN</code>.</p>
  
  <p>If I would use <code>LEFT OUTER JOIN</code> then I run into the problem, that <code>json_object_agg</code> try's to aggregate <code>NULL</code> values and dies with an error.</p>
  
  <h2>Approaches</h2>
  
  <h3>1. Return empty list of keys and values</h3>
  
  <p>So I tried to check if the <strong>key-column</strong> of a user is <code>NULL</code> and return an empty array so <code>json_object_agg</code> would just create an empty <strong>JSON</strong> object.</p>
  
  <p>But there is not really a function to create an empty array in SQL. The nearest thing I found was this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select '{}'::text[];
  </code></pre>
  
  <p>In combination with <code>COALESCE</code> the query looks like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>json_object_agg(COALESCE(d.key, '{}'::text[]), COALESCE(d.value, '{}'::text[])) AS data
  </code></pre>
  
  <p>But if I try to use this I get following error:</p>
  
  <pre><code>ERROR:  COALESCE types text and text[] cannot be matched
  LINE 10:     json_object_agg(COALESCE(d.key, '{}'::text[]), COALES...
                                                  ^
  Query failed
  PostgreSQL said: COALESCE types text and text[] cannot be matched
  </code></pre>
  
  <p>So it looks like that at runtime <code>d.key</code> is a single value and not an array.</p>
  
  <h3>2. Split up JSON creation and return empty list</h3>
  
  <p>So I tried to take <code>json_object_agg</code> and replace it with <code>json_object</code> which does not <strong>aggregate</strong> the keys for me:</p>
  
  <pre class="lang-sql prettyprint-override"><code>json_object(COALESCE(array_agg(d.key), '{}'::text[]), COALESCE(array_agg(d.value), '{}'::text[])) AS data
  </code></pre>
  
  <p>But there I get the error that <code>null value not allowed for object key</code>. So <code>COALESCE</code> does not check that the array is empty.</p>
  
  <h2>Qustion</h2>
  
  <p>So, is there a function to check if a joined column is empty, and if yes return just a simple JSON object?</p>
  
  <p>Or is there any other solution which would solve my problem?</p>