How to use %s if we want to add/update key-value pairs in the field which has JSON data type? #966

NikunjVadodariya · 2021-02-22T12:58:01Z

NikunjVadodariya
Feb 22, 2021

Suppose I have a table named company and it has two columns one column is id(Auto increment) and another column is employee_details which has JSON data type. The value of the employee_details is a JSON and it has following properties.

name
email
country

I am using PyMySQL==0.9.3 for generating the query dynamically..I need to add/update name field of the employee_details JSON, So I used following query 10BC0

'UPDATE company SET employee_details=JSON_SET(employee_details, "$.name", %(name)s) WHERE id=1', {"name": "John"}

In above I knew that I want to update name property of the employee_details, but what if I need to add/update dynamically?
Example

Sometimes I only need to update name
Sometimes I need to update name and email
Sometimes I need to update name, email and country
Sometimes I need to update email only
Sometimes I need to update email and country

For above requirements I created below query

If I need to update email and country then below query is generated.
fields_to_update = {"email": "new_email@gmail.com", "country": "US"} -->A dictionary which shows which JSON field to update.
{"properties": '"$.email", "new_email@gmail.com", "$.country": "US"'} --> generated by logical operation using above dict

Query generated by pymysql:
'UPDATE company SET employee_details=JSON_SET(employee_details, %(properties)s) WHERE id=1', {"properties": '"$.email", "new_email@gmail.com", "$.country": "US"'}

But it gives error like Invalid json. May you please suggest what is wrong in above? Is there any alternative way to fulfil my requirements of updating a JSON dynamically.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

How to use %s if we want to add/update key-value pairs in the field which has JSON data type? #966

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Replies: 0 comments

Select a reply

Uh oh!

Uh oh!

How to use %s if we want to add/update key-value pairs in the field which has JSON data type? #966

Uh oh!

Uh oh!

NikunjVadodariya Feb 22, 2021

Replies: 0 comments

NikunjVadodariya
Feb 22, 2021