I'm trying to create a materialized view in RisingWave that flattens a source with nested structs, specifically an array of products. Here's the source definition:
CREATE SOURCE RUDDER_TRANSACTION (
userId VARCHAR
,anonymousId VARCHAR
,timestamp_column timestamptz
,event VARCHAR
,type VARCHAR
,properties STRUCT <
checkout_id INT
,order_id INT
,products STRUCT <
category VARCHAR
,NAME VARCHAR
,price INT
,product_id VARCHAR
,quantity INT
,brand VARCHAR
,attributes VARCHAR []
,sku VARCHAR
> []
,total INT
,transaction_type VARCHAR
,clubId INT
>
)I attempted to use a CROSS JOIN with unnest() to flatten the products, but it's not working as expected. The join seems to collapse all row combinations to an empty response. I'm not sure how to correctly perform this operation in RisingWave, as there is limited documentation on cross joins. Can you help me achieve my goal?
Daniel Byta
Asked on Nov 12, 2023
I've resolved my problem. It turns out that unnest doesn't work with nested struct[] in RisingWave. It couldn't parse the incoming JSON as the defined structure, treating it as null. To work around this, I changed the nested structure to type JSONB and used the jsonb_array_elements function instead of unnest, which worked. However, this doesn't seem like the best way to define stream structures, and I'm not clear on why I couldn't follow the documentation guides. I'm considering creating an issue to address this.