json

//查询时间偏移
select * from  coiner where created_at + interval '8 hour' > '2023-01-13 00:00:00'

//jsonb 查询 attribute.Award.type = 'ttc'
attribute -> 'Award' ->> 'type' = 'ttc


//时间偏移去重分组
select count(distinct(user_id) ),to_char(created_time + interval '7 hour', 'YYYY-MM-DD') as dd
from ramadan_user_checkin_log
where created_time > '2023-04-01'
group by dd


--时间偏移 json内容sum
select 
 sum((attribute ->> 'deduct_ttc')::NUMERIC) as deduct_ttc,
 to_char(created_time + interval '8 hour', 'YYYY-MM-DD') as dd 
from bean_records 
where come_from = 'recharge' and status = 'add_bean_success' and qa = 'false' 
group by dd;


jsonb_array_elements  用于将数组内容展开，原本一行数据会扩展为多行，每行使用数组中一个元素。 即数组有N个元素，则展开为N行。 

需要对数组内容agg处理时，可以根据唯一ID进行group