34 lines
771 B
SQL
34 lines
771 B
SQL
SELECT
|
|
gset.cust
|
|
,gset.vers
|
|
,gset.chan
|
|
,gset.nurs
|
|
,gset.ghse
|
|
,gset.mold
|
|
,gset.v1ds
|
|
,gset.v0ds
|
|
,je.k
|
|
,seas.*
|
|
FROM
|
|
rlarp.price_pool_dev p
|
|
LEFT JOIN LATERAL jsonb_to_record(p.gset) AS gset(
|
|
chan text
|
|
,mold text
|
|
,v1ds text
|
|
,v0ds text
|
|
,cust text
|
|
,vers text
|
|
,nurs text
|
|
,ghse text
|
|
) ON TRUE
|
|
LEFT JOIN LATERAL jsonb_each(p.season) je(k,v) on true
|
|
LEFT JOIN Lateral jsonb_to_record(je.v) as seas(
|
|
units numeric
|
|
,sales_usd numeric
|
|
,price_usd numeric
|
|
) ON TRUE
|
|
WHERE
|
|
gset @> '{"mold":"TFR001G0","v0ds":"BASE"}'
|
|
AND agglevel ?| array['chan', 'mold', 'v0ds']
|
|
AND NOT agglevel ?| array['cust','ghse'];
|