1

I have a table that contains product price and stock by size and color. I put them in JSON format. Table structure like

id | chart
---+--------------------------------------------------------------+
1  | [{"size":"XL", "color":"Red", "stock":"30", "price":"300"},  |
   |  {"size":"XXL", "color":"Red", "stock":"40", "price":"400"}, |
   |  {"size":"XXL", "color":"Blue", "stock":"35", "price":"450"}]|
-------------------------------------------------------------------

I want to update the price value to 350, If the size match with XL and the Color with Red. I tried this code,

UPDATE t
SET chart = REGEXP_REPLACE(
  JSON_EXTRACT(chart, '$'), 
  JSON_OBJECT("size", "XL", "color", "Red", "stock", "([0-9]+)", "price", "([0-9]+)"),
  JSON_OBJECT("size", "XL", "color", "Red", "stock", "$1", "price", "350")
)
WHERE id = 1;

It executes successfully. But the main problem is stock value changes with $1 string. Here it should be updated with previous stock value which is unknown to me.

Md Jahid Khan Limon
  • 194
  • 1
  • 10
  • Any chance the schema could be refactored so the tables are at least in a 1st normal form? – Serg May 24 '22 at 07:03
  • @Serg I'm sorry, I can't understand. Please write in details – Md Jahid Khan Limon May 24 '22 at 07:09
  • There are JSON functions for modifying JSON data, don't use a regular expression. – Barmar May 24 '22 at 07:09
  • @Barmar can you please solve this problem with JSON function? – Md Jahid Khan Limon May 24 '22 at 07:10
  • 2
    See https://stackoverflow.com/questions/55809822/mysql-search-json-value-by-key-in-array for how to find an object in a JSON array by key value. Then use `JSON_REPLACE()` to replace the price. – Barmar May 24 '22 at 07:13
  • I'm not going to try to solve it myself because the JSON functions are too confusing. I would never think of using the JSON datatype because this is so hard. – Barmar May 24 '22 at 07:14
  • 2
    I advice creating a separate table `Chart(productId, size, color, stock, price)` to store your data. This way you can easilly update whatever you want. – Serg May 24 '22 at 07:16
  • JSON_TABLE > REPLACE + WHERE > JSON_ARRAYAGG – Akina May 24 '22 at 07:34

0 Answers0