Select value of jsonb column in PostgreSQL

postgresql jsonb
Ajoe picture Ajoe · Oct 19, 2016 · Viewed 12.1k times · Source

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

Patrick picture Patrick · Oct 19, 2016

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

Related questions

How to perform update operations on columns of type JSONB in Postgres 9.4

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns. Documentation for JSONB types and functions: http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.…

Read More
Query for array elements inside JSON type

I'm trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], "background":"background.png" } I would …

Read More
Explanation of JSONB introduced by PostgreSQL

PostgreSQL just introduced JSONB and it's already trending on hacker news. It would be great if someone could explain how it's different from Hstore and JSON previously present in PostgreSQL. What are its advantages and limitations and when should someone …

Read More