# OTQL examples

## Basic queries

To begin, we'll talk about fundamentals of the syntaxe language. How it works and what it's the excepted result for each query. It will give you additional informations about OTQL. 

{% hint style="info" %}
It's better to start reading the [Introduction to OTQL](https://developer.mediarithmics.io/otql-queries#introduction) queries, if you didn't to it yet, before continue. 
{% endhint %}

### Schema example

For the following examples, we consider the runtime schema below:

```graphql
type UserPoint {
    id : ID!
    activities : [UserActivity!]!
    profiles : [UserProfile!]!
}

type UserActivity {
    id : ID!
    events : [UserEvent!]!
}

type UserEvent {
    id : ID!
    name : String
}

type UserProfile {
    id : ID!
    age : String
}
```

If we want to represent a userpoint, it will be object tree. For illustrate it, we could considerate it like this :   

![Representation of an userpoint](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FMvfjIhZYiHbvRUFvpeK0%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-28%20a%CC%80%2014.49.33.png?alt=media\&token=f544e161-f2d3-4d79-ba91-f36c8e32a422)

### The different steps of an OTQL query

Even if the syntaxe of an OTQL query is close to a SQL one, the execution isn't the same at all. We talk about object tree and not column. This main difference gets lots of consequences and one of them is how the query is executed. 

**Query resolution is a two phases process**

1. Narrow queried object mentioned in `FROM` by applying a `WHERE` clause on it or/and on any sub-object's fields

```sql
// SELECT <objects fields or aggregates> # fields returned
// FROM <object collection> # where the query will be executed
// WHERE <object tree expression> # filter applied

SELECT { activities { events { name } } }
FROM UserPoint 
WHERE activities { events { name = "$transaction_confirmed" } }
```

When `WHERE` clause is applied on sub-object's field, if at least one sub-object validates the condition then all parent objects validate it as well.

![In this example, the UserEvent id=3 validates the WHERE which means UserActivity id=2 is selected and therefore UserPoint id=1 is returned from the query.](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FoUv2xxoRLeu6xzstOrlz%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-26%20a%CC%80%2014.25.43.png?alt=media\&token=cd910db0-762e-4370-9db8-9624a3366569)

&#x20; 2\. Return only desired objects & fields by listing them in `SELECT` clause

![This query means : "Get all event’s name by user with at least one transaction confirmed"](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FJwQUQ7GHXGwQVJS93Jhk%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-26%20a%CC%80%2014.25.57.png?alt=media\&token=bd13669b-0128-4e5b-9cc8-d8420ed5da49)

Finally the query returns :&#x20;

```
[
   {
      activities : [ { events : [ { name : "$page_view" } ] },
                     { events : [ { name : "$page_view" }, 
                                  { name : "$transaction_confirmed" } ] } ] 
   } 
]
```

As you can see, despite the `WHERE` clause on `$transaction_confirmed` events, the query  returns `$page_view` events since `SELECT` is applied from **UserPoint**.

#### Same explanation with multiple userpoints

1. Narrow queried object mentioned in `FROM` by applying a `WHERE` clause on it or/and on any sub-object's fields

```sql
# Example : Get all event’s name by user with at least one transaction confirmed
SELECT { activities { events { name } } }
FROM UserPoint 
WHERE activities { events { name = "$transaction_confirmed" } }
```

![Like we seen, one transaction confirmed validate all the userpoint](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FLfl9vRn9eygdlWNsyRZZ%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-26%20a%CC%80%2014.35.26.png?alt=media\&token=6cfc009d-c717-4063-b314-ec221e1afdd4)

&#x20; 2\. Return only desired objects & fields by listing them in `SELECT` clause

![The SELECT only be apply in userpoint still in the list, after the WHERE filter](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FCpFGovIghRScUBy7179a%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-26%20a%CC%80%2014.35.16.png?alt=media\&token=50c6c974-f691-451b-b9c0-6523402b3e52)

Here the query returns :&#x20;

```
[
   {
      activities : [ { events : [ { name : "$page_view" }, 
                                  { name : "$transaction_confirmed" } ] } ]
   }, 
   {
      activities : [ { events : [ { name : "$page_view" }, 
                                  { name : "$transaction_confirmed" } ] }, 
                     { events : [ { name : "$page_view" } ] } ]
   }
 ]
```

In this example, the **UserPoint id=3** was not picked in the `WHERE` clause since it doesn't have any `$transaction_confirmed` event attached to it.

### Change the scope of your query&#x20;

#### Use FROM to chose your execution context

As you could see, the `WHERE` can filter only object in defined by the `FROM` . It gives you the ability to start the query where you need, it defines the scope of the query resolution and where the other operators are executed.&#x20;

If you want to pick only a specific event, you will have to change the context. The `FROM` allows you to do so.

```sql
# Example : Get the name of each event where the name is "$trasaction_confirmed"
# Here, we just want to be sure this query return only events we want
SELECT { name }
FROM UserEvent 
WHERE name = "$transaction_confirmed"
```

![](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2Fynyi0f9oUuvM6jzJbrcV%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-26%20a%CC%80%2014.41.39.png?alt=media\&token=22089ee5-dbe8-4b20-b266-8436e388e38b)

Here the query returns :&#x20;

```
[ 
    { 
        name : "$transaction_confirmed"
    }
]
```

However specifying a sub-object also limit the scope of the predicate. For example, use `FROM UserEvent`  doesn't give you access to **UserPoint** fields (like `profiles`).

**Another way to get specific fields values : use `@filter` in the `SELECT` part to only retrieve elements you need**&#x20;

You can also use [`@filter`](https://developer.mediarithmics.io/otql-queries#filters) if you don't want to change the scope of your query but you need to get only specific elements. It is completely independent of the `WHERE` clause and the filter is applied after the `WHERE` clause execution.

The first step will be the same as the one mentionned previously (narrow queried object mentioned in `FROM` using the `WHERE` clause).

```sql
# Example : Get events named "$page_view" by user with at least one transaction confirmed
SELECT { activities { events @filter(clause:"name = \"$page_view\"") { name } } }
FROM UserPoint 
WHERE activities { events { name = “$transaction_confirmed” } }
```

![Apply the WHERE clause on your data](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FzPtHjyFqqs5zvOMFRMGI%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-26%20a%CC%80%2015.34.17.png?alt=media\&token=0e12b6bb-2fd2-480a-8a7a-843aa08959ed)

But during the second phase, the `@filter` will be apply on selected objects.&#x20;

![@filter remove all the object which doesn't match with its clause](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FOPxXlFapLkk7cXN9Bj8T%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-26%20a%CC%80%2015.38.30.png?alt=media\&token=a49a01f8-dfc9-4ae7-93f6-1b600c3ae950)

Thefore, only `$page_view` are returned by the query :&#x20;

```
[
   {
      activities : [ { events : [ { name : "$page_view" } ] } ]
   }, 
   {
      activities : [ { events : [ { name : "$page_view" } ] }, 
                     { events : [ { name : "$page_view" } ] } ]
   }
 ]
```

#### Get condition on different sub-object Tree

You can add multiple conditions in the `WHERE` clause using [boolean operators](https://developer.mediarithmics.io/otql-queries#where-object-tree-expressions).&#x20;

```sql
# Example : Get event’s names by user with at least one transaction confirmed 
# and an age between 20 and 30 years old
SELECT { activities { events { name } } }
FROM UserPoint WHERE profiles { age = "20-30" } 
  AND activities {events { name = "$transaction_confirmed" } }
```

![In this example, the two conditions need to be validated](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FRRhrJPXcpMeq5rXWKAMq%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-26%20a%CC%80%2016.13.28.png?alt=media\&token=4d51f1bb-4565-414a-9e8c-0f148b26665e)

```
[
  {
      activities : [ { events : [ { name : "$page_view" }, 
                                  { name : "$transaction_confirmed" } ] }, 
                     { events : [ { name : "$page_view" } ] } ]
   }
 ]
```

#### Add a condition from another Object Tree

As previously demonstrated, it's quite easy to add conditions in sub-objects scope (`FROM`). However if you need to execute your query in a specific scope and apply condition from an other object, you will have to use `JOIN`.&#x20;

```sql
# Example : Get event’s names by events named "$transaction_confirmed" 
# and where the user is between 20 and 30 years old
SELECT { name }
FROM UserEvent WHERE name = "$transaction_confirmed"
JOIN UserPoint WHERE profiles { age == "20-30" }
```

*Note:* the join is automatically resolved by UserPoint, there is no need to provide join constraint.

![Step 1 : Apply the first WHERE clause ](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FDHW5V8Ed1azSwzceM130%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-28%20a%CC%80%2010.36.46.png?alt=media\&token=2dcd9d4e-02d0-4d09-ae09-83cd5c40b3e1)

![Step 2  : Create an intersection with the second WHERE ](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2FyHBOxm4PAw2szxoHLGIt%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-26%20a%CC%80%2016.45.19.png?alt=media\&token=91bfb5cb-0f78-486a-9c92-e91c9a028114)

![Step 3 : Execute the selection in the new scope](https://4196284719-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MMuoqM-5hJ5JY0WnAKL%2Fuploads%2F54i2RyfkHfGtExSDkMTx%2FCapture%20d%E2%80%99e%CC%81cran%202022-04-28%20a%CC%80%2010.36.54.png?alt=media\&token=84de5c09-d0f2-4554-9bb9-6b9083940612)

```
[ 
    { 
        name : "$transaction_confirmed"
    }
]
```

By the way, it is also possible to directly make the `JOIN` in `UserProfile` to get the same result :&#x20;

```sql
# Example : Get event’s names by events named "$transaction_confirmed" 
# and where the user is between 20 and 30 years old
SELECT { name }
FROM UserEvent WHERE name = "$transaction_confirmed"
JOIN UserProfile WHERE age == "20-30"
```

## Scoring operator <a href="#https-app.gitbook.com-o-mkamxgck7ut1oeta9l-s-mmuoqm-5hj5jy0wnakl-changes-libr2mo7fkqtp3jsj4u9-queryi" id="https-app.gitbook.com-o-mkamxgck7ut1oeta9l-s-mmuoqm-5hj5jy0wnakl-changes-libr2mo7fkqtp3jsj4u9-queryi"></a>

This is the runtime schema for examples below

```graphql
type UserPoint  @TreeIndexRoot(index:"USER_INDEX") {
   id:ID!
   activity_events:[ActivityEvent!]!
}

type ActivityEvent  @Mirror(object_type:"UserEvent") {
   order:Order @Property(path:"$properties.order")
}

type Order  {
   order_products:[OrderProduct]!
   date: Timestamp! 
}

type OrderProduct  {
   id:String @TreeIndex(index:"USER_INDEX")
   price: Float @TreeIndex(index:"USER_INDEX") # in €
   category:String @TreeIndex(index:"USER_INDEX") # possible value : "IT" or "Book"
}
```

#### Use case : Count UserPoint who bought more than X€ of product of the IT category

```sql
# More than 1000€ in one order : 
SELECT @count{} FROM UserPoint
WHERE activity_events { 
    order { 
        order_products @ScoreField(name: "price") @ScoreSum(min: 1000) { 
            category="IT" 
        }
    }
}

# More than 1000€ in cross orders (explicite): 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000, result:"boolean_value") {
    order { 
        order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
            category="IT"
        }
    }
}

# More than 1000€ in cross orders (implicite): 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
    order { 
        order_products @ScoreField(name:"price") {
            category="IT"
        }
    }
}

# More than 1000€ in cross orders this last 10 days: 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
    order { 
        order_products @ScoreField(name:"price") {
            category="IT"
        }
        AND date > "now-10d"
    }
}

# More than 1000€ in cross orders with at least products which cost 10€: 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
	order {
	    order_products @ScoreField(name: "price") @ScoreSum(min: 10, result:"score_value") { 
          category="IT" 
      }
   }
}

# More than 1000€ in cross orders with at least 10 products : 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
	order {
	    order_products @ScoreField(name: "price") @ScoreSum(result:"score_value") { 
          category="IT" 
      }
      AND order_products @ScoreSum(min: 10) {
	        category="IT"
	    }
   }
}

# More than 1000€ in cross orders with at least one product which costs more than 10€ : 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
	order {
	    order_products @ScoreField(name: "price") @ScoreSum(result:"score_value") { 
          category="IT" 
      }
      AND order_products @ScoreField(name: "price") @ScoreMax(min: 10) {
	        category="IT"
	    }
   }
}

# WARNING : DOES NOT WORK 

# More than 1000€ in cross orders with at least 10 orders more than 100€ : 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
	order @ScoreSum(min: 10, result: "score_value") {
      @ScoreSum(min: 100, result: "boolean_value") {
           order_products @ScoreField(name: "price") @ScoreSum(result:"score_value") { 
              category="IT" 
           }
       }
   }
}
```

#### Use case : Count UserPoint who bought in average more than X€ of product of the IT category

```sql
# More than 1000€ in one order : 
SELECT @count {} FROM UserPoint
WHERE activity_events { 
    order { 
        order_products @ScoreField(name: "price") @ScoreAvg(min: 1000) { 
            category="IT" 
        }
    }
}

# More than 1000€ in cross orders : 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreAvg(min : 1000) {
    order { 
        order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
            category="IT"
        }
    }
}
```

#### Use case : Count UserPoint who bought more than X€ of product of the IT or Book category

```sql
# More than 1000€ in cross orders in IT or Book category : 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
    order { 
        order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
            category="IT"
            OR category="BOOK"
        }
    }
}

# Same result : 
#Doesn't work yet
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
    order { 
        order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
            category="IT"
        },
        order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
            category="BOOK"
        }
    }
}

# More than 1000€ in cross orders in only with product of IT or Book category : 
# maximum of separately IT products and Book products is superior to 1000€
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
    order { 
        order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
            category="IT"
        }
        OR  order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
            category="BOOK"
        }
    }
}

# More than 1000€ in cross orders of products in IT and products in Book category : 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
    order { 
        order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
            category="IT"
            OR category="BOOK"
        }
    }
}

# More than 1000€ in cross orders in IT and Book category : 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
    order { 
        order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
            category="IT"
        }
    }
AND activity_events @ScoreSum(min : 1000) {
    order { 
        order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
            category="BOOK"
        }
    }
}

# More than 1000€ in cross orders with at least 10 IT products and 10 BOOK products : 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
    order { 
        order_products @ScoreField(name: "amount") @ScoreSum(){
            category="IT"
            OR category="BOOK"
        } 
        AND order_products @ScoreSum(min: 10) {
            category="IT"
        } 
        AND order_products @ScoreSum(min: 10) {
            category="BOOK"
        }
    }
}

### DUPLICATE WITH PREVIOUS QUERY
# More than 1000€ in cross orders in IT or Book category with at least 10€ of each in each order: 
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
    order { 
        order_products @ScoreField(name: "amount") @ScoreSum(){
            category="IT"
            OR category="BOOK"
        } 
        AND order_products @Scorefield(name: "amount") @ScoreSum(min: 10) {
            category="IT"
        } 
        AND order_products @Scorefield(name: "amount") @ScoreSum(min: 10) {
            category="BOOK"
        }
    }
}
```

## Date operators

This is the runtime schema for examples below

```graphql
type UserPoint  @TreeIndexRoot(index:"USER_INDEX") {
   id:ID!
   profiles:[UserProfile!]!
}

type UserProfile  {
   id:ID!
   birth_date:Date @TreeIndex(index:"USER_INDEX")
}
```

**Use case: Select all UserPoint who are celebrating their birthday today and are between 18 and 28 years old (rolling years).**

<pre class="language-sql"><code class="lang-sql">SELECT { id } 
FROM UserPoint 
WHERE profiles { birth_date IN ["now-18y/d", "now-19y/d", "now-20y/d", "now-21y/d", 
<strong>    "now-22y/d", "now-23y/d", "now-24y/d", "now-25y/d", "now-26y/d", "now-27y/d", "now-28y/d"]
</strong>}
</code></pre>

{% hint style="warning" %}
Be aware that `now` is evaluated at the start of the segment calculation, which may result in a discrepancy between the expected and actual values.
{% endhint %}

**Use case: Select all UserPoint who are celebrating their birthday in the next 7 days and are between 18 and 28 years old (rolling years).**

```sql
SELECT { id } 
FROM UserPoint 
WHERE profiles { ( birth_date >= "now-18y/d" AND birth_date < "now+7d-18y" ) OR
( birth_date >= "now-19y/d" AND birth_date < "now+7d-19y" ) OR
( birth_date >= "now-20y/d" AND birth_date < "now+7d-20y" ) OR
( birth_date >= "now-21y/d" AND birth_date < "now+7d-21y" ) OR
( birth_date >= "now-22y/d" AND birth_date < "now+7d-22y" ) OR
( birth_date >= "now-23y/d" AND birth_date < "now+7d-23y" ) OR
( birth_date >= "now-24y/d" AND birth_date < "now+7d-24y" ) OR
( birth_date >= "now-25y/d" AND birth_date < "now+7d-25y" ) OR
( birth_date >= "now-26y/d" AND birth_date < "now+7d-26y" ) OR
( birth_date >= "now-27y/d" AND birth_date < "now+7d-27y" ) OR
( birth_date >= "now-28y/d" AND birth_date < "now+7d-28y" ) }
```

## Filters

`@filter` can be quite hard to understand, so let's see some examples to clarify its usage.

This is the runtime schema for examples below

```graphql
type UserPoint  @TreeIndexRoot(index:"USER_INDEX") {
   id:ID!
   events:[UserEvent!]!
   activities:[UserActivity!]!
}

type UserActivity  {
   id:ID!
   events:[UserEvent!]!
}

type UserEvent  @Mirror(object_type:"UserEvent") {
   id:ID!
   event_name:String @Property(path:"$event_name") @TreeIndex(index:"USER_INDEX")
}
```

**Use case: I want to retrieve, for each user, all URLs of type "newsarticle" and category "actu" that were browsed yesterday.**

If I use the following query :&#x20;

```sql
SELECT { events @filter(clause: "page_type == \"newsarticle\" AND page_category == \"actu\""){ url } } 
FROM UserPoint 
WHERE activities { ts >= "now-1d/d" }
```

```json
// This query returns
[
  [
    {
      "events": [
        {
          "url": "xxx"
        }
      ]
    },
    {
      "events": []
    },
    {
      "events": [
          {
            "url": "xxx"
          },
          {
            "url": "xxx"
          },
          {
            "url": "xxx"
          }
        ]
    },
    {
      "events": []
    },
//...
  ]
]
```

This query returns many empty events, making the result unusable. Although the events are filtered, I haven’t excluded UserPoint that don’t contain any events with the clause.

To fix this, I need to add a **WHERE** clause to ensure each UserPoint has at least one event which matches the clause.

```sql
SELECT { events @filter(clause: "page_type == \"newsarticle\" AND page_category == \"actu\""){ page_type page _category url } } 
FROM UserPoint 
WHERE activities { ts >= "now-1d/d" } AND events { page_type == "newsarticle" AND page_category == "actu" }
```

```json
// This query returns
[
  [
    {
      "events": [
        {
          "page_type": "newsarticle",
          "page_category": "actu",
          "url": "xxx"
        }
      ]
    },
    {
      "events": [
        {
          "page_type": "newsarticle",
          "page_category": "actu",
          "url": "xxx"
        },
        {
          "page_type": "newsarticle",
          "page_category": "actu",
          "url": "xxx"
        },
        {
          "page_type": "newsarticle",
          "page_category": "actu",
          "url": "xxx"
        }
      ]
    }
  ]
]
```

Here, the result is an improvement over the previous one, displaying only the events with a url of a page with the type "newsarticle" and the category "actu".

Now, if we want to retrieve information from activities while still filtering the events, we need to refine the query further.

```sql
SELECT { activities { events @filter(clause: "page_type == \"newsarticle\" AND page_category == \"actu\""){ page_type page_category url } } }
FROM UserPoint 
WHERE activities { ts >= "now-1d/d" } AND events { page_type == "newsarticle" AND page_category == "actu" }
```

```json
[
  [
    {
      "activities": [
        {
          "events": [
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            },
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            },
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            }
          ]
        },
        {
          "events": []
        },
        {
          "events": [
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            }
          ]
        }
      ]
    }
  ]
]
```

As you can see, some events may be empty because the filter is not applied at the `activities` level. This means the returned activities contain at least one `events` which matches the clause.

If you try applying the filter at a higher level, specifically at the activity level:

```sql
SELECT { activities @filter(clause: "events { page_type == \"newsarticle\" AND page_category == \"actu\"}"){ events { page_type page_category url } }
FROM UserPoint 
WHERE activities { ts >= "now-1d/d" } AND events { page_type == "newsarticle" AND page_category == "actu" }
```

```json
[
  [
    {
      "activities": [
        {
          "events": [
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            }
          ]
        },
        {
          "events": [
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            },
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            },
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            },
            {
              "page_type": "video",
              "page_category": "sport",
              "url": "xxx"
            },
            {
              "page_type": "video",
              "page_category": "sport",
              "url": "xxx"
            }
          ]
        }
    ]
]
```

By removing the event filter, the query now returns all events from activities that contain at least one page that matches the clause, rather than only pages that match.

To fix this, the first solution is to add another `@filter` to exclude unnecessary elements from both `activities` and `events`.

```sql
SELECT { activities @filter(clause: "events { page_type == \"newsarticle\" AND page_category == \"actu\"}"){ 
    events @filter(clause: "page_type == \"newsarticle\" AND page_category == \"actu\""){ page_type page_category url } } }
FROM UserPoint 
WHERE activities { ts >= "now-1d/d" } AND events { page_type == "newsarticle" AND page_category == "actu" }
```

```json
[
  [
    {
      "activities": [
        {
          "events": [
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            }
          ]
        },
        {
          "events": [
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            },
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            },
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            }
          ]
        }
    ]
]
```

This query filters out unnecessary elements from both events and activities, but it is quite lengthy and difficult to read.

The second solution is to apply an empty `@filter` at the activities level in addition to the events level one:

<pre class="language-sql"><code class="lang-sql"><strong>SELECT @filter{ activities { events @filter(clause: "page_type == \"newsarticle\" AND page_category == \"actu\""){ page_type page_category url } } }
</strong>FROM UserPoint 
WHERE activities { ts >= "now-1d/d" } AND events { page_type == "newsarticle" AND page_category == "actu" }
</code></pre>

```json
[
  [
    {
      "activities": [
        {
          "events": [
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            }
          ]
        },
        {
          "events": [
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            },
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            },
            {
              "page_type": "newsarticle",
              "page_category": "actu",
              "url": "xxx"
            }
          ]
        }
    ]
]
```

The empty filter removes any `events` that are directly empty from the `activities`.

{% hint style="warning" %}
The empty filter only removes empty sub-objects. If you add another field that contains data, the filter will not remove the `events`

```sql
SELECT @filter{ activities { id events @filter(clause: "page_type == \"newsarticle\" AND page_category == \"actu\""){ url } } }
FROM UserPoint 
WHERE activities { ts >= "now-1d/d" } AND events { page_type == "newsarticle" AND page_category == "actu" }
```

<pre class="language-json"><code class="lang-json">[
  [
    {
      "activities": [
        {
          "events": [
            {
              "url": "xxx"
            },
            "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
          ]
        },
        {
          "events": [],
          "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
        }
      ]
    }
  ]
<strong>]
</strong></code></pre>

{% endhint %}