CODEWITHSUNDEEP
jsonpostgresql
Cátia Matos
Cátia Matos

Reputation: 930

sum elements of json column postgres

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.

Upvotes: 7

Views: 9641

Answers (2)

dhananjay
dhananjay

Reputation: 71

U can query in both the ways:

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

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

Upvotes: 7

Cátia Matos
Cátia Matos

Reputation: 930

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';

Upvotes: 12

Related Questions