Postgresql - distinct values from jsonb array

Question

I have the following jsonb array column (tags)

|name | tags (jsonb)   |
--------------------------------
|john | [ "foo", "bar" ]
|smith| [ "bar", "bat" ]
|adam | [ "foo", "dot" ]

How to get the distinct tags as follows ["foo", "bar", "bat", "dot"] ?

Answer 1

This will solve your particular problem.

SELECT DISTINCT tag FROM
  (SELECT name, JSONB_ARRAY_ELEMENTS(tags) as b FROM my_table) AS foo;

however you have a bigger problem. Storing tags like this is a mistake that's repeated far too often. You should normalize your table. Please see Django JSONField inside ArrayField

Answer 2

The accepted solution works, but can be simplified:

SELECT DISTINCT JSONB_ARRAY_ELEMENTS_TEXT(tags) AS tag FROM yourtable;

Also, while it's true that this might be an anti-pattern in many cases, there are still valid uses for this, as long as you are aware of its drawbacks (joining and searching will be more difficult).