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.

8
  • Any chance the schema could be refactored so the tables are at least in a 1st normal form? Commented May 24, 2022 at 7:03
  • @Serg I'm sorry, I can't understand. Please write in details Commented May 24, 2022 at 7:09
  • There are JSON functions for modifying JSON data, don't use a regular expression. Commented May 24, 2022 at 7:09
  • 2
    See stackoverflow.com/questions/55809822/… for how to find an object in a JSON array by key value. Then use JSON_REPLACE() to replace the price. Commented May 24, 2022 at 7:13
  • 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. Commented May 24, 2022 at 7:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.