OTQL examples
This page provides examples of OTQL queries, based on simplified schemas. The objective is to be less technical and illustrate how works our language by different use cases.
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.
It's better to start reading the Introduction to OTQL queries, if you didn't to it yet, before continue.
For the following examples, we consider the runtime schema below:
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
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 aWHERE
clause on it or/and on any sub-object's fields
// 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.
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"
Finally the query returns :
[
{
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.- 1.Narrow queried object mentioned in
FROM
by applying aWHERE
clause on it or/and on any sub-object's fields
# 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
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
filterHere the query returns :
[
{
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.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. If you want to pick only a specific event, you will have to change the context. The
FROM
allows you to do so.# 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"

Here the query returns :
[
{
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 You can also use
@filter
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).# 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
But during the second phase, the
@filter
will be apply on selected objects. 
@filter remove all the object which doesn't match with its clause
Thefore, only
$page_view
are returned by the query : [
{
activities : [ { events : [ { name : "$page_view" } ] } ]
},
{
activities : [ { events : [ { name : "$page_view" } ] },
{ events : [ { name : "$page_view" } ] } ]
}
]
# 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
[
{
activities : [ { events : [ { name : "$page_view" },
{ name : "$transaction_confirmed" } ] },
{ events : [ { name : "$page_view" } ] } ]
}
]
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
. # 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 
Step 2 : Create an intersection with the second
WHERE

Step 3 : Execute the selection in the new scope
[
{
name : "$transaction_confirmed"
}
]
By the way, it is also possible to directly make the
JOIN
in UserProfile
to get the same result : # 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"
This is the runtime schema for examples below
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"
}
# 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"
}
}
}
}
# 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"
}
}
}
# 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 catory 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"
}
}
}
Last modified 1yr ago