Destructure Json Array Elements in Continuous Aggregate

TimescaleDB and PostgreSQL
1

This is what I have so far, but timescale complains about the lateral join

CREATE MATERIALIZED VIEW price
WITH (timescaledb.continuous) AS
select
	time, 
	(row ->> 'price_id') as "price_id",
	((row ->> 'price_state')::jsonb ->> 'price')::numeric as "price",
	(row ->> 'conf')::numeric as "conf"
from attribute, jsonb_array_elements(value::jsonb) as row
GROUP BY (time_bucket('00:00:01'::interval, "time")), "time", attribute.value, row;

attribute.value is a json array of variable length of json elements, which I want to destructure into new rows. Thanks!

2

can you please give more details?

what data comes from your hypertable and what comes from this extra thing?

Can you just normalize your query to call:

value::jsonb->>'price_id' ?

3

so attribute.value looks like this:

[{“price_id”:“0x7a5bc1d2b56ad029048cd63964b3ad2776eadf812edc1a43a31406cb54bff592”,“ema_price”:“28.886804000000000000”,“ema_conf”:“0.043720900000000000”,“conf”:“0.047065370000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“29.032783810000000000”,“cumulative_price”:“595244103.834408360000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x2b89b9dc8fdf9f34709a5b106b472f0f39bb6ca9ce04b0fd7f2e971688e2e53b”,“ema_price”:“1.000397500000000000”,“ema_conf”:“0.000668350000000000”,“conf”:“0.000815630000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“1.000430020000000000”,“cumulative_price”:“30167303.835940010000000000”,“timestamp”:“1713834450”}},{“price_id”:“0xeaa020c61cc479712813461ce153894a96a6c00b21ed0cfc2798d1f9a9e9c94a”,“ema_price”:“0.999935120000000000”,“ema_conf”:“0.000525600000000000”,“conf”:“0.000551750000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“0.999951830000000000”,“cumulative_price”:“30166806.825410890000000000”,“timestamp”:“1713834450”}},{“price_id”:“0xec5d399846a9209f3fe5881d70aae9268c94339ff9817e8d18ff19fa05eea1c8”,“ema_price”:“0.556822220000000000”,“ema_conf”:“0.000502800000000000”,“conf”:“0.000548340000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“0.555701810000000000”,“cumulative_price”:“14320548.009347680000000000”,“timestamp”:“1713834450”}},{“price_id”:“0xb00b60f88b03a6a625a8d1c048c3f66653edf217439983d037e7222c4e612819”,“ema_price”:“8.953761900000000000”,“ema_conf”:“0.008954880000000000”,“conf”:“0.010022230000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“8.980726720000000000”,“cumulative_price”:“192832007.345879240000000000”,“timestamp”:“1713834450”}},{“price_id”:“0xe62df6c8b4a85fe1a67db44dc12de5db330f7ac66b72dc658afedf0f4a415b43”,“ema_price”:“66901.638000000000000000”,“ema_conf”:“37.742856000000000000”,“conf”:“40.091740000000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“67093.600000000000000000”,“cumulative_price”:“991196858037.149980210000000000”,“timestamp”:“1713834450”}},{“price_id”:“0xff61491a931112ddf1bd8147cd1b641375f79f5825126d665480874634fd0ace”,“ema_price”:“3210.674300000000000000”,“ema_conf”:“2.775237900000000000”,“conf”:“3.625993360000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“3216.824106640000000000”,“cumulative_price”:“44383271875.368327600000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x60144b1d5c9e9851732ad1d9760e3485ef80be39b984f6bf60f82b28a2b7f126”,“ema_price”:“1.271073350000000000”,“ema_conf”:“0.002018130000000000”,“conf”:“0.001600320000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“1.276250020000000000”,“cumulative_price”:“18483555.483510380000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x72b021217ca3fe68922a19aaf990109cb9d84e9ad004b4d2025ad6f529314419”,“ema_price”:“0.000020315700000000”,“ema_conf”:“0.000000036800000000”,“conf”:“0.000000050200000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“0.000020335800000000”,“cumulative_price”:“221.364059066700000000”,“timestamp”:“1713834450”}},{“price_id”:“0xef0d8b6fda2ceba41da15d4095d1da392a0d2f8ed0c6c7bc0f4cfac8c280b56d”,“ema_price”:“157.880474000000000000”,“ema_conf”:“0.131982400000000000”,“conf”:“0.152634000000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“158.431007470000000000”,“cumulative_price”:“1975581666.132314520000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x2f95862b045670cd22bee3114c39763a4a08beeb663b145d283c31d7d1101c4f”,“ema_price”:“606.099520000000000000”,“ema_conf”:“0.620546550000000000”,“conf”:“0.669259520000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“606.725616060000000000”,“cumulative_price”:“7425608760.722876360000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x5867f5683c757393a0670ef0f701490950fe93fdb006d181c8265a831ac0c5c6”,“ema_price”:“0.984617050000000000”,“ema_conf”:“0.001190150000000000”,“conf”:“0.001274650000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“0.986709580000000000”,“cumulative_price”:“26946704.947684970000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x53614f1cb0c031d4af66c04cb9c756234adad0e1cee85303795091499a4084eb”,“ema_price”:“0.656651000000000000”,“ema_conf”:“0.000827870000000000”,“conf”:“0.001154860000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“0.661381480000000000”,“cumulative_price”:“8554514.056319900000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x0bbf28e9a841a1cc788f6a361b17ca072d0ea3098a1e5df1c3922d06719579ff”,“ema_price”:“0.693629630000000000”,“ema_conf”:“0.001007630000000000”,“conf”:“0.000944790000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“0.699004670000000000”,“cumulative_price”:“7369560.767554870000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x09f7c1d7dfbb7df2b8fe3d3d87ee94a2259d212da4f30c1f0540d066dfa44723”,“ema_price”:“11.541070500000000000”,“ema_conf”:“0.015258030000000000”,“conf”:“0.015545190000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“11.609449680000000000”,“cumulative_price”:“182607676.937912240000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x93da3352f9f1d105fdfe4971cfa80e9dd777bfc5d0f683ebb6e1294b92137bb7”,“ema_price”:“39.482048000000000000”,“ema_conf”:“0.038839050000000000”,“conf”:“0.065566260000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“39.612631740000000000”,“cumulative_price”:“315748097.333824220000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x4ca4beeca86f0d164160323817a4e42b10010a724c2217c6ee41b54cd4cc61fc”,“ema_price”:“2.932752500000000000”,“ema_conf”:“0.004805190000000000”,“conf”:“0.006226560000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“2.950010690000000000”,“cumulative_price”:“13759651.774937630000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x23d7315113f5b1d3ba7a83604c44b94d79f4fd69af77f804fc7f920a6dc65744”,“ema_price”:“1.397440610000000000”,“ema_conf”:“0.001656600000000000”,“conf”:“0.001616040000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“1.400939510000000000”,“cumulative_price”:“28572418.557747830000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x3fa4252848f9f0a1480be62745a4629d9eb1322aebab8a791e344b3b9c1adcf5”,“ema_price”:“1.213383220000000000”,“ema_conf”:“0.001031710000000000”,“conf”:“0.001164100000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“1.217451980000000000”,“cumulative_price”:“12345048.049895620000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x385f64d993f7b77d8182ed5003d97c60aa3361f3cecfe711544d2d59165e9bdf”,“ema_price”:“2.563591750000000000”,“ema_conf”:“0.002768450000000000”,“conf”:“0.003995470000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“2.573954460000000000”,“cumulative_price”:“55578028.035863970000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x0a0408d619e9380abad35060f9192039ed5042fa6f82301d0e48bb52be830996”,“ema_price”:“1.203138230000000000”,“ema_conf”:“0.001971470000000000”,“conf”:“0.002212350000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“1.215997350000000000”,“cumulative_price”:“6449097.746622860000000000”,“timestamp”:“1713834450”}},{“price_id”:“0x8ac0c70fff57e9aefdf5edf44b51d62c2d433653cbb2cf5cc06bb115af04d221”,“ema_price”:“15.496253300000000000”,“ema_conf”:“0.012948090000000000”,“conf”:“0.014837980000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“15.497924270000000000”,“cumulative_price”:“352625691.627356280000000000”,“timestamp”:“1713834450”}},{“price_id”:“0xdcef50dd0a4cd2dcc17e45df1676dcb336a11a61c69df7a0299b0150c672d25c”,“ema_price”:“0.161443990000000000”,“ema_conf”:“0.000150170000000000”,“conf”:“0.000156360000000000”,“publish_time”:“1713834448”,“price_state”:{“price”:“0.161823080000000000”,“cumulative_price”:“2256354.510970600000000000”,“timestamp”:“1713834450”}}]

4

So I would like a new row for every element inside this json array. So I unfortunately I don’t know how I can normalize it.

5

I have the answer for you!

The idea behind this blog post fits exactly your case :wink:

6

If I’m understanding the blog post, then I don’t think this quite fits my use case. I want to take a single row which contains a json array with multiple elements. Then I want that single row to be expanded in the continuous aggregate into multiple rows. Basically, the single attribute.value I posted above should be expanded into n rows in the continuous aggregate.

If i’m misunderstanding let me know.