Solved

Advice for Filtering on Event Properties that are Arrays

  • 31 March 2022
  • 5 replies
  • 1316 views

Userlevel 4
Badge +8

Hey all,

 

When a transaction occurs on our ecomm site, we send one Purchase event to Amplitude. We have event properties such as product name, product sku, product brand, etc. If there are multiple products, the values are sent as an array. For example, if an order contains an Apple iPhone and a cell phone plan, the product name event property will come over as [Apple iPhone 11 Pro Max, 4GB Service Plan].

 

Our need is pretty simple and straightforward. I’d like to be able to query the Purchase event by product name. For example, I would like to see all Purchases that had either an Apple iPhone 11 Pro Max or a Samsung Galaxy S12. 

 

After working with support, it is my understanding that I am not able to use the set contains operator to apply multiple filters to arrays. I would have to do the following instead:


Event 1: Purchase where product name set contains Apple iPhone 11 Pro Max
Event 2: Purchase where product name set contains Samsung Galaxy S12
Event 3: Purchase where product name set contains Apple iPhone 11 Pro Max, Samsung Galaxy S12
 

Then I will need to use the custom formula TOTALS(A)+TOTALS(B)-TOTALS(C).

 

I think my use case is pretty common, so I was surprised that there wasn’t a more straightforward solve. I can also see the individual events causing a headache when I want to look at Purchase events that include several specific items, not just 2. How are others approaching this? Are you sending the values as strings instead of arrays? 

 

Thanks in advance!

icon

Best answer by Saish Redkar 1 April 2022, 04:26

View original

5 replies

Userlevel 7
Badge +10

Hey @SheenaGreen 

I might be oversimplifying this one, but have you tried just using equals (=) on both Apple iPhone 11 Pro Max, Samsung Galaxy S12 to get your desired number? Amplitude parses the values in arrays as individual values when querying the charts.

If you look at this demo chart, the numbers add up when trying to match total counts when using equals(94,95) to the total counts when using the overall formula : set contains (94) + set contains(95) - set contains(94,95).  I have used that on user properties, but it shouldn’t behave any differently on event properties.

 

Let me know if that makes any sense.

Userlevel 4
Badge +8

@Saish Redkar you are a LIFESAVER!!!! I was feeling so perplexed over this. Cool if I bounce a sanity check off you? 

 

Here’s my current understanding:

  • To use OR logic with arrays (Purchase contains Product A OR Product B), I simply need to use the equals operator. Product name equals Product A, Product B
  • To use AND logic with arrays (Purchase contains Product A AND Product B), I need to use the set contains operator. Product name set contains Product A, Product B
  • If I want to query Purchase contains Product A OR Product B, but not Product C, I need to use both the equals and set does not contain operators. Product name equals Product A, Product B, Product name set does not contain Product C.

Does that align with your take on arrays in Amplitude?

Userlevel 7
Badge +10

Hey Sheena
Yup. That’s my understanding based on the data I played around with in the AmpliTunes demo. Maybe we can get some confirmation from a Platform Specialist in case there are any caveats associated with this.

Userlevel 4
Badge +8

Hey @Saish Redkar , just wanted to close the loop as the Amplitude engineering team confirmed the above info on querying arrays is accurate. Thanks for the help!

Here is the documentation on this: https://help.amplitude.com/hc/en-us/articles/5606320929179-Array-operators-in-Amplitude

Reply