SELECT
orders.num, orders.createdon, orders.cost, orders.status,
JSON_ARRAYAGG(JSON_OBJECT('name', products.name, 'count', products.count, 'cost', products.cost)) products
FROM `modx_ms2_orders` AS orders
JOIN `modx_ms2_order_products` AS products ON orders.id = products.order_id
WHERE `user_id` = 106
GROUP BY orders.num, orders.createdon, orders.cost, orders.status
Проверить SQL запрос здесь