Search code examples
sqljsonpostgresqltypesjsonb

Avoid PostgreSQL type evaluation in CASE statements

I want to select a column key value if this column is a jsonb. If it's not a jsonb, I want to simply select the column.

Here is the code:

SELECT 
    CASE WHEN (pg_typeof(mycolumn)::text LIKE 'jsonb') 
        THEN mycolumn->>'mykey' 
        ELSE mycolumn 
    END
FROM mytable;

It does not work, because somehow the mycolumn->>'mykey' is still evaluated for columns that are not of type JSONB.

ERROR: operator does not exist: [...] ->> unknown

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Is there a way to make PostgreSQL type check accept this statement, or is there any other way to do this?

Solution

  • In general you can do this by casting the query result to text:

    SELECT 
    CASE WHEN (pg_typeof(mycolumn)::text LIKE 'jsonb') 
        THEN (mycolumn::text::jsonb)->>'mykey' 
        ELSE mycolumn::text 
    END
FROM mytable;