2

I have JSONB column in PostgresSQL like following, I want to remove duplicate items

 {
  "other": null,
  "values": [
    "REDUCE_STRESS",
    "LOSE_WEIGHT",
    "INCREASE_ACTIVITY",
    "EAT_HEALTHIER",
    "IMPROVE_SLEEP",
    "EAT_HEALTHIER",
    "INCREASE_ENERGY"
  ]
}
Improve this question

1 Answer 1

Reset to default
9

The easiest thing is to create a function: 

create function remove_dupes(p_array jsonb) 
  returns jsonb
as
$$
select jsonb_agg(distinct e)
from jsonb_array_elements(p_array) as t(e);
$$
language sql;

Then you can use that in an UPDATE statement:

update the_table
   set the_column = jsonb_set(the_column, '{values}', remove_dupes(the_column -> 'values'))
where ...
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments