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

I think you meant ["foo", "bar", "bat", "dot"], you wrote bar twice here. — fpietka
@e4c5, not yet.. tied up with few other things.. sure will check out and leave the feedback! — Yans
When you post questions here, you are asking people to take the time to read it And then some of those readers will take the time to think and write an answer. Surely the leat that you can do is to allocate some of your own time to try out those answers?? — e4c5

e4c5 e4c5 · Accepted Answer · 2017-01-11T04:00:38

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

Postgresql - distinct values from jsonb array

1 Answers