How to remove duplicate items from array which's inside JSONB column in PostgreSQL?

Question

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"
  ]
}

user330315 · Accepted Answer · 2020-04-21 18:21:44Z

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 ...

answered Apr 21, 2020 at 18:21

user330315

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

Collectives™ on Stack Overflow

How to remove duplicate items from array which's inside JSONB column in PostgreSQL?

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