title: Replacement for JSON_ARRAYAGG and JSON_OBJECT from MySQL in v5.6 or PHP
body: <p>I've inherited an old project that runs on MySql 5.6 and Zend FW 2. I'm trying to run a query that will give the same results as JSON_OBJECT() but mysql 5.6 doesn't support that.</p>
  <p>Basically I have 2 tables</p>
  <p>Receipts -</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>receipt_id</th>
  <th>phone</th>
  <th>amount</th>
  <th>status</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>7777777777</td>
  <td>5682</td>
  <td>Success</td>
  </tr>
  <tr>
  <td>2</td>
  <td>8888888888</td>
  <td>4586</td>
  <td>Success</td>
  </tr>
  <tr>
  <td>3</td>
  <td>5555555555</td>
  <td>7589</td>
  <td>Success</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Receipt_Item -</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>receipt_id</th>
  <th>item_id</th>
  <th>quantity</th>
  <th>price</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>23</td>
  <td>5682</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>30</td>
  <td>5682</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>10</td>
  <td>7589</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  <td>23</td>
  <td>4355</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  <td>41</td>
  <td>3665</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And need to get this output</p>
  <pre><code>[
      {
          &quot;receipt_id&quot;: 1,
          &quot;phone&quot;: &quot;7777777777&quot;,
          &quot;amount&quot;: &quot;5682&quot;,
          &quot;status&quot;: &quot;Success&quot;,
          &quot;receipt_item&quot; : [
              {
                &quot;receipt_id&quot; : 1,
                &quot;item_id&quot; : 1,
                &quot;quantity&quot; : 23,
                &quot;price&quot; : 5682
              },
              {
                &quot;receipt_id&quot; : 1,
                &quot;item_id&quot; : 2,
                &quot;quantity&quot; : 30,
                &quot;price&quot; : 5682
              }
  
          ]
      },
      {
          &quot;receipt_id&quot;: 2,
          &quot;phone&quot;: &quot;8888888888&quot;,
          &quot;amount&quot;: &quot;4586&quot;,
          &quot;status&quot;: &quot;Success&quot;,
          &quot;receipt_item&quot; : [
              {
                &quot;receipt_id&quot; : 2,
                &quot;item_id&quot; : 1,
                &quot;quantity&quot; : 10,
                &quot;price&quot; : 7589
              }
          ]
      },
      {
          &quot;receipt_id&quot;: 3,
          &quot;phone&quot;: &quot;5555555555&quot;,
          &quot;amount&quot;: &quot;7589&quot;,
          &quot;status&quot;: &quot;Success&quot;,
          &quot;receipt_item&quot; : [
              {
                &quot;receipt_id&quot; : 3,
                &quot;item_id&quot; : 1,
                &quot;quantity&quot; : 23,
                &quot;price&quot; : 4355
              },
              {
                &quot;receipt_id&quot; : 3,
                &quot;item_id&quot; : 2,
                &quot;quantity&quot; : 41,
                &quot;price&quot; : 3665
              }
  
          ]
      }
  ]
  </code></pre>
  <p>Due to the version I cannot use JSON_OBJECT(). Is there any other way in sql to get this result.</p>
  <p>If not can I post-process this in PHP to combine multiple objects with the same receipt_id and creating an inner array with combined receipt items?</p>
  <p>Essentially turning this:</p>
  <pre><code>[
      [
          &quot;receipt_id&quot;    =&gt; 1,
          &quot;phone&quot;     =&gt; &quot;7777777777&quot;,
          &quot;amount&quot;    =&gt; &quot;5682&quot;,
          &quot;status&quot;    =&gt; &quot;Success&quot;,
          &quot;item_id&quot;     =&gt; 1,
          &quot;quantity&quot;      =&gt; 23,
          &quot;price&quot;     =&gt; 5682
      ],
      [
          &quot;receipt_id&quot;    =&gt; 1,
          &quot;phone&quot;     =&gt; &quot;7777777777&quot;,
          &quot;amount&quot;    =&gt; &quot;5682&quot;,
          &quot;status&quot;    =&gt; &quot;Success&quot;,
          &quot;item_id&quot;     =&gt; 1,
          &quot;quantity&quot;      =&gt; 23,
          &quot;price&quot;     =&gt; 5682
      ],
  ]
  </code></pre>
  <p>into this</p>
  <pre><code>[
      [
          &quot;receipt_id&quot;    =&gt; 1,
          &quot;phone&quot;    =&gt; &quot;7777777777&quot;,
          &quot;amount&quot;    =&gt; &quot;5682&quot;,
          &quot;status&quot;    =&gt; &quot;Success&quot;,
          &quot;receipt_item&quot;     =&gt; [
              [
                &quot;item_id&quot;     =&gt; 1,
                &quot;quantity&quot;     =&gt; 23,
                &quot;price&quot;     =&gt; 5682
              ],
              [
                &quot;item_id&quot;     =&gt; 2,
                &quot;quantity&quot;     =&gt; 30,
                &quot;price&quot;     =&gt; 5682
              ]
  
          ]
      ],
  ]
  </code></pre>
  <p>In PHP I can use <code>array_unique()</code> and <code>array_column()</code>, and then loop through the original array to get the result. But is there an in-built way to get there?</p>
  <p>Thanks.</p>