Backslash in json_encode (PHP) + JSON_ARRAYAGG (mySQL)

Question

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"
  }
]

Answer 1

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