Delete duplicate rows based on json value

Question

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.

user330315 · Accepted Answer · 2018-02-05 15:53:54Z

1

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

answered Feb 5, 2018 at 15:53

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete duplicate rows based on json value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related