How to insert quotes to the key in a query with "JSON_OBJECT"? from mysql

Question

Navigation

#1 by (1 votes)

1

I use this query:

JSON_ARRAYAGG(JSON_OBJECT("id",pd.id_pedido_detalle,"cantidad",pd.cantidad,"precio",pd.precio_pedido))

This gives me the following result:

[{id: 991, cantidad: 1, precio: 10}, {id: 992, cantidad: 1, precio: 20}]

I try to convert it, with javascript , to a json and it throws me the following error:

On this link I use JSON Editor Online , to see the why, and it's because the "" in the keys are missing.

In this link add the quotes to the keys to show that it's just that what's missing.

mysql sql

asked by Pablo Contreras 26.12.2018 в 19:51

source

1 answer

score 1 · Accepted Answer

Since I do not know the structure of your tables, I answer you with the following example

I declare the structure of a table with a column of type JSON

CREATE TABLE demo(
   data JSON NOT NULL
);

Later I insert some values in the column data

INSERT INTO demo(data)
VALUES

('{"backend": true, "frontend": "maybe"}'),
('{"backend": false, "frontend": "si"}');

Now to return those values, I make the following query

SELECT JSON_ARRAYAGG(JSON_OBJECT("backend", data->'$.backend', "frontend", data->'$.frontend')) as D FROM demo;

As you can see I am using the shortened syntax of columnaPrincipal->'$.columnaNombre' to build the JSON that will return my query

Final result

[{"backend": true, "frontend": "maybe"}, {"backend": false, "frontend": "si"}]

Now if you spend it for JSON Validator like: link

You rearrange it like this:

[{
    "backend": true,
    "frontend": "maybe"
}, {
    "backend": false,
    "frontend": "si"
}]

And at the end it indicates that it is a structure JSON valid