Select value of jsonb column in PostgreSQL

Question

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.

Answer 1

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

Answer 2

you can use also use the following snippet.

SELECT 
  id, 
  data::json->'name' as name
FROM books;