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]

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]

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

Define the main entity of the query

// example - main entity caluse
"entity": "customer"

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

The entity to join

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

Give an alias to the joined entity

timeAggregation

The time aggregation definition

// example - time aggregation
"timeAggregation": {
   "timeGrain": "day",
   "windowSize": 3,
   "direction": "backward"
},

The properties for timeAggregation are:

timeGrain

The time grain to aggregate by (day / week / month etc..) For example: "day", for 7 days backward

windowSize [optional]

The window size to apply (numeric value), in case of time window aggregation. For example: "7 ", for 7 days backward

direction [optional]

The direction to apply (backward / forward), in case of time window aggregation. For example: "backward", for 7 days backward.

For more information about time aggregations, visit Time Aggregations.

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]

Use this option for fetching entity features

// example - select - field
"select": [
   {
     "type" : "field",
     "entity": "customer",
     "field" : "customer_id",
     "alias": "cid"
   }
]

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)

Measures are reusable aggregate definitions that can be applied on entity rollup.

Just like regular SQL, when using measures (aggregate functions), make sure to put the rest of the entity features in the GROUP BY clause.

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

Apply filters to the query

There are two types of filters you can apply in the where clause:

• fields

• sql

Note that you can apply either fields or sql filters, but not both at the same where clause. (The effect will be the same whichever way you choose to apply as filter)

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 to

• field - the entity feature to filter on

• operator - 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

Apply a simple sql expression to use for filtering

// example - where - sql
"where": {
  "type": "sql",
  "sql": "{customer}.{nation_id} in ('40', '42')"
}

groupBy

Array of fields to use in the group by clause

// example - groupBy
"groupBy": ["1", "2"]

having

Apply filters to the query using aggregated fields

There are two types of filters you can apply in the having clause:

• fields

• sql

Note that you can apply either fields or sql filters, but not both at the same having clause. (The effect will be the same whichever way you choose to apply as filter)

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 to

• measure - the entity measure to filter on

• operator - 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

Apply a simple sql expression to use for filtering

// example - having - sql
"where": {
  "type": "sql",
  "sql": "{customer}.{total_sales} > ('100')"
}

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 from

• field - the entity field to sort by

• direction - “desc” or “asc”. (default “asc”)

// example - sort
"orderBy": [
   {"entity": "customer", "field": "total_sales", "direction": "desc"}
 ]

limit

The number of rows to return

// example - limit
"limit": 3

offset

The number of rows to skip

// example - offset
"offset": 100