Hey there,
I would like to add a where clause to an event with the property query. I have a long list of values, so instead of adding each value one by one I would like to use glob matching.
Usually the syntax would be
?(dog|cat|zebra)
But this does not work (no event matches).
What is the correct way to do this in Amplitude?
Best answer by tracy.guo
Hi
In this case, I’d recommend using the “contains” operator instead of the glob match operator. You can add where ‘query’ contains and then paste a comma separated list into the values section (eg dog,cat,zebra). From here, the dropdown menu can parse the comma separated list and you should be able to click “Select All” to add all three values to the filter. Now any query value that contains dog or cat or zebra will be returned.
More information on adding a list of values can be found here: https://help.amplitude.com/hc/en-us/articles/360035354552#adding-a-list-of-property-values
The reason I don’t think glob match is a good fit here is that when glob matching, the “?” wildcard matches on a single character and unlike regex, glob match does not typically support the “|” operator. Glob match could potentially work if you glob match for *dog*,*cat*, and *zebra*, which should produce the same results as the contains operator, except that glob match is also case sensitive, so only lower cased dog, cat, and zebra values will be returned.
More information on glob match syntax can be found here: https://en.wikipedia.org/wiki/Glob_(programming)#Syntax
Do you think the “contains” operator works for your use case? Let me know your thoughts and if you have any questions!
+3Hi
In this case, I’d recommend using the “contains” operator instead of the glob match operator. You can add where ‘query’ contains and then paste a comma separated list into the values section (eg dog,cat,zebra). From here, the dropdown menu can parse the comma separated list and you should be able to click “Select All” to add all three values to the filter. Now any query value that contains dog or cat or zebra will be returned.
More information on adding a list of values can be found here: https://help.amplitude.com/hc/en-us/articles/360035354552#adding-a-list-of-property-values
The reason I don’t think glob match is a good fit here is that when glob matching, the “?” wildcard matches on a single character and unlike regex, glob match does not typically support the “|” operator. Glob match could potentially work if you glob match for *dog*,*cat*, and *zebra*, which should produce the same results as the contains operator, except that glob match is also case sensitive, so only lower cased dog, cat, and zebra values will be returned.
More information on glob match syntax can be found here: https://en.wikipedia.org/wiki/Glob_(programming)#Syntax
Do you think the “contains” operator works for your use case? Let me know your thoughts and if you have any questions!
What about if I am trying to write a formula in derived properties, where I am using the following line:
IF( REGEXEXTRACT($2, 'DI|EM|SL') == 'DI|EM|SL', 'lodge',
It seems that neither pipe nor comma work in this case as an ‘or’
+9In your formula, you are using the pipe symbol | inside the regular expression 'DI|EM|SL' to match any of the three strings "DI", "EM", or "SL". However, when you compare the result of REGEXEXTRACT($2, 'DI|EM|SL') to the string 'DI|EM|SL', you are comparing the matched string to a literal string that includes the pipe symbol.
To compare the matched string to any of the three strings "DI", "EM", or "SL", you can use the IN operator, which checks whether a value is included in a list of values. For example:
IF(REGEXEXTRACT($2, 'DI|EM|SL') IN ('DI', 'EM', 'SL'), 'lodge', ...)
This formula checks whether the result of REGEXEXTRACT($2, 'DI|EM|SL') is included in the list of strings ('DI', 'EM', 'SL'), and if so, it returns the string 'lodge'.
Note that in this case, you should not include the pipe symbol | in the comparison string. Instead, you should provide a list of possible values as separate strings inside parentheses, separated by commas.
If you don't have an Amplitude account, you can create an Amplitude Starter account for free and enjoy direct access to the Community via SSO. Create an Amplitude account. You can also create a Guest account below!
If you don't have an Amplitude account, you can create an Amplitude Starter account for free and enjoy direct access to the Community via SSO. Create an Amplitude account. Want to sign up as a guest? Create a Community account.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
