mariadbrecordlongtext

MariaDB Remove text from record

I have longtext data in data column as show by image, how to remove

{"current_id_position":"8861","targetted_id_position":"","count_new_technical":"0"}

for all record in 1 query? I just need number inside targetted_id_position

enter image description here

Thanks, hope u're helping me. I'm so confused what should I do.

Solution

  • Since the column contains JSON, you can use the JSON extraction operator.

    UPDATE tableName
SET data = data->>'$.targetted_id_position'

    MariaDB didn't copy MySQL's ->> operator, so you have to call the functions instead.

    UPDATE tableName
SET data = JSON_UNQUOTE(JSON_EXTRACT(data, '$.targetted_id_position'))