MariaDB Remove text from record

Question

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

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

Answer 1

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'))