Select value of array from 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.

This might help you. dba.stackexchange.com/questions/102556/… — Srivatsa N
– Srivatsa N, Commented Oct 19, 2016 at 5:09

Patrick · Accepted Answer · 2016-10-19 05:59:05Z

34

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

answered Oct 19, 2016 at 5:59

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

3 Comments

Ajoe Over a year ago

If I need to add a where condition with this jsonb data type ,what shall I do? Eg. select * from documents where tags->'Tag' = 'Suite'

Patrick Over a year ago

If I understand your question correctly, then the answer is no, because you can not directly access computed field values in the select list as filter conditions. You would either need to repeat the function calls or - probably easier in your case - use the above as a sub-query and then filter in the main query. In either case, you should use the ->> operator to get the text version of the JSON object.

Ajoe Over a year ago

SELECT * FROM documents, jsonb_array_elements( documents.tags ) AS elem WHERE elem->>'Tag' = 'Suite'; From this I got the required result. Thank you

epox · Accepted Answer · 2023-03-20 15:45:56Z

19

since Postgres 9.6+

SELECT DISTINCT
  Tags->>'Tag'
FROM Documents;

answered Mar 20, 2023 at 15:45

epox

11.4k2 gold badges67 silver badges43 bronze badges

2 Comments

Maarten Kieft Over a year ago

Best answer imho, also drops the quotes around the actual value

Bergi Nov 27 at 15:06

This does not work with the OP example where tags is an array

epox · Accepted Answer · 2023-03-20 15:19:55Z

12

you can also use the following snippet.

SELECT DISTINCT
  Tags::json->'Tag'
FROM Documents;

edited Mar 20, 2023 at 15:19

epox

11.4k2 gold badges67 silver badges43 bronze badges

answered Dec 28, 2020 at 17:27

Mohsin Mahmood

3,4843 gold badges24 silver badges26 bronze badges

1 Comment

Bergi Nov 27 at 15:08

This does not work at all, and even with jsonb does not work with the OP example where tags is an array

Collectives™ on Stack Overflow

Select value of array from jsonb column in PostgreSQL

3 Answers 3

3 Comments

since Postgres 9.6+

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

since Postgres 9.6+

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related