{
"tr_no": "2",
"actions": [
{
"link_type": "1"
"tr_link": "www.abc.com"
},
{
"link_type": "1"
"tr_link": "www.def.com"
},
{
"link_type": "1"
"tr_link": "www.abc.com"
}
]
}
How can I remove the duplicated "actions" objects by considering "tr_link" using a MySQL query and update the all records in the table that this column has?
Tested on MySQL 8.0.29:
with cte (actions) as (
select json_arrayagg(json_object('link_type', link_type, 'tr_link', tr_link))
from (
select distinct j.* from mytable
cross join json_table(data, '$.actions[*]' columns(
link_type varchar(10) path '$.link_type',
tr_link varchar(50) path '$.tr_link')) as j
) as d
)
update mytable cross join cte
set data = json_set(data, '$.actions', cte.actions);
Re your comments:
I tested the query above after changing the column to LONGBLOB, and I got this error:
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.
So it appears you can't use MySQL's built-in JSON functions on binary data.
This explains why your sample JSON in your question above is invalid JSON format (lacking commas between object fields). If you were using MySQL's JSON data type, you could not store invalid JSON.
So it seems you can't use SQL to eliminate the duplicates. You should fetch the whole JSON document into a client application, deserialize it, remove duplicates with whatever code you want, and then re-serialize it to JSON (hopefully valid JSON this time), and save it back to the database.