postgreSQL json函数

1. 将同组数据聚合为一个对象

postgreSQL

select geo,seller,product_name 
, json_agg(json_build_object('date',bucket,'value',pdp_final))
from ludp_pdp 
WHERE version = '20221114' and geo in ( 'PRC','AP','EMEA','NA','LA' ) and country='AE' and seller= 'AE_Others' and product ='PAU20000AE' and bucket >= '2022-10-01' and bucket <= '2023-03-31' group by geo,seller,product_name order by geo,seller,product_name

mysql

select geo,seller,product_name 
, concat("[", GROUP_CONCAT(concat( "{\"date\":\"", bucket, "\",\"value\":", pdp_final, "}" )), "]") as aggComplex 
from catalog_pdp 
WHERE version = '20221114' and geo in ( 'PRC','AP','EMEA','NA','LA' ) and country='AE' and seller= 'AE_Others' and product ='PAU20000AE' and bucket >= '2022-10-01' and bucket <= '2023-03-31' group by geo,seller,product_name order by geo,seller,product_name

解析json数组中的 json元素

with a as(
	select *
		from catalog_activation_projection_calculation
		where "version" ='2023-01-30'
		and geo = 'LA'
		and product_name ='ARUBA'
)
, b as(

	select jsonb_array_elements(complex)->>'date' as date
		,jsonb_array_elements(complex)->>'value' as value
		from a
)
-- select sum(value :: decimal(12,2)) from  b where date>='2023-04-03' and date<='2023-12-25'
select c.start_date, sum(value :: decimal(12,2))
from b
left join sc_mbg_calendar_cml c
on b.date=c.start_date
where date>='2023-04-03' and date<='2023-12-25'
group by c.start_date
order by c.start_date