Query to remove duplicated Json Objects from a Json Object Array in MySQL-CodePudding

{
    "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?

CodePudding user response：

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.