Solved

JQL - Filter for undefined properties not working?


Badge +2

Hi there!

 

I want to add a filter to my JQL query below, to pull only users where the Frist_seen property is undefined. So far it looks like I’m getting all users, event when the property has a value (a timestamp).

 

Can anyone help me see what need to fix?

 

Thanks!

 


function main() {
  //Get today's date
  var today = new Date ( (new 
  Date())).toISOString().split('T')[0];
  return join( 
  Events({
    from_date: '2020-03-01',
    to_date:   today,
  
  }), People())
  groupByUser([user.properties.First_Seen]),
  {type:'left'}
  .filter(function(user){return user.properties.First_Seen === undefined})
 .groupByUser(
  
  mixpanel.reducer.min('event.time'))
  .map(function(row){ return {
     "id": row.key[0],   //key 0 is the distinct_id name
      "First_Seen": new Date(row.value).toISOString()
  }}
  )
  
}

icon

Best answer by glencwillis 16 July 2020, 21:48

To clarify, when you’re doing a INNER join(), both events and the user profile must exist*

View original

14 replies

Badge

Hi!

Try changing your filter line to the following:

.filter(function(user){return !user.properties.First_Seen})

The exclamation in front of the property will filter for user profiles that do not have that property set.  If you alternatively want to pull profiles that do have the First_Seen property set, but those values are blank, you can try this:

.filter(function(user){return user.properties.First_Seen && user.properties.First_Seen === null})

Hope this helps!

Glen

Badge +2

Thank you Glen for your response!

 

I tried both suggestions and they each give me a similar error:

s.Recv failed: rpc error: code = FailedPrecondition desc = Uncaught exception TypeError: Cannot read property 'First_Seen' of undefined .filter(function(user){return user.properties.First_Seen && user.properties.First_Seen === null})

 

What else am I probably missing?

Badge +2

Just to add to my previous message:

 

The filter with the “!” does seem to return users with no first_seen field. So there isn’t even a field that says “undefined”. The second example, using “null” returns no results.

Is this as expected?

Badge

That’s likely expected, if the property is set on the profile it should have some value...

Badge +2

OK so we’re getting expected results when I apply the filter to just a people properties query (below). But I’m still not getting what I expect when i add the filter to the original query above.

I’d like the original query in the question to perform the calculation of First_Seen only on the profiles where First_Seen has not been set.

 

function main() {
  return People()
  .filter(function(user){return !user.properties.First_Seen});

}

Badge

Since your original query is a join() of both events and people, your filter should look as follows all-in:

.filter(function(some_variable){return !some_variable.user.properties.First_Seen});

I removed “user” as the variable since that would be a little confusing, but you need the format of “some_variable.user” or “some_variable.event” to specifically access the events or user profiles within the join().

Hope this helps!

Badge +2

Again, thank you so much for your help with this Glen!

I’m trying your above suggestion, plus I’ve switched the join to an inner join. I’m still getting all profiles, those that are both undefined AND defined.

I would think that I should expect to see either one or the other. Either undefined only because that’s who I’m looking for, or defined only because i’m trying to create first_seen values for users that don’t have them.

Am I expecting too much from this query?

Badge

Please send over the current query.

Badge +2

Here it is!

Thank you again for working with me on this!!


 

function main() {
 //Get today's date
 var today = new Date ( (new
Date())).toISOString().split('T')[0];
 return join(
 Events({
 from_date: '2020-03-01',
 to_date:  today,

 }), People())
groupByUser([user.properties.First_Seen]),

 {type:'inner'}
// .filter(function(some_variable){return !some_variable.user.properties.First_Seen})
 //.filter(function(user){return !user.properties.First_Seen})
.filter(function(some_variable){return some_variable.user.properties.First_Seen && some_variable.user.properties.First_Seen === null})

.groupByUser(

 mixpanel.reducer.min('event.time'))
 .map(function(row){ return {
"id": row.key[0],  //key 0 is the distinct_id name
"First_Seen": new Date(row.value).toISOString()
 }}
 );
}

Badge

There was an issue with where you are putting the {type: ‘inner’} piece, and not quite sure why there is the groupByUser within the join as well.  Try this:

function main() {
 //Get today's date
 var today = new Date((new Date())).toISOString().split('T')[0];
 return join(Events({
 from_date: '2020-03-01',
 to_date:  today,
 }), People(),
 {type:'inner'})
// .filter(function(some_variable){return !some_variable.user.properties.First_Seen})
 //.filter(function(user){return !user.properties.First_Seen})
.filter(function(some_variable){return some_variable.user.properties.First_Seen && some_variable.user.properties.First_Seen === null})
.groupByUser(mixpanel.reducer.min('event.time'))
.map(function(row){ return {
  "id": row.key[0],  //key 0 is the distinct_id name
  "First_Seen": new Date(row.value).toISOString()
 }}
 );
}

Badge +2

Thank you Glen!

I get a message saying my query returned no results.

 

If I’m reading the query correctly, I think (hope) it’s supposed to be taking all events within the date range, and inner joining on users who don’t have a First_Seen value. So if it were to stop there, I should see all the events for users without First_Seen.

Then I’m asking for the distinct_id of those users, and the time stamp of the first event (I will later write this data back into a profile property called First_Seen using the people_set() function).

If I’m getting no results from this query, my assumption is that there’s a problem with where the filter is taking place, or maybe the ‘undefined’ value isn’t really null.

I’ve tried filtering for ‘null’ and ‘undefined’, I’ve also tried filtering for profiles where First_Seen isn’t set (using the filter suggestions you made). All filters give me no results. If i run the query without a filter, it calculates the first timestamp for everyone - likely regardless of whether First_Seen is set.

The project I’m working on currently has 541 users with First_Seen set, and 315 users with First_Seen not set. So, if it calculates the first timestamp of just the users without First_Seen set, i should see 315 distinct rows.

Badge +2

Hi Glen!

I think I got closer to what I’m looking for. I am using your example but extending the event dates. I’m now seeing 267 users with First_Seen not being set and First_Timestamp being calculated. Still not the 315 I’m expecting, but maybe there’s something making the count of user profiles, and the count of users with events not match.

I’m looking into that with the dev teams.

Badge

Hi,

If you’re doing a join(), there must be both a user profiles AND events that exist for that user within the time range you’re looking - so my assumption is the discrepancy between the 267 and 315 is that those other users have a user profile but do did not perform any events within the time range you’re looking.

Glen

Badge

To clarify, when you’re doing a INNER join(), both events and the user profile must exist*

Reply


Mixpanel