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