SQL REST API
Querying the SQL REST API is as simple as sending a json format with the parameters you would use in a regular SQL query.
For AI agents, it is recommended to use the SQL REST API. SQL REST API is easier for agents to understand and generate.
Connecting to Lynk SQL REST API
Connecting to Lynk SQL REST API is done via a regular HTTP POST request to the following endpoint:
/api/sql/query?branch=&context=
For example:
// SQL REST API example
/api/sql/query?branch=dev&context=marketing
branch
[optional]
branch
[optional]Indicate which git branch to use. Allows exploring semantic definitions while in development.
If not specified, Lynk will take the definitions of the branch which is defined as the main branch.
context
[optional]
context
[optional] Indicate which context to use. Allows consuming semantic definitions from different contexts.
If not specified, Lynk will take the default
context
Authentication
To authenticate with the SQL REST API, you need to create and use an API token.
The API token should be sent in the x-api-key
header of the request.
Request body - simple example
In the example below you can find a simple Request Body for SQL REST API.
In this example, we request for top 100 the customers (customer_id) and their total_sales, for customers from BRAZIL, ordered by total_sales.
// Request body - simple SQL REST API
{
"entity": "customer",
"select": [
{
"type" : "field",
"entity": "customer",
"field" : "customer_id",
"alias": "customer_id"
},
{
"type" : "field",
"measure" : "total_sales",
"alias": "total_sales"
},
],
"where": {
"type": "fields",
"fields": [{
"entity": "customer",
"field": "nation_name",
"operator" : "equal",
"values": [
"BRAZIL"
]
}]
},
"oredrBy": [
{"entity": "customer", "field": "total_sales", "direction": "desc"}
],
"limit": 100
}
The above simple SQL REST API request simple example is equivalent to the following SQL API request:
-- Simple example - equivalent SQL API
USE {
"branch" : "<branch>", -- Use from endpoint's query param
"context" : "<context>", -- Use from endpoint's query param
}
}
SELECT
customer_id,
total_sales
FROM entity("customer")
WHERE nation_name in ('BRAZIL')
ORDER BY total_sales DESC
limit 100
Request body parameters
entity
entity
Define the main entity of the query
// example - main entity caluse
"entity": "customer"
joins
joins
Entities to be joined to the main entity
// example - joined entities
"joins": [
{
"entity": "order",
"joinName": "customer_orders",
"alias": "o"
}
]
The properties for joins
are:
entity
entity
The entity to join
join name [optional]
join name [optional]
Specify the join name. If not specified, Lynk will use the default join between the main entity and the entity in this joins clause
alias
alias
Give an alias to the joined entity
timeAggregation
timeAggregation
The time aggregation definition
// example - time aggregation
"timeAggregation": {
"timeGrain": "day",
"windowSize": 3,
"direction": "backward"
},
The properties for timeAggregation
are:
timeGrain
timeGrain
The time grain to aggregate by (day / week / month etc..) For example: "day", for 7 days backward
windowSize
[optional]
windowSize
[optional]The window size to apply (numeric value), in case of time window aggregation. For example: "7 ", for 7 days backward
direction
[optional]
direction
[optional]The direction to apply (backward / forward), in case of time window aggregation. For example: "backward", for 7 days backward.
select
select
The fields to select
There are three types of fields you can select in the select
clause:
field
measure
lynk_function
// example - select
"select": [
{
"type" : "field",
"entity": "customer",
"field" : "customer_id",
"alias": "cid"
},
{
"type" : "measure",
"measure" : "average_sales",
"alias": "total_orders"
},
{
"type" : "lynk_function",
"alias": "pop1",
"function": "pop",
"params" : {
"feature": "total_orders",
"offset": 12,
"repeats": 1,
"popOperator": "({a}-{b})",
"offsetOperator": "min",
"toDate": false
}
}
]
The properties for select
are:
field
[select
]
field
[select
]Use this option for fetching entity features
// example - select - field
"select": [
{
"type" : "field",
"entity": "customer",
"field" : "customer_id",
"alias": "cid"
}
]
measure
[select
]
measure
[select
]Use this option for fetching measures from the main entity.
// example - select - measure
"select": [
{
"type" : "measure",
"measure" : "average_sales",
"alias": "total_orders"
}
]
Make sure to select measures of the main entity (not from joined entities). Lynk joins all the joined entities to the main entity, and aggregates to the main entity level (or main entity + time_agg definition, if specified)
lynk_function
[select
]
lynk_function
[select
]Use this option to apply a Lynk Function and fetching it's result. For more information about this, visit Lynk Functions.
// example - select - lynk function (POP)
"select": [
{
"type" : "lynk_function",
"alias": "total_orders_over_last_4_months_average",
"function": "pop",
"params" : {
"feature": "total_orders",
"offset": 1,
"repeats": 4,
"offsetOperator": "average",
"popOperator": "({a}/{b})"
}
}
]
where
where
Apply filters to the query
There are two types of filters you can apply in the where
clause:
fields
sql
where
- fields
where
- fields
In case of applying where
filters as fields
, you will need to add the following parameters:
entity
- the entity which the field to filter on belongs tofield
- the entity feature to filter onoperator
- the filter operator (see all options for filter operators)values
- an array of field values to filter by
// example - where - fields
"where": {
"type": "fields",
"fields": [{
"entity": "customer",
"field": "nation_id",
"operator" : "equal",
"values": [
"40", "42"
]
}]
}
The above example translates to the following SQL WHERE
clause:
-- example - where - fields
WHERE customer.nation_id in ("40", "42")
where
- sql
where
- sql
Apply a simple sql expression to use for filtering
// example - where - sql
"where": {
"type": "sql",
"sql": "{customer}.{nation_id} in ('40', '42')"
}
groupBy
groupBy
Array of fields to use in the group by
clause
// example - groupBy
"groupBy": ["1", "2"]
having
having
Apply filters to the query using aggregated fields
There are two types of filters you can apply in the having
clause:
fields
sql
having
- fields
having
- fields
In case of applying having
filters as fields
, you will need to add the following parameters:
entity
- the entity which the field to filter on belongs tomeasure
- the entity measure to filter onoperator
- the filter operator (see all options for filter operators)values
- an array of field values to filter by
// example - having - fields
"having": {
"type": "fields",
"fields": [{
"entity": "customer",
"measure": "measure(total_sales)",
"operator" : "gt",
"values": [
"100"
]
}]
}
The above example translates to the following SQL HAVING
clause:
-- example - having - fields
HAVING customer.total_sales > ("100")
having
- sql
having
- sql
Apply a simple sql expression to use for filtering
// example - having - sql
"where": {
"type": "sql",
"sql": "{customer}.{total_sales} > ('100')"
}
orderBy
orderBy
Array of items to sort by (order by):
In case of applying orderBy
option, you will need to add the following parameters:
entity
- the entity to take the field to sort by fromfield
- the entity field to sort bydirection
- “desc” or “asc”. (default “asc”)
// example - sort
"orderBy": [
{"entity": "customer", "field": "total_sales", "direction": "desc"}
]
limit
limit
The number of rows to return
// example - limit
"limit": 3
offset
offset
The number of rows to skip
// example - offset
"offset": 100
Last updated