Joining two tables with foreign keys stored in JSON data type

I have two tables which I want to join, the foreign key used to join the "colors" table is stored in JSON (in an array), is this possible in any way? Or will I have to try and figure it out in JavaScript with a loop?

FYI : I am using mysql2 with express and nodejs.

Table 1, Products:

(
product_id INT
product_name VARCHAR
product_colors JSON
product_price FLOAT
)

Table 2, Colors :

(
color_id: INT
color_name VARCHAR
color_description VARCHAR
)

Example of current 'SELECT * FROM products':

[{
product_id: 1,
product_name: 'name of product',
product_colors: [2, 42, 12, 9],
product_price: 12.95
}]

Example of desired output:

[{
product_id: 1,
product_name: 'name of product',
product_colors: [{color_id: 2, color_name: 'Velvet', color_description: 'A reddish-purple color'}, {color_id: 7, color_name: 'Navy', color_description: 'A darker shade of blue similar to the ocean.'}],
product_price: 12.95
}]

Solution

You should not store an array of foreign keys. That's not a valid relational data design.

The relationship between products and colors in your database is many-to-many, and any such relationship is best stored in a third table:

CREATE TABLE ProductColors (
  product_id INT,
  color_id INT,
  PRIMARY KEY (product_id, color_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id),
  FOREIGN KEY (color_id) REFERENCES Colors(color_id)
);

Each product/color pairing is stored in its own individual row in this third table.

Then you can write a query to return the result in the format you want:

SELECT p.product_id,
 p.product_name,
 JSON_ARRAYAGG(JSON_OBJECT(
  "color_id": c.color_id,
  "color_name": c.color_name,
  "color_description": c.color_description
 )) AS product_colors,
 p.product_price
FROM Products AS p
JOIN ProductColors USING (product_id)
JOIN Colors AS c USING (color_id)
GROUP BY p.product_id;

Store the data in a normalized format. You can generate JSON results using JSON functions.

P.S.: Don't use the FLOAT data type for currency, use NUMERIC(9,2). Read https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html