title: Postgresql json_array_elements
body: <p>I have 2 tables:</p>
  
  <p>table groups - id (bigserial), name (varchar), mails (json)</p>
  
  <p>table mails - id (bigserial), name (varchar)  </p>
  
  <p>My Data in groups</p>
  
  <pre><code>1, en-mails, [{"id" : 1}, {"id" : 2}]
  2, fr-mails, [{"id" : 3}, {"id" : 4}]
  </code></pre>
  
  <p>My Data in mails</p>
  
  <pre><code>1, mail1@gmail.com
  2, mail2@gmail.com
  3, mail3@gmail.com
  4, mail4@gmail.com
  </code></pre>
  
  <p>My Query:</p>
  
  <pre><code>SELECT tg.name, tm.mail
      FROM groups as tg
      CROSS JOIN LATERAL json_array_elements (tg.mails :: json) group_mails
      LEFT OUTER JOIN mails as tm ON (group_mails -&gt;&gt; 'id') :: BIGINT = tm.c_id
  </code></pre>
  
  <p>My Result</p>
  
  <pre><code>Array ( [name] =&gt; en-mails [mail] =&gt; mail1@gmail.com )
  Array ( [name] =&gt; en-mails [mail] =&gt; mail2@gmail.com )
  Array ( [name] =&gt; fr-mails [mail] =&gt; mail3@gmail.com )
  Array ( [name] =&gt; fr-mails [mail] =&gt; mail4@gmail.com )
  </code></pre>
  
  <p>My Question - how query return:</p>
  
  <pre><code>Array ( [name] =&gt; en-mails [mail] =&gt; [mail1@gmail.com, mail2@gmail.com] )
  Array ( [name] =&gt; fr-mails [mail] =&gt; [mail1@gmail.com, mail2@gmail.com] )
  </code></pre>
  
  <p>Thanks in advance</p>