I am using postgresql 11 and I have a jsonb field.

amount
----------------------------------------
{"value": "3590", "currency": "AUD"}

I can use this syntax to select the nested field value: select amount->'value'.

but I don't know how I can convert it to integer and sum it, like select sum(amount->'value') from

I got this error: HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I tried to convert it by

select sum(amount->'value')::DECIMAL
select sum(TO_NUMBER(amount->'value'))

but none of them working. What is the correct way to do that?

CodePudding user response：

Use the ->> operator to get the value as text: https://www.postgresql.org/docs/11/functions-json.html

SELECT SUM((amount->>'value')::INT)