Remove duplicates from a Jsonb array in Postgres-CodePudding

I have a table in postgres having column web with jsonb type. It contains duplicate values like below. I want to remove all duplicates from the array. Can you please help.

Jsonb column only has values like below 2 rows

["test.com","test.com","abc.com"]
["google.com","fb.com","google.com"]

Required output

["test.com","abc.com"]
["google.com","fb.com"]

CodePudding user response：

There is nothing built-in, but it's easy to write a function:

create function unique_elements(p_input jsonb)
  returns jsonb
as
$$
  select jsonb_agg(distinct t.element)
  from jsonb_array_elements(p_input) as t(element);
$$
language sql
immutable;

Then use it like this:

select unique_elements(web)
from the_table;