0

I need to remove duplicated objects in a jsonb field in table in PostgreSQL 12.7. How can I do this for all rows in table?

In this example I need to remove the 3th and 4th object (date: 2022-09-12)

[
  {
    "price": 153.6106,
    "date": "2022-09-14",
    
  },
  {
    "price": 153.6106,
    "date": "2022-09-13",
    
  },
  {
    "price": 153.6106,
    "date": "2022-09-12",
    
  },
  {
    "price": 153.6106,
    "date": "2022-09-12",
    
  },
  {
    "price": 153.6106,
    "date": "2022-09-11",
    
  }
]
2
  • 1
    unnest group by having count(*)=1
    – Jasen
    Commented Sep 16, 2022 at 12:08
  • Can you explain it better? Thank you Commented Sep 16, 2022 at 13:05

1 Answer 1

0

something like this perhaps:

with a as ( select value::text from json_array_elements('[
  {
    "price": 153.6106,
    "date": "2022-09-14"
    
  },
  {
    "price": 153.6106,
    "date": "2022-09-13"
    
  },
  {
    "price": 153.6106,
    "date": "2022-09-12"
    
  },
  {
    "price": 153.6106,
    "date": "2022-09-12"
    
  },
  {
    "price": 153.6106,
    "date": "2022-09-11"
    
  }
]'::json) group by value::text having count(*)=1 ) select array_to_json(array_agg(value::json)) from a; 
      array_to_json       
--------------------------
 [{                      
     "price": 153.6106,  
     "date": "2022-09-13"
                         
   },{                   
     "price": 153.6106,  
     "date": "2022-09-14"
                         
   },{                   
     "price": 153.6106,  
     "date": "2022-09-11"
                         
   }]
(1 row)

Time: 1.010 ms

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.