I have a "Products" table in MySQL (5.7.10) that contains many products. I want to include into "Products" table a field called HistoricalPrices using JSON datatype.
For the example "Products" table includes the following:
ProductID
ProductName
ProductDesc
CreateDate
Price
HistoricalPrices (json) NEW
HistoricalPrice should include "CreateDate" and "Price" keys in json and so i can add multiple changes of price.
I prefer this method (instead of adding new row per price history) because i just need these prices for reporting.
What i am looking for is the MySQL Query for adding new price changes inside HistoricalPrice field (json)