sum elements of json column postgres

Question

I'm trying to sum all my elements of a specific type in a json column of the last 24 hours and I can't figure out my query.

So I have a json column called data and i want the "live" key to have the sum of all "live" in the last 24 hours and for that I was trying to do something like this

select sum(data->>'live'::json) 
from probing 
where device_id='f051b333-8f1f-4e65-9acc-e76470a87f47' 
  and timestamp > current_timestamp - interval '1 day' 
order by timestamp desc;

I know this doesn't work and I can't figure a viable solution to this. I saw some solutions including using "json_array_elemens" or "json_each" but can't make this work.

Answer 1

I came with this solution:

select 
    sum(cast(data->>'live' as integer)) 
from 
    probing 
where 
    device_id='f051b333-8f1f-4e65-9acc-e76470a87f47' 
    and timestamp > current_timestamp - interval '1 day';