MySQL problem generating json_object without comma

Question

I'm trying to convert query into a json file but it does not includes comma at the end of each row.

Query

SELECT json_object("NumeroDetallePedido",d.idpedido,"PrecioUnidad",d.preciounidad,"Cantidad",d.cantidad,"Descuento",d.descuento, "Producto",json_array("NombreProducto",pro.nombreProducto),"Pedidos",json_array("FechaPedido",pe.FechaPedido)) 
  FROM detallesdepedidos d
  JOIN productos pro 
    ON d.idproducto = pro.idproducto 
  JOIN pedidos pe 
    ON d.idpedido = pe.IdPedido 
  INTO OUTFILE '/var/lib/mysql-files/detallespedido_v4.json'

Result {"Pedidos": ["FechaPedido", "1994-09-20"], "Cantidad": 45, "Producto": ["NombreProducto", "Té Dharamsala"], "Descuento": 0, "PrecioUnidad": 14, "NumeroDetallePedido": 10285} {"Pedidos": ["FechaPedido", "1994-09-30"], "Cantidad": 18, "Producto": ["NombreProducto", "Té Dharamsala"], "Descuento": 0, "PrecioUnidad": 14, "NumeroDetallePedido": 10294} {"Pedidos": ["FechaPedido", "1994-10-31"], "Cantidad": 20, "Producto": ["NombreProducto", "Té Dharamsala"], "Descuento": 0, "PrecioUnidad": 14, "NumeroDetallePedido": 10317}

it's not separated by commas ! Any ideas?

Thank u

At the end of each row, the result it's making differents json_objects and I looking for an jsonArray with all the results — Ruben Ramos
– Ruben Ramos, Commented Feb 7, 2020 at 4:34

Vikram Jain · Accepted Answer · 2020-02-07 05:14:08Z

2

Please, try with below query using JSON_ARRAYAGG:

SELECT 
JSON_ARRAYAGG(
  json_object(
        "NumeroDetallePedido", d.idpedido, "PrecioUnidad", d.preciounidad, "Cantidad", d.cantidad, "Descuento", d.descuento, "Producto",
        json_array("NombreProducto", pro.nombreProducto), "Pedidos", json_array("FechaPedido",pe.FechaPedido)
    ) 
)
FROM detallesdepedidos d 
INNER JOIN productos pro ON d.idproducto = pro.idproducto 
INNER JOIN pedidos pe ON d.idpedido = pe.IdPedido)

answered Feb 7, 2020 at 5:14

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ruben Ramos Over a year ago

I had and error, FUNCTION neptuno.JSON_ARRAYAGG does not exist.

Vikram Jain Over a year ago

What's version of mysql.

Vikram Jain Over a year ago

MySQL 5.7.22 and later supports two aggregate JSON functions JSON_ARRAYAGG() and JSON_OBJECTAGG()

Ruben Ramos · Accepted Answer · 2020-02-07 04:50:50Z

0

select concat('[', GROUP_CONCAT( JSON_OBJECT(
'name_field', name_field ,'address_field', address_field ,'contact_age', contact_age ) SEPARATOR ',') ,']') from contact;

I was looking for this.

answered Feb 7, 2020 at 4:50

Ruben Ramos

313 bronze badges

Collectives™ on Stack Overflow

MySQL problem generating json_object without comma

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related