How to update json data in a column using mysql

Question

My goal is to completely change the listings field

Structure in the database: { "other":{ "price": 0, "isFrozen": false, }, "listings": { "price": 0, "isFrozen": false, "isLocked": false, "pricings": [], "isSalable": false, "listingId": "c2520a55-83fa-4989-a219-ac0efb13bf9a", "isSuspended": false, "lockReasons": [], "merchantSku": "AAAQKIRMIZIL", "dispatchTime": 0 } }

I've been trying something like this but no results I am trying to update according to the information returned from the API. The type of response['listings'][0] is dict.

update products set column_name= JSON_REPLACE(column_name, '$.listings', "+response['listings'][0]+") where id='"+str(id)+"'

You didn't include a diagnostic error message. Are you _sure_ you followed the UPDATE with a COMMIT? — J_H, Jan 20 '23 at 05:38
You asked this question again today, and I voted to mark it as a duplicate of this question. Re-asking the same question is discouraged, even if you don't get answers as quickly as you want. See https://stackoverflow.com/help/no-one-answers — Bill Karwin, Jan 20 '23 at 21:20

score 2 · Accepted Answer · answered Jan 20 '23 at 21:37

Here's a Python fragment that I tested:

doc = json.dumps(response["listings"][0])
id = 1

sql = """
  UPDATE products 
  SET column_name = JSON_SET(column_name, '$.listings', CAST(? AS JSON)) 
  WHERE id = ?
"""

cursor.execute(sql, (doc, id))

cnx.commit()

The trick is that you should serialize your Python dict into a JSON format string, then pass that as a parameter (using a query parameter, not string concatenation) to MySQL. In MySQL, use CAST() to convert the string into a JSON value, then it will preserve the nested structure. If you don't cast it, the JSON will be assigned as a string value that happens to contain commas and quotes and stuff like that, not as a nested JSON object.

P.S.: Please, please, please get into the habit of using query parameters instead of string-concatenation.

I tested it using mysql.connector 8.0.31. Maybe in your version you need to use `%s` instead of `?` as a placeholder. — Bill Karwin, Jan 20 '23 at 22:22

How to update json data in a column using mysql

1 Answers1