CODEWITHSUNDEEP
postgresqljsonb
Yans
Yans

Reputation: 307

Postgresql - distinct values from jsonb array

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

Upvotes: 7

Views: 15094

Answers (2)

svenema
svenema

Reputation: 2526

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).

Upvotes: 4

e4c5
e4c5

Reputation: 53734

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

Upvotes: 10

Related Questions