Event: Get query help

Help
,
1

Hi all,

I seem to have the worst time getting my head around advanced queries. This one should be simple but I don’t seem to be able to get it to work.

{
  "$and": [
    {
      "creationDate": {
        "$gte": "{{working.date}}T00:00:00Z"
      }
    },
    {
      "creationDate": {
        "$lte": "{{working.date}}T23:59:59Z"
      }
    },
    {
      "eventTags": {
        "$eq": {
          "$tagKey": "suppressed",
          "$tagValue": { "$in" : [ "false", "0" ] }
        }
      }
    }
  ]
}

The json parses correctly. working.date is set to yesterday in YYYY-MM-DD format and that bit of the query works, I can pull data between midnight and 11:59pm. It’s the eventTags bit that doesn’t work. For some unknown reason they’re using both “false” and 0 to indicate if events are suppressed, also true and 1 if they are. So I want to pull all events between two times where the tagKey suppressed is either false or 0. What am I doing wrong?

Thanks,

2

Hey @Nigel_Reed,

I’ll try to model some data based on your query on my end, and get back to you shortly.

Here’s a query I’ve used in the past that is similar to your current request:

{
  "$and": [
    {
      "created_at": {
        "$gte": "{{working.aMonthAgo}}",
        "$lte": "{{working.endOfMonth}}"
      },
      "created_by": {
        "$nin": [
          "User A",
          "User B"
        ]
      }
    }
  ]
}

Thank you,
Heath

3

This is where your problem lies. For tag queries, only the $eq and $ne operators are allowed, and furthermore, they cannot be applied down at the $tagKey or $tagValue levels.

I think this would get you what you are looking for. Note the use of an $or query further down in the query.

{
  "$and": [
    {
      "creationDate": {
        "$gte": "{{working.date}}T00:00:00Z"
      }
    },
    {
      "creationDate": {
        "$lte": "{{working.date}}T23:59:59Z"
      }
    },
    {
      "$or": [
        {
          "eventTags": {
            "$eq": {
              "$tagKey": "suppressed",
              "$tagValue": "false"
            }
          }
        },
        {
          "eventTags": {
            "$eq": {
              "$tagKey": "suppressed",
              "$tagValue": "0"
            }
          }
        }
      ]
    }
  ]
}

Somebody submitted some documentation feedback around advanced queries earlier today. (Perhaps it was you given this question.) I actually made some edits to that document that will hopefully answer your question here; we hope to get those edits published next week.

4

I’m also surprised that your original query was accepted and returned 0 results, instead of returning a 400-level response about an invalid request (since your use of the $in operator, much less an object as the value, is not a valid for the $tagValue property). I’ll submit a ticket to the engineering team to improve that validation.

5

@Dylan_Schuster
Thanks for that. I did try and $or query but I guess I didn’t put it in the right place. When I look in the debug node, working.result just shows “0 items”. . Rather than putting this as a bug, why not submit it as a use case for expanding the available queries on the eventTags?

It was probably me that submitted the feedback and my explain why I couldn’t find what I was looking for under that specific section.

@Heath Thanks for your suggestion but it doesn’t take into account the tagKey/tagValue pair. It looks like Dylan’s query is what I needed.

Thanks, to you both,