0

For ex I have a table like this, exclusive is jsonb column

enter image description here

I want to select:

Total number of case goinside = 1
Total Number of case type = 1, 2, 3
group by date,id_station,area_type

select date, id_station, area_type, 
COUNT(CAST ( exclusive ->> 'goinside' AS INTEGER ) = 1) as goinside,
COUNT(CAST ( exclusive ->> 'type' AS INTEGER ) = 1) as type_1,
COUNT(CAST ( exclusive ->> 'type' AS INTEGER ) = 2) as type_2,
COUNT(CAST ( exclusive ->> 'type' AS INTEGER ) = 3) as type_3
from test
group by date,id_station,area_type

but the result all is 0, where am I wrong?

Done.
Part II: I select 2 date range to data and use may "filter" as below, and the speed extremely slow. How to set up properly index in this case?

select id_station,area_type,
sum((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) filter (where value ->> 'goinside' = '1' and((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) > 0 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_time,
count(*) filter (where value ->> 'goinside' = '1' and((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) > 0 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num,
sum((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 180 and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 360 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_3to6,
count(*) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 180 and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 360 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_3to6_prev,
sum((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 360 and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 600 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_6to10,
count(*) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 360 and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 600 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_6to10_prev,
sum((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 180 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_less3,
count(*) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 180 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_less3_prev,
sum((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 60 and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 180 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_1to3,
count(*) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 60 and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 180 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_1to3_prev,
sum((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 600 and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 900 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_10to15,
count(*) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 600 and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 900 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_10to15_prev,
sum((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 60 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_less1,
count(*) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) < 60 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_less1_prev,
sum((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 900 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_over15,
count(*) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 900 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_over15_prev,
sum((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 600 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_over10,
count(*) filter (where value ->> 'goinside' = '1' and ((value ->> 'zone1')::int+(value ->> 'zone2')::int+(value ->> 'zone3')::int+(value ->> 'cashiertime')::int+(value ->> 'special')::int) >= 600 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22) as ex_z1z2z3z4z5_num_over10_prev from data_1034 cross join jsonb_array_elements(exclusive) 
where id_station IN (2399,2397) AND ((date >= '2017-10-01' and date <= '2017-10-31' AND hour >= 9 and hour < 22) OR (date >= '2017-08-31' and date <= '2017-09-30' AND hour >= 9 and hour < 22)) 
group by id_station, area_type

explain analyze:

HashAggregate  (cost=1713803.93..1713804.33 rows=40 width=152) (actual time=17560.950..17560.970 rows=37 loops=1)
Group Key: data_1034.id_station, data_1034.area_type
->  Nested Loop  (cost=0.00..18678.68 rows=827900 width=46) (actual time=0.068..616.493 rows=282899 loops=1)
->  Seq Scan on data_1034  (cost=0.00..2120.68 rows=8279 width=821) (actual time=0.047..33.225 rows=10970 loops=1)
Filter: ((id_station = ANY ('{2399,2397}'::integer[])) AND (hour >= 9) AND (hour < 22) AND (((date >= '2017-10-01'::date) AND (date <= '2017-10-31'::date)) OR ((date >= '2017-08-31'::date) AND (date <= '2017-09-30'::date))))
->  Function Scan on jsonb_array_elements  (cost=0.00..1.00 rows=100 width=32) (actual time=0.040..0.044 rows=26 loops=10970)
Planning time: 1.537 ms
Execution time: 17562.512 ms
Improve this question

1 Answer 1

Reset to default
1

The column exclusive is a json array, so you should unnest it with jsonb_array_elements() to get its elements. Also, the count() function is used in improper way.

Use count(*) with filter:

select date, id_station, area_type, 
count(*) filter (where value ->> 'goinside' = '1') as goinside,
count(*) filter (where value ->> 'type' = '1') as type_1,
count(*) filter (where value ->> 'type' = '2') as type_2,
count(*) filter (where value ->> 'type' = '3') as type_3
from test
cross join jsonb_array_elements(exclusive)
group by date, id_station, area_type;

or sum() on boolean expressions cast to integers:

select date, id_station, area_type,
sum((value ->> 'goinside' = '1')::int) as goinside,
sum((value ->> 'type' = '1')::int) as type_1,
sum((value ->> 'type' = '2')::int) as type_2,
sum((value ->> 'type' = '3')::int) as type_3
from test
cross join jsonb_array_elements(exclusive)
group by date, id_station, area_type;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

hi klin, could you help me to check Part II of this question? issue with many "filter"
You should definitely normalize your model. There is no index that could significantly speed up data processing in the current structure. Ask a new question about it.
I made new question here: stackoverflow.com/questions/47390283/…. not use jsonb, every jsonb object is one row now. thank you :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.