Question 160882 in Mysql Question posted in
The official MySQL documentation can be found here.

Can MySQL delete a key-value pair in a JSON column?

I am trying to update a column in MySQL, where it contains a json encoded value with multiple key-value pairs, but cannot write the exact query. Can anyone help?

For example:

tableA:

id response
1 {"event_id":"11111","contact":{"contact_id":"as2344","answers": [{"answer_id":"2223e"}],"tags":"cadd76","question_id":"67888"}

From the above row, I am trying to remove "answers" and "tags" from the key "contact", so that the result after the update query will be like below:

{"event_id":"11111","contact":{"contact_id":"as2344","question_id":"67888"}

I cannot find any method as to how to update that column. So please anyone can help?

Tags:

2

Answers


  1. 0

    You will have to do the plain string Manipulation here. Please use below reference to find out functions you need here.

    https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

    Hope this helps you.

    Login or Signup to reply.
  2. 0

    I am trying to remove "answers" and "tags" from the key "contact"

    In MySQL, you can use JSON_REMOVE() for this since version 5.7:

    select json_remove(response, '$.contact.answers', '$.contact.tags') new_response
from t

    Or if you wanted an update statement:

    update t
set response = json_remove(response, '$.contact.answers', '$.contact.tags')

    Demo

    Note that modifying stored JSON in somehow a red-flag that indicate a data model problem; in many cases, it is much easier to properly model your relationships into database tables, and then use standard DML capabilities.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search