sqlpostgresqlcolumn-alias

Can we cast and rename a jsonb field in one Postgres SQL query?

I have a field in a jsonb field in a Postgres table and want to do two things directly in the query so we don't have to do cleaning in the next steps (have most of the data cleaning in one place essentially). The two things I want to do are:

  1. Change the field type from string to integer. I know I can do this with

SELECT (answers->'recommend'->> 'score')::int FROM TABLE

  1. Rename a field to a more meaningful one. I can do this with

SELECT answers->'recommend'->>'score' as "Recommendation Score" FROM TABLE,

But I can't find a way to do both in the same query. Is it possible? Given the smaller size of our tables, I'm not concerned about performance yet

Solution

  • The alias needs to go after the cast expression:

    SELECT (answers->'recommend'->> 'score')::int as "Recommendation Score"
FROM ...