Remove from array in Postgres

Question

I have a table in postgres having column web with jsonb type. It contains values like below. I want to remove 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"]

this might help - dba.stackexchange.com/questions/226456/… — marmeladze
– marmeladze, Commented Nov 3, 2022 at 6:20

user330315 · Accepted Answer · 2022-11-03 06:56:23Z

1

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;

answered Nov 3, 2022 at 6:56

user330315

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

2 Comments

hero_star Over a year ago

Thanks. This works. can you let me know what is the use of as t(element) here?

user330315 Over a year ago

@hero_star: it's a table alias

Collectives™ on Stack Overflow

Remove from array in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related