CODEWITHSUNDEEP
sqljsonpostgresqlduplicates
galiolio
galiolio

Reputation: 275

Delete duplicate rows based on json value

I have a table with two columns: id and content.

content is a json field.

I need to delete duplicates on a property in jsons.

Here is an exemple of jsons I have :

{
    "num_aire_ospf": "",
    "name": "name1",
    "rpv": "119"    
}

And here is an exemple of duplicates items I need to delete :

ID    | Content  
--------------------------------------------------------------------------
 1    | {"num_aire_ospf": "", "name": "name1", "rpv": "119" }
 2    | {"num_aire_ospf": "", "name": "name1", "rpv": "119" }
 3    | {"num_aire_ospf": "", "name": "name1", "rpv": "119" }
 4    | {"num_aire_ospf": "", "name": "name1", "rpv": "120" }
 5    | {"num_aire_ospf": "", "name": "name8", "rpv": "150" }
 6    | {"num_aire_ospf": "", "name": "name8", "rpv": "150" }
 7    | {"num_aire_ospf": "", "name": "name8", "rpv": "151" }

I want to delete the first rows with the same names.

In this exemple, I'd like to do a query to delete rows 1,2,3,5,6 and only keep these rows :

ID    | Content  
--------------------------------------------------------------------------    
 4    | {"num_aire_ospf": "", "name": "name1", "rpv": "120" }    
 7    | {"num_aire_ospf": "", "name": "name8", "rpv": "151" }

So only keep the last rows with the same name.

So for 'name1' I need to delete the first 3 rows with 'name1' as name. And for 'name8' I need to delete the first 2 rows with 'name'8 as name.

It has to be dynamic because I have a lot of rows with the same name and I want to keep the last of them.

Upvotes: 0

Views: 1213

Answers (1)

user330315
user330315

Reputation:

There is no such thing as "the last row" (or "first row") in a table of a relational database. From your examples I guess with "last row", you mean the one with the highest id, then you can use this:

delete from data
where id not in (select max(id)
                 from data 
                 group by content ->> 'name')

Upvotes: 1

Related Questions