The problem is the backslashes with the quotes in the array colors. I think it’s because the JSON_ARRAYAGG but I don’t know how to print a correct json.
Query:
SELECT a.id_product, JSON_ARRAYAGG(c.name_color) as colors, a.url FROM products as a LEFT JOIN product_has_colors b ON a.id_product = b.id_product LEFT JOIN colors c ON c.id_color = b.id_color GROUP BY a.id_product; +------------+-------------------+-----------------+ | id_product | colors | url | |------------+-------------------+-----------------+ | 1 | ["yellow", "blue"]| https://url.com | | 2 | ["black, "green"] | https://url.com | +------------+-------------------+-----------------+
PHP:
header('Content-Type: application/json); echo json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);
OUTPUT:
[ { "id_product: "1", "colors": ["yellow", "blue"]", "url": "https://url.com" }, { "id_product: "2", "colors": ["black", "green"]", "url": "https://url.com" } ]
Advertisement
Answer
You are encoding colors
twice. First in SQL and then in PHP. What you can do is either decode colors
in PHP before you encode $data
:
foreach ($data as $key = $row) { $data[$key]['colors'] = json_decode($row['colors']); }
If you fetch as objects, then use this:
foreach ($data as $obj) { $obj->colors = json_decode($obj->colors); }
Or generate the JSON completely in SQL:
SELECT JSON_ARRAYAGG(JSON_OBJECT( 'id_product', id_product, 'colors', colors, 'url', url )) as json FROM ( SELECT a.id_product, JSON_ARRAYAGG(c.name_color) as colors, a.url FROM products as a LEFT JOIN product_has_colors b ON a.id_product = b.id_product LEFT JOIN colors c ON c.id_color = b.id_color GROUP BY a.id_product ) x