Select value of jsonb column in PostgreSQL

jsonb, postgresql

Solution

The shortest version would be:

SELECT DISTINCT value->'Tag' AS tag
FROM Documents, jsonb_array_elements(Documents.Tags);

The `jsonb_array_elements()` function unnests the JSONB array into a set of rows with a single column called "value". It uses an implicit "lateral join" on the `Documents` table.

This gives you the distinct tags as `jsonb` values. If you want them as a `text` value, use the `->>` operator instead of `->`.

Problem

I have a table 'Documents' which has a column 'Tags' with 'jsonb' datatype. Sample data in Tags column ``` [{"Tag": "Social Media"}, {"Tag": "Adobe Creative"}] [{"Tag": "Interactive"}] [{"Tag": "Web 2.0"}, {"Tag": "Adobe Creative"},{"Tag": "Suite"}] ``` I need to get the distinct values of "Tag" like ``` Social Media Adobe Creative Interactive Web 2.0 Suite ``` I am new in PostgreSQL.

Original source