cast a jsonb value to varchar in postgresql

Question

I have a query like

SELECT DISTINCT (value->'Tag') AS tagname 
FROM documents, 
     jsonb_array_elements(documents.Tags)

so the tagname is a jsonb value. I need to cast that tagname to varchar. How do I do that cast?

sample data in Tags column in documents table is

'[{"Tag":"Web 2.0"},{"Tag":"Adobe Creative"},{"Tag":"Suite"}]'

I am working on java jpa. I tried the native query as

Query query = this.em.createNativeQuery(
            "select distinct (value->'Tag')::varchar as tags from documents, jsonb_array_elements(documents.Tags)");

but its showing

Not all named parameters have been set:

score 13 · Accepted Answer · 2017-09-26 06:50:08Z

13

No need for a cast, simply use the ->> operator which returns the value as text

SELECT DISTINCT value ->> 'Tag' AS tagname 
FROM documents, 
     jsonb_array_elements(documents.tags);

As you might need a cast for something different someday:

:: isn't the only way to cast a value (and which is what JPA chokes on). You can also use the standard cast() operator:

SELECT DISTINCT cast(value -> 'Tag' as varchar) AS tagname

But the ->> operator is the better choice here.

edited Sep 26, 2017 at 6:50

answered Sep 26, 2017 at 6:11

user330315

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

Collectives™ on Stack Overflow

cast a jsonb value to varchar in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related