I'm trying to come up with a sql query that shows the client information as well as their orders.

this is the desired result:

{
    "success": true,
    "client": {
        "name": "General Kenobit",
        "email": "[email protected]",
        "contact": 123456789,
        "registerDate": "2022-04-06T16:00:05.000Z",
        "status": "activo",
        "orders": [
            {
                "orderId": 1002,
                "total": 19.5,
                "payment": "money",
                "products": [
                    {
                        "productId": 1,
                        "product": "Test",
                        "quantity": 4
                    }
                ]
            },
            {
                "orderId": 1006,
                "total": 67.5,
                "payment": "money",
                "products": [
                    {
                        "productId": 1,
                        "product": "Test",
                        "quantity": 4
                    }
                    {
                        "productId": 2,
                        "product": "Product 2",
                        "quantity": 3
                    }
                ]
            },
            {
                "orderId": 1009,
                "total": 134,
                "payment": "card",
                "products": [
                    {
                        "productId": 1,
                        "product": "Test",
                        "quantity": 4
                    }
                    {
                        "productId": 2,
                        "product": "Product 2",
                        "quantity": 4
                    }
                    {
                        "productId": 3,
                        "product": "Food",
                        "quantity": 5
                    },

                ]
            }
        ]
    }
}

and this is is query I'm trying to solve

SELECT c.name, c.email, c.contact, c.registerDate, c.status,
CONCAT('[',
    GROUP_CONCAT(JSON_OBJECT("orderId", o.orderId, "total", o.total, "payment", o.payment, "products",
        CONCAT('[', GROUP_CONCAT(JSON_OBJECT("productId", p.productId, "product", p.product, "quantity", op.quantity) SEPARATOR ','), ']')
    ) SEPARATOR ','),
']') AS 'orders'
FROM t_client AS c
INNER JOIN t_order AS o ON o.email = c.email
INNER JOIN t_orderproduct AS op ON op.orderId = o.orderId
INNER JOIN t_product AS p ON p.productId = op.productId
WHERE c.clientId = 1
GROUP BY c.clientId

If I use the group_concat function before the second json_object I get error #1111 for invalid use of grouping function (group)... Otherwise this is what it comes back as result:

{
    "success": true,
    "client": {
        "name": "General Kenobit",
        "email": "[email protected]",
        "contact": 123456789,
        "registerDate": "2022-04-06T16:00:05.000Z",
        "status": "activo",
        "orders": [
            {
                "orderId": 1002,
                "total": 19.5,
                "payment": "money",
                "products": [
                    {
                        "productId": 1,
                        "product": "Test",
                        "quantity": 4
                    }
                ]
            },
            {
                "orderId": 1006,
                "total": 67.5,
                "payment": "money",
                "products": [
                    {
                        "productId": 1,
                        "product": "Test",
                        "quantity": 4
                    }
                ]
            },
            {
                "orderId": 1009,
                "total": 134,
                "payment": "card",
                "products": [
                    {
                        "productId": 1,
                        "product": "Test",
                        "quantity": 4
                    }
                ]
            },
            {
                "orderId": 1006,
                "total": 67.5,
                "payment": "money",
                "products": [
                    {
                        "productId": 2,
                        "product": "Product 2",
                        "quantity": 3
                    }
                ]
            },
            {
                "orderId": 1009,
                "total": 134,
                "payment": "card",
                "products": [
                    {
                        "productId": 2,
                        "product": "Product 2",
                        "quantity": 4
                    }
                ]
            },
            {
                "orderId": 1009,
                "total": 134,
                "payment": "card",
                "products": [
                    {
                        "productId": 3,
                        "product": "Food",
                        "quantity": 5
                    }
                ]
            }
        ]
    }
}

I turned the whole query upside down already and don't know where else to tweak. Any suggestion or tip is appreciated.

CodePudding user response：

You can't have nested aggregations in a query, so you need to do the aggregation of the order products in a subquery.

And instead of CONCAT() and GROUP_CONCAT(), you can use JSON_ARRAYAGG() if you're running at least 5.7.22.

SELECT c.name, c.email, c.contact, c.registerDate, c.status,
       JSON_ARRAYAGG(JSON_OBJECT("orderId", o.orderId, "total", o.total, "payment", o.payment, "products", op.products)) AS orders
FROM t_client AS c
INNER JOIN t_order AS o ON o.email = c.email
INNER JOIN (
      SELECT op.orderId, JSON_ARRAYAGG(JSON_OBJECT("productId", p.productId, "product", p.product, "quantity", op.quantity)) AS products
      FROM t_orderproduct AS op 
      INNER JOIN t_product AS p ON p.productId = op.productId
      GROUP BY op.orderId
) AS op ON op.orderId = o.orderId
WHERE c.clientId = 1
GROUP BY c.clientId