Select Distinct Array Values from Postgresql Table

Question

I have a PostgreSQL database table which looks like this

content
title          category              content
Hello World    ["Great"]             This is the content
Learn More     ["Learn", "Great"]    Learn about things

I know this is not the best way to store data like that, but it can't be changed at this point.

I want to get an array of unique categories in one query, some thing like this:

SELECT DISTINCT category FROM content

And end up with an array like this:

["Great", "Learn"]

I know it would be easy if the categories were in a separate table, but how would you do it if they are nested like this?

category is JSONB formatted.

Answer 1

In Postgres, you can use array functions like;

select distinct unnest(category) as nestCategory from content

PostgreSQL Array Functions