title: Postgresql coalesce columns with JSON array
body: <p>Let's say that we have the following JSON object:</p>
  
  <pre><code>{"key_to_array":[{"key1":"value1","key2":{"var123":{"xyz":"I_need_version_1"}}},{"key1":"value1","key2":{"htg":{"uy1":"I_need_version_2"}}}]}}
  </code></pre>
  
  <p>inserted into a table:</p>
  
  <pre><code>drop table if exists modeling.modeling.pl_nested_json_array;
  select '{"key_to_array":[{"key1":"value1","key2":{"var123":{"xyz":"I_need_version_1"}}},{"key1":"value1","key2":{"htg":{"uy1":"I_need_version_2"}}}]}' as json_data
  into modeling.modeling.pl_nested_json_array
  from modeling.modeling.some_other_table
  limit 1;
  </code></pre>
  
  <p>In order to get to the <code>I_need_version_*</code> I must travel semi-constant way. 'Semi' means that some elements are varying (keys: <code>var123</code>, <code>xyz</code>, <code>htg</code>, <code>uy1</code>). There is a finite number of their combinations so I can handle them by writing different ways in my code such as:</p>
  
  <pre><code>json_array_elements(cast(json_data as json) -&gt; 'key_to_array') -&gt; 'key2' -&gt; 'var123' -&gt;&gt; 'xyz' as col1
  </code></pre>
  
  <p>The problem is that my current approach:</p>
  
  <pre><code>/* many columns version - unwanted */
  select 
      json_array_elements(cast(json_data as json) -&gt; 'key_to_array') -&gt;&gt; 'key1' as key1,
      json_array_elements(cast(json_data as json) -&gt; 'key_to_array') -&gt; 'key2' -&gt; 'var123' -&gt;&gt; 'xyz' as col1,
      json_array_elements(cast(json_data as json) -&gt; 'key_to_array') -&gt; 'key2' -&gt; 'htg' -&gt;&gt; 'uy1' as col2
  from modeling.modeling.pl_nested_json_array;
  </code></pre>
  
  <p>produces as many columns as there are ways to get to <code>I_need_version_*</code>. Since for each <code>JSON_data</code> only one way is valid (produces <code>I_need_version_*</code>) the rest of the columns <code>col*</code> are NULL, I decided to use <code>COALESCE()</code> in order to get one column <code>col</code> with the non-NULL value:</p>
  
  <pre><code>select 
      json_array_elements(cast(json_data as json) -&gt; 'key_to_array') -&gt;&gt; 'key1' as key1,
      coalesce(
      json_array_elements(cast(json_data as json) -&gt; 'key_to_array') -&gt; 'key2' -&gt; 'var123' -&gt;&gt; 'xyz',
      json_array_elements(cast(json_data as json) -&gt; 'key_to_array') -&gt; 'key2' -&gt; 'htg' -&gt;&gt; 'uy1') as col
  from modeling.modeling.pl_nested_json_array;
  </code></pre>
  
  <p>Upon executing this query I get <code>ERROR: set-returning functions are not allowed in COALESCE</code>.</p>
  
  <p>The output I'm currently getting:</p>
  
  <pre><code>key1          col1               col2
  value1        I_need_version_1   [NULL]
  value1        [NULL]             I_need_version_2
  </code></pre>
  
  <p>What I'd like to get:</p>
  
  <pre><code>key1     col
  value1   I_need_version_1
  value1   I_need_version_2
  </code></pre>