Replacement for JSON_ARRAYAGG and JSON_OBJECT from MySQL in v5.6 or PHP

Question

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.

Basically I have 2 tables

Receipts -

receipt_id	phone	amount	status
1	7777777777	5682	Success
2	8888888888	4586	Success
3	5555555555	7589	Success

Receipt_Item -

receipt_id	item_id	quantity	price
1	1	23	5682
1	2	30	5682
2	1	10	7589
3	1	23	4355
3	2	41	3665

And need to get this output

[
    {
        "receipt_id": 1,
        "phone": "7777777777",
        "amount": "5682",
        "status": "Success",
        "receipt_item" : [
            {
              "receipt_id" : 1,
              "item_id" : 1,
              "quantity" : 23,
              "price" : 5682
            },
            {
              "receipt_id" : 1,
              "item_id" : 2,
              "quantity" : 30,
              "price" : 5682
            }

        ]
    },
    {
        "receipt_id": 2,
        "phone": "8888888888",
        "amount": "4586",
        "status": "Success",
        "receipt_item" : [
            {
              "receipt_id" : 2,
              "item_id" : 1,
              "quantity" : 10,
              "price" : 7589
            }
        ]
    },
    {
        "receipt_id": 3,
        "phone": "5555555555",
        "amount": "7589",
        "status": "Success",
        "receipt_item" : [
            {
              "receipt_id" : 3,
              "item_id" : 1,
              "quantity" : 23,
              "price" : 4355
            },
            {
              "receipt_id" : 3,
              "item_id" : 2,
              "quantity" : 41,
              "price" : 3665
            }

        ]
    }
]

Due to the version I cannot use JSON_OBJECT(). Is there any other way in sql to get this result.

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?

Essentially turning this:

[
    [
        "receipt_id"    => 1,
        "phone"     => "7777777777",
        "amount"    => "5682",
        "status"    => "Success",
        "item_id"     => 1,
        "quantity"      => 23,
        "price"     => 5682
    ],
    [
        "receipt_id"    => 1,
        "phone"     => "7777777777",
        "amount"    => "5682",
        "status"    => "Success",
        "item_id"     => 1,
        "quantity"      => 23,
        "price"     => 5682
    ],
]

into this

[
    [
        "receipt_id"    => 1,
        "phone"    => "7777777777",
        "amount"    => "5682",
        "status"    => "Success",
        "receipt_item"     => [
            [
              "item_id"     => 1,
              "quantity"     => 23,
              "price"     => 5682
            ],
            [
              "item_id"     => 2,
              "quantity"     => 30,
              "price"     => 5682
            ]

        ]
    ],
]

In PHP I can use array_unique() and array_column(), and then loop through the original array to get the result. But is there an in-built way to get there?

Thanks.

Answer 1

You can aggregate data on PHP side using array_reduce:

$output = array_reduce(
    $input,
    function($acc, $r) {
        if (!isset($acc[$r["receipt_id"]])) {
            $acc[$r["receipt_id"]] =  [
                "receipt_id"    => $r["receipt_id"],
                "phone"         => $r["phone"],
                "amount"        => $r["amount"],
                "status"        => $r["status"],
                "receipt_item"  => []
            ];
        }
        
        $acc[$r["receipt_id"]]["receipt_item"][] = [
            "item_id"  => $r["item_id"],
            "quantity" => $r["quantity"],
            "price"    => $r["price"]
        ];
        return $acc;
    },
    []
);

run PHP online