Skip to main content

Pipeline Query Builder

The query builder is a strongly typed object used in Pipelines to define the query that should be used to extract data from a Source. The query builder Typescript type Query is defined in the @telepath/telepath package.

The most helpful thing to understand about the query builder is that it ultimately transpiles to SQL on the backend. So, most of the terms used by the query builder correspond to SQL operators. Understanding this makes it much easier to intuit how to construct your query.

Table and Column Identifier Syntax​

In many places in the query, you may need to reference a table or column name. Like SQL, there are multiple ways this can be done:

  • tableName.columnName is the safest way to reference a column because it avoids ambiguity when multiple tables in the query may have duplicate column names (e.g. id or created_at)
  • tableName or columnName alone can be used if the reference is distinct (no other table or column has the same name within the query).

If a table or column has been assigned an alias (e.g. tableName as myTableAlias) then you should refer to the alias, just as you would in SQL (e.g. myTableAlias.columnName).

Aliases can be assigned to tables, columns, and sub-queries in a few different ways, depending on the operation, which are defined throughout these docs.

Known Limitations and Gotchas!​

Pipelines must output unique column names​

Unlike many SQL databases, the results output from a Telepath Pipeline cannot contain any duplicate column names. If multiple tables in the query contain columns with the same name (e.g. id), you must either SELECT only one of them or assign them unique aliases.

Query Builder API​

select (optional)​

Creates a SQL SELECT statement that defines which columns should be included in the query results. If no select property is defined, Telepath will default to SELECT *.

Accepts a key-value object where the key defines the column alias and the value references the column to be selected. Typescript type: QuerySelect.

select: {
  myColumnAlias: 'tableName.columnName'
}

Transpiles to SQL:

SELECT tableName.columnName AS myColumnAlias

If the column does not require an alias, then you can save time by using an optional boolean format:

{
  select: {
    columnName: true,
    'tableName.columnName': true
  }
}

Transpiles to SQL:

SELECT columnName, tableName.columnName

DISTINCT

Select distinct column values by wrapping the column in a distinct: { ... } object.

{
  select: {
    myDistinctColumn: {
      distinct: 'tableName.columnName'
    }
  }
}

Transpiles to SQL:

SELECT DISTINCT tableName.columnName AS myDistinctColumn

Aggregate Functions

The following aggregations are supported:

  • SUM
  • MIN
  • MAX
  • AVG
  • COUNT
{
  select: {
    // SUM
    mySum: {
      sum: 'tableName.columnName'
    },

    // MIN
    myMin: {
      min: 'columnName'
    },

    // MAX
    myMax: {
      max: 'columnName'
    },

    // AVG
    myAvg: {
      avg: 'columnName'
    },

    // COUNT
    myCount: {
      count: 'columnName'
    },

    // Aggregate functions can also be combined with DISTINCT
    myDistinctCount: {
      count: {
        distinct: 'tableName.columnName'
      }
    }
  }
}

Transpiles to SQL:

SELECT 
    SUM(tableName.columnName) AS mySum, 
    MIN(columnName) AS myMin,
    MAX(columnName) AS myMax,
    AVG(columnName) AS myAvg,
    COUNT(columnName) as myCount,
    COUNT(DISTINCT tableName.columnName) AS myDistinctCount

from​

Creates a SQL FROM statement that defines which resource or sub-query the query is reading from.

There are two different input formats, depending on whether you're reading from a Source (type: SourceTableRef) or from a CTE (a sub-query defined in the with property).

From a Source​

{
  from: {
    // Reference the ID of a Source resource
    sourceId: mySource.id,
    // Name of the table to read from the Source
    table: 'my_table'
  }
}

// Assign an Alias
{
  from: {
    sourceid: mySource.id,
    table: 'my_table',
    as: 'myTableAlias'
  }
}

Transpiles to SQL:

FROM my_table

/* With Alias */
FROM my_table AS myTableAlias

**From CTE (a sub-query defined in the with property)**

{
  // Define some sub-query in the "with" property. 
  with:
  {
    mySubQuery: { ...
    }
  }
,
  // Reference the sub-query
  from: 'mySubQuery'
}

Transpiles to SQL:

WITH mySubQuery AS (...) 
[SELECT *] FROM mySubQuery

with​

Creates a SQL WITH statement, commonly referred to as a CTE (Common Table Expression) or a named sub-query. CTEs are a very useful for keeping your queries clean and modular. If you are unfamiliar with them, here is a good 5-minute overview video.

Accepts a key-value object where the key is the alias to assign to the CTE and the value is a Query. Typescript type: QueryWith

{
  with: {
    myCte: {
      from: {
        sourceId: mySource.id,
        table: 'my_table'
      }
    }
  }
}

Transpiles to SQL:

WITH myCTE AS (SELECT * FROM my_table)

join​

Creates SQL JOIN statements.

Accepts an array of objects with each object representing a single join. Typescript type: QueryJoin

The target property defines the table that should be joined. It accepts either the name of a table or CTE that has already been defined in the query or a reference to a Source (type: SourceTableRef).

Join a previously-declared table or CTE​

{
  join: [{
    // Type of join: INNER, LEFT, RIGHT, FULL
    // Defaults to INNER, if not specified.
    type: 'LEFT'
    // The table or sub-query that should be joined
    target: 'users',
    // Optional alias to assign to the joined table
    as: 'myJoinAlias',
    // The columns to join on. 
    // Accepts an array containing two column names or an
    // array of arrays, if you're joining on more than one column.
    on: [
      ['myJoinAlias.id', 'anotherTable.user_id'],
      ['myJoinAlias.date', 'anotherTable.date']
    ]
  }]
}

Transpiles to SQL:

LEFT JOIN users AS myJoinAlias 
    ON myJoinAlias.id = anotherTable.user_id 
        AND myJoinAlias.date = anotherTable.date

Join a Source​

{
  join: [{
    target: {
      // Reference the ID of a Source resource
      sourceId: mySource.id,
      // Name of the table to read from the Source
      table: 'users'
    },
    // The columns to join on
    on: ['users.id', 'anotherTable.user_id']
  }]
}

Tranpiles to SQL:

JOIN users ON users.id = anotherTable.user_id

where​

Creates a SQL WHERE statement.

Accepts a key-value object where the keys are the names of columns and the values are the WHERE operators to apply. Typescript type: QueryWhere.

As a shorthand for the Equals operator, a column value can be provided instead of an operator object (see example below) .

Additionally, conditions can be grouped together using the keys “AND”, “OR”, and “NOT”, as detailed below.

The following is a limited example usage. See the documentation below for additional supported operators.

where: {
  'tableName.price'
:
  {
    gt: 100
  }
,
  // If only a value is provided, the Equals operator will be used
  'tableName.product_category'
:
  'clothing'
}

Transpiles to SQL:

WHERE tableName.price > 100 
    AND tableName.product_category = 'clothing'

Using NULL value​

Use Javascript's null primitive when explicitly defining a NULL value.

where: {
  'tableName.column'
:
  {
    notEq: null
  }
}

Transpiles to SQL:

WHERE tableName.column IS NOT NULL

Comparing to the value of another column​

To use reference the value of another column, wrap the column name in a key-value object with the key column. Typescript type: QueryValueColumnRef

where: {
  'tableName.column_1'
:
  {
    gt: {
      column: 'tableName.column_2'
    }
  }
}

// DO NOT do this, or the column name will be treated as a string
where: {
  'tableName.column_1'
:
  {
    gt: 'tableName.column_2'
  }
}

Transpiles to SQL:

WHERE tableName.column_1 > tableName.column_2

/* DO NOT do this, or the column name will be treated as a string */
WHERE tableName.column_1 > 'tableName.column_2'

Group conditions with AND / OR / NOT​

WHERE conditions are grouped with AND operators by default, but conditions can be combined using other operators by grouping them into an array and assigning an AND, OR, and NOT key.

note

If your data happens to have columns named “AND”, “OR”, or “NOT”, then you must use the tableName.columnName format to use them in a where condition. Otherwise, the query builder will treat the words as conditions of the WHERE operation instead of as column names.

{
  where: {
    // Top-level conditions will be joined with the default "AND" operator
    'customer.country': 'California',
    // Conditions in the "OR" array will be joined with "OR" operator
    OR: [
      { 'customer.city': 'Los Angeles' },
      { 'customer.city': 'San Francisco' }
    ]
    
    // Conditions in the "NOT" array will be wrapped in a "NOT" operator
    NOT: [
      {
        'customer.balance': {
           gt: 500
        }
      },
      {
        'customer.zip_code': '90210'
      }
    ]
  }
}

Transpiles to SQL:

WHERE customer.country = 'California'
    AND (
        customer.city = 'Los Angeles' 
        OR customer.city = 'San Francisco'
    )
    AND NOT (
        customer.balance > 500
        AND customer.zip_code <> '90210'
    )

AND / OR / NOT operators can be combined and nested​

where: {
  // Conditions will be joined with "OR" operators
  OR: [
    { 'customer.city': 'Los Angeles' },
    { 'customer.city': 'San Francisco' },
    
    {
      // Conditions will be joined with "AND" operators
      AND: [
        { 'customer.city': 'San Diego' },
        { 'customer.neighborhood': 'La Jolla' }
      ]
    }
  ],
  
  // Conditions will be wrapped in "NOT" operator
  NOT: [
    {
      // Conditions will be joined with "OR" operators
      OR: [
        {
          'customer.purchase_count': {
          lt: 10
        },
        {
          'customer.is_preferred': false
        }
      ]
    }
  ]
}

Transpiles to SQL:

WHERE 
  (
    customer.city = 'Los Angeles'
    OR customer.city = 'San Francisco'
    OR (
      customer.city = 'San Diego'
      AND customer.neighborhood = 'La Jolla'
    )
    )
    
    AND NOT (
    customer.purchase_count < 10
    OR customer.is_preferred = FALSE
    )

Equals​

Typescript type: QueryEquals

In addition to the normal Equals operator, a shorthand can be used by just providing a column value.

{
  where: {
    'tableName.column_1': {
      eq: 'my value'
    },

    // Or use the shorthand by just providing a value
    'tableName.column_2: 'another value'
  }
}

Transpiles to SQL:

WHERE tableName.column_1 = 'my value'
    AND tableName.column_2 = 'another value'

Not Equals​

Typescript type: QueryNotEquals

{
  where: {
    'tableName.column': {
      notEq: 'my value'
    }
  }   
}

Transpiles to SQL:

WHERE tableName.column <> 'my value'

Greater Than​

Typescript type: QueryGreaterThan

{
  where: {
    'tableName.column': {
      gt: 100
    }
  }    
}

Transpiles to SQL:

WHERE tableName.column > 100

Greater Than or Equal To​

Typescript type: QueryGreaterThanOrEqualTo

{
  where: {
    'tableName.column': {
      gte: 100
    }
  }   
}

Transpiles to SQL:

WHERE tableName.column >= 100

Less Than​

Typescript type: QueryLessThan

{
  where: {
    'tableName.column': {
      lt: 100
    }
  }
}

Transpiles to SQL:

WHERE tableName.column < 100

Less Than or Equal To​

Typescript type: QueryNotEquals

{
  where: {
    'tableName.column': {
      lte: 100
    }
  }   
}

Transpiles to SQL:

WHERE tableName.column <= 100

In​

Typescript type: QueryIn

{
  where: {
    'tableName.column': { 
      in: ['dog', 'cat', 'bird']
    }
  }   
}

Transpiles to SQL:

WHERE tableName.column IN ('dog', 'cat', 'bird')

Not In​

Typescript type: QueryNotIn

{
  where: {
    'tableName.column': {
      notIn: ['chicken', 'frog', 'rabbit']
    }
  }
}

Transpiles to SQL:

WHERE tableName.column NOT IN ('chicken', 'frog', 'rabbit')

Like​

Typescript type: QueryLike

{
  where: {
    'tableName.column': {
      like: '%amazing%'
    }
  }   
}

Transpiles to SQL:

WHERE tableName.column LIKE '%amazing%'

Not Like​

Typescript type: QueryNotLike

{
  where: {
    'tableName.column': {
      notLike: '%boring%'
    }
  }
}

Transpiles to SQL:

WHERE tableName.column NOT LIKE '%boring%'

Between​

Typescript type: QueryBetween

Accepts an array with two values: [min, max]

{
  where: {
    'tableName.column': {
      between: [10, 20]
    }
  }
}

Transpiles to SQL:

WHERE tableName.column BETWEEN 10 AND 20

Not Between​

Typescript type: QueryNotEquals

Accepts an array with two values: [min, max]

{
  where: {
    'tableName.column': {
      notBetween: [10, 20]
    }
  }
}

Transpiles to SQL:

WHERE tableName.column NOT BETWEEN [10, 20]

groupBy​

Creates a SQL GROUP BY statement.

Accepts either a single column name, or an array of column names.

{
  groupBy: 'myTable.columnName'
}

// GROUP BY multiple columns
{
  groupBy: ['myTable.column_1', 'myTable.column_2']
}

Transpiles to SQL:

GROUP BY myTable.columnName

/* Multple columns */
GROUP BY myTable.column_1, myTable.column2

having​

Creates a SQL HAVING statement.

Accepts a key-value object where the keys are names of columns and the values are the HAVING operators to apply. Typescript type: QueryHaving

The behavior of having is almost identical to the behavior of where, with the following exceptions:

  • HAVING conditions are always AND (OR and NOT are not supported).
  • Not all WHERE operations are supported as HAVING operations. See the supported list below.

The following operators are supported. You can see more example usage for each of them in the documentation for where.

  • Equals (type: QueryEquals)
  • Not Equals (type: QueryNotEquals)
  • Greater Than (type: QueryGreaterThan)
  • Greater Than or Equal To (type: QueryGreaterThanOrEqualTo)
  • Less Than (type: QueryLessThan)
  • Less Than or Equal To (type: QueryLessThanOrEqualTo)
  • In (type: QueryIn)
  • Not In (type: QueryNotIn)
  • Between (type: QueryBetween)
  • Not Between (type: QueryNotBetween)

As a shorthand for the Equals operator, a column value can be provided instead of an operator object (see example below) .

{
  having: {
    'tableName.column_1': {
      gt: 500
    },

    // If only a value is provided, the Equals operator will be used
    'tableName.column_2': 'some value'
  }
}

Transpiles to SQL:

HAVING tableName.column_1 > 500 
    AND tableName.column_2 = 'some value'

orderBy​

Creates a SQL ORDER BY statement that defines the column(s) that should be used for sorting query results.

Accepts a single object or an array of objects that define the columns, sort direction, and (optionally) null position to sort the query results as. Typescript type: OrderByColumn

There are two different key-value object formats supported for specifying each order-by column:

  • Key-value pair where the key defines the column and the value defines the sort order. Typescript type: OrderByDirection
  • Key-value pair where the key defines the column and the value defines both the sort order and which position to put any null values (first or last). Typescript type: OrderByWithNulls

Simple ORDER BY with sort direction

{
  orderBy: {
    'tableName.columnName': 'DESC' // Direction must be ASC or DESC
  }
}

// ORDER BY multiple columns
{
  orderBy: [
    {
      'tableName.column_1': 'ASC'
    },
    {
      'tableName.column_2': 'DESC'
    }
  ]
}

Transpiles to SQL:

ORDER BY tableName.columnName DESC

// Multiple columns
ORDER BY tableName.column_1 ASC, tableName.column_2 DESC

ORDER BY with sort direction and NULLs position

{
  orderBy: {
    'tableName.columnName': {
      // Sort direction must be ASC or DESC
      sort: 'ASC',
      // NULL position must be FIRST or LASST
      nulls: 'FIRST'
    }
  }
}

// Or ORDER BY multiple columns
{
  orderBy: [
    {
      'tableName.column_1': {
        sort: 'ASC',
        nulls: 'LAST'
      }
    },
    {
      'tableName.column_2': {
        sort: 'DESC',
        nulls: 'FIRST'
      }
    }
  ]
}

Transpiles to SQL:

ORDER BY tableName.columnName ASC NULLS FIRST

// Multiple columns
ORDER BY tableName.column_1 ASC NULLS LAST, tableName.column_2 DESC NULLS FIRST

offset​

Creates a SQL OFFSET statement.

Accepts an integer value.

{
  offset: 1000
}

Transpiles to SQL:

OFFSET 1000

limit​

Creates a SQL LIMIT statement.

Accepts an integer value.

{
  limit: 50
}

Transpiles to SQL:

LIMIT 50

Use Javascript's null primitive when explicitly defining a NULL value.

{
  where: {
    'tableName.column': {
      notEq: null
    }
  }
}

Transpiles to SQL:

WHERE tableName.column IS NOT NULL