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
– fpietka, Commented Jan 11, 2017 at 6:26
@e4c5, not yet.. tied up with few other things.. sure will check out and leave the feedback! — Yans
– Yans, Commented Jan 12, 2017 at 3:23
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, Commented Jan 14, 2017 at 7:26

Community · Accepted Answer · 2017-05-23 12:16:29Z

10

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

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Jan 11, 2017 at 4:00

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

4 Comments

Yans Over a year ago

Thanks for the suggestion, I've normalized the model and looks much cleaner to work with and maintain.

e4c5 Over a year ago

glad to have been of help

Ben Gotow Over a year ago

FYI the normalization suggestion in this answer is a bit out-of-date - now that Postgres supports GIN indexes on JSONB columns (with the crazy fast ? operator for testing for array/keys), storing tags in a JSONB column instead of a join table can help you avoid massive fan-out during queries and is a totally reasonable solution.

e4c5 Over a year ago

On the contrary Ben, you are following a well known SQL anti pattern by putting comma seperated data in a column.

svenema · Accepted Answer · 2021-11-28 09:19:39Z

4

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

answered Nov 28, 2021 at 9:19

svenema

2,6863 gold badges35 silver badges55 bronze badges

Collectives™ on Stack Overflow

Postgresql - distinct values from jsonb array

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related