Output results of a MySQL query in JSON by nesting results of a sub query

Question

A table is being called from nodeJS and I get the result from the table correctly:

            ymvdc_producto   |   ymvdc_imagenes_producto
            ----------------------------------------------
            product 1        |      image product 1 1
                             |      image product 1 2
                             |      image product 1 3
                             |
            product 2        |      image product 2 2
                             |      image product 2 3 

The result of the table should be the above and the query is

        export function listProductos(req: Request, res: Response) {
            //const id_producto = MySQL.instance.conn.escape(req.params.id_producto);
            const objeto = "";
            const query = `SELECT a.nombre_producto,b.id_imagen_producto  FROM ymvdc_producto a
        INNER JOIN ymvdc_imagenes_producto b ON a.id_producto = b.id_producto)`;
        
            MySQL.ejecutarQuery(query, objeto, (error: any, productos: Productos[]) => {
            if (error) {
                res.status(400).json({
                ok: false,
                warning: "error",
                });
            } else {
                res.json({
                ok: true,
                productos: productos,
                });
            }
            });
        }

However, I need the JSON output in res.json to be of the type:

        {
            "ok": true,
            "productos": [{
                    "nombre_producto": "Producto concreto",
                    "imagenes": [{
                        "id_imagen_producto": "1",
                        "imagen_producto": "imagen 1"
                    }, {
                        "id_imagen_producto": "2",
                        "imagen_producto": "imagen 2"
                    }]
                },
                {
                    "nombre_producto": "Producto concreto 2",
                    "imagenes": [{
                        "id_imagen_producto": "1",
                        "imagen_producto": "imagen 1"
                    }, {
                        "id_imagen_producto": "2",
                        "imagen_producto": "imagen 2"
                    }]
                }
            ]
        }

This is the interface:

 export interface Productos {
   id_producto?: string;
   id_usuario: string;
   nombre_producto: string;
   descripcion_producto: string;
   id_categoria: string;
   id_subcategoria: string;

   id_imagen_producto: [];
   imagen_producto: [];
 }

How should I create the response? Any suggestion?

Answer 1

It seems that you are asking for formatting that is not available in SQL. SELECT can not (or at least not easily) produce what you show in the ymvdc_producto column, namely duplicate values blanked out.

If that is the issue being asked for in this Question, plan on doing it in your Javascript code after receiving the results from the database.

A compromise:

SELECT ymvdc_producto,
       GROUP_CONCAT(ymvdc_imagenes_producto)
    FROM ...
    GROUP BY ymvdc_producto

That will give you

     ymvdc_producto   |   ...
     ----------------------------------------------
     product 1        |   image product 1 1,image product 1 2,image product 1 3
     product 2        |   image product 2 2,image product 2 3 

You may find it easier to use GROUP_CONCAT, then split on comma to build the preferred output.