How can I convert a jsonb nested string field to int in postgresql?

Question

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?

Matt · Accepted Answer · 2021-10-16 13:17:12Z

1

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

answered Oct 16, 2021 at 13:17

Matt

20.8k1 gold badge61 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1