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.

Michel Milezzi · Accepted Answer · 2017-11-29 11:01:27Z

12

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

edited Nov 29, 2017 at 11:01

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

answered Nov 29, 2017 at 10:20

Cátia Matos

9503 gold badges9 silver badges29 bronze badges

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

Comments

dhananjay · Accepted Answer · 2020-07-24 14:36:47Z

7

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 ;

edited Jul 24, 2020 at 14:36

user330315

answered Jul 24, 2020 at 14:35

dhananjay

711 silver badge1 bronze badge

Collectives™ on Stack Overflow

sum elements of json column postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related