I am currently tracking product_purchased events in PostHog using the following JavaScript snippet:

posthog.capture('product_purchased', {
  purchase_id,
  items: data.data.items.map(({ item, ...rest }) => ({
    ...item,
    ...rest,
  })),
}) 

This event captures a list of items purchased, including their details. My goal is to analyze these events on the PostHog Trends Insights page. Specifically, I want to filter these events by the item type and purchase date, and then calculate the total revenue and number of sales.

Here is an example of what I’ve been able to do with SQL:

SELECT 
    JSONExtractString(arrayJoin(JSONExtractArrayRaw(properties.items ?? '[]')), 'category') AS itemType,
    JSONExtractString(arrayJoin(JSONExtractArrayRaw(properties.items ?? '[]')), 'name') AS itemName,
    COUNT(JSONExtractString(arrayJoin(JSONExtractArrayRaw(properties.items ?? '[]')), 'unique_id')) AS orderCount,
    SUM(JSONExtractFloat(arrayJoin(JSONExtractArrayRaw(properties.items ?? '[]')), 'cost')) AS totalRevenue
FROM events_table
ARRAY JOIN JSONExtractArrayRaw(properties.items ?? '[]') AS product
WHERE event_name = 'purchase_event'
  AND JSONExtractString(product, 'category') = 'ProductType'
  AND event_date <= now()
  AND {other_filters}
GROUP BY itemType, itemName
HAVING itemType = 'ProductType'
ORDER BY itemName
LIMIT 500

However, I am struggling to achieve similar results using HOG QL on the Trends page. I can’t seem to find a way to effectively filter and aggregate based on the nested items array in each product_purchased event.

Could someone provide guidance or examples on how to perform these operations using HOG QL in PostHog? Any help would be greatly appreciated!

Initial Attempts:

Tried using SQL functions like arrayJoin and JSONExtractString to extract and filter nested item_type from properties.items in the product_purchased events, but encountered syntax errors and implementation limitations.
Simplified Queries:

Verified array structure with count(properties.items) which worked.
Attempted to unnest arrays and extract specific fields but faced issues with complex functions.
Valid Functions:

Successfully used simpler functions like count(properties.items) but struggled with more advanced aggregations.
Dynamic Filtering:

Attempted to use dynamic filters and breakdowns using valid HogQL expressions for item_type and title, but encountered implementation challenges.