CODEWITHSUNDEEP
sqlpostgresqljpa
Ajoe
Ajoe

Reputation: 1545

cast a jsonb value to varchar in postgresql

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:

Upvotes: 7

Views: 15557

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 13

Related Questions