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

PreviousSQL API NextCache & Pre-Aggregations

Last updated 4 months ago

Connecting to Lynk SQL REST API

branch [optional]

context [optional]

Authentication

Request body - simple example

Request body parameters

entity

joins

entity

join name [optional]

alias

timeAggregation

timeGrain

windowSize [optional]

direction [optional]

select

field [select]

measure [select]

lynk_function [select]

where

where - fields

where - sql

groupBy

having

having - fields

having - sql

orderBy

limit

offset

`branch` [optional]

`context` [optional]

`entity`

`joins`

`entity`

`join name [optional]`

`alias`

`timeAggregation`

`timeGrain`

`windowSize` [optional]

`direction` [optional]

`select`

`field` [`select`]

`measure` [`select`]

`lynk_function` [`select`]

`where`

`where` - `fields`

`where` - `sql`

`groupBy`

`having`

`having` - `fields`

`having` - `sql`

`orderBy`

`limit`

`offset`