PostgreSQL | Extract value from jsonb and change its type

I have a view that contains columns from multiple tables, one of these columns is created from a value in a jsonb in another table :

CREATE VIEW my_view AS
SELECT table_1.id AS id, table_2.name AS name,
table_3.metadata::json->>'total_count' AS count
FROM table_1
LEFT JOIN table_2 ON ...

Now I can perfectly extract the value from the jsonb like this without any problem, but the thing is the column count created in my_view is of type text (since it was extracted from a json) but I want it to be of type numeric, is there a way I can do this ? extract and change the type of the value extracted ?

I tried to change the type by altering the view but I found out that we can't do that in postgres so my only solution is by changing the type while extracting it.

Solution

You are already using the :: cast syntax. If you put your extraction in parentheses, you can use it again to change your resulting text into a numeric.

Demo at db<>fiddle

create table table_1(id int);
create table table_2(id int,name text);
create table table_3(id int,metadata text);
insert into table_1 select 1;
insert into table_2 select 1,'name1';
insert into table_3 select 1,'{"total_count":3}';

CREATE VIEW my_view AS
SELECT table_1.id AS id, table_2.name AS name,
table_3.metadata::json->>'total_count' AS count
FROM table_1
LEFT JOIN table_2 using (id)
LEFT JOIN table_3 using (id);

Altering view definition is done with create or replace, but

The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

CREATE OR REPLACE VIEW my_view AS
SELECT table_1.id AS id, table_2.name AS name,
(table_3.metadata::json->>'total_count')::numeric AS count
FROM table_1
LEFT JOIN table_2 using (id)
LEFT JOIN table_3 using (id)

ERROR:  cannot change data type of view column "count" from text to numeric

For type change, it has to be dropped and recreated. To avoid even a minimal time window when concurrent sessions can lose sight of the view, you can wrap the two operations in a single transaction:

begin;

DROP VIEW my_view;
CREATE VIEW my_view AS
SELECT table_1.id AS id, table_2.name AS name,
(table_3.metadata::json->>'total_count')::numeric AS count
FROM table_1
LEFT JOIN table_2 using (id)
LEFT JOIN table_3 using (id);

commit;

Note that it swaps out the view for a new one of the same name, but it's not the same object: anything that was linked to it, will have to also be redefined to use the new one regardless of matching name.