Tutorial

The REPL can be obtained from a Github release.

The root folder partiql-cli contains a README.md file and 3 subfolders

To exit the PartiQL REPL, press:

or close the terminal/command prompt window.

An easy way to load the necessary data into the REPL is use the -e switch when starting the REPL and provide the name of a file that contains your data.

You can then see what is loaded in the REPL’s global environment using the REPL command !global_env, i.e.,

For convenience we have provided the file tutorial-all-data.env in the folder Tutorial/code/. You will also find separate .env files in the same folder for each query in the tutorial.

For example, running

will load all the data used in the tutorial in the REPL. This will allow you to copy-paste queries from the tutorial into the REPL and try them out.

Literals of the PartiQL query language correspond to the types in the PartiQL data model:

Notice that in the spirit of the PartiQL data model, literals compose freely and any kind of literal may appear within any tuple, array and bag literal, eg.,

Let’s now add the nested attribute projects into the data set.

Notice that the value of 'projects' is an array. Arrays are denoted by [ ... ] with array elements separated by commas. In our example the array happens to be an array of tuples. We will see that arrays may be arrays of anything, not just arrays of tuples.

A value may also be a tuple — also called object and struct in many models and formats. For example, the project value in the following tuples is always a tuple with project name and project org.

PartiQL’s multistep paths enable navigating within tuples. For example, the following query finds AWS projects and outputs the project name and employee name.

The result is

The previous examples have shown nested attributes that were arrays of tuples. It need not be the case that the nested attributes are collections of tuples. They may just as well be arrays of scalars, arrays of arrays, or any combination of data that one can create by composing scalars, tuples and arrays. You need not learn a different set of query language features for each case. The unnesting features, which we have already seen, are sufficient.

Let’s go back to the hr.employees table (that is, bag of tuples). Bob Smith has no title and, as is typical in SQL, the lack of title is modeled with the null value.

Nowadays, many semi-structured formats allow users to represent "missing" information in two ways.

That is, we can represent the fact that Bob Smith has no title by simply having no title attribute in the 'Bob Smith' tuple:

PartiQL does not argue about when to use nulls and when to use "missing". Myriads of datasets already use one of the two or both. However, PartiQL enables queries to distinguish between null and missing values, and also enables query results that have nulls and missing values. Indeed, PartiQL makes it very easy to propagate source data nulls as query result nulls and source data missing attributes into result missing attributes.

Consider again this PartiQL query, which happens to also be an SQL query.

What will happen when the query goes over the Bob Smith tuple, which has no title?

The first step to answering this question is understanding the result of the path e.title when the alias (variable) e binds to the tuple { 'id': 3, 'name': 'Bob Smith' }. In more basic terms, what is the result of the expression { 'id': 3, 'name': 'Bob Smith' }.title ? PartiQL says that it is the special value MISSING. MISSING behaves very similar to null.

A PartiQL variable (called alias in SQL) can bind to data of different types during a query’s evaluation. This is unlike SQL where the variables always bind to tuples. It is even different from what happened in Use Case: Unnesting Arrays of Scalars and what happened in Use Case: Unnesting Arrays of Arrays.

In the first use case, the PartiQL variable p happened to always bind to a string (given the particular sample data of the example). In the second use case, the PartiQL variable y was always bound to an array (again, given the particular sample data of the example).

To make the case for variables that bind to different types, consider the following twist in the employeesNest data set. Some of the elements of the projects array are plain strings and some are tuples. Even the employee tuples do not always have the same attributes.

This query on hr.employeesMixed2 produces employee name — employee project pairs.

Notice the sub-expression (p IS TUPLE). The IS operator can be used to check a value against its type at evaluation time. Notice also that the variable p binds to different types.

In general, the FROM clause of a query binds its variables (aliases) to data. The variables need not bind to data that have the same types. Each binding is fed to the SELECT clause, which evaluates its expressions.

This table shows each variables' binding produced by the FROM clause and the corresponding tuple output by the SELECT clause.

{
  'id': 3,
  'name': 'Bob Smith',
  'title': null,
  'projects': [
      { 'name': 'AWS Redshift Spectrum querying' },
      'AWS Redshift security',
      { 'name': 'AWS Aurora security' }
  ]
}

{ 'name': 'AWS Redshift Spectrum querying' }

{
 'employeeName': 'Bob Smith',
 'projectName': 'AWS Redshift Spectrum querying'
}

{ 'id': 3,
  'name': 'Bob Smith',
  'title': null,
  'projects': [
      { 'name': 'AWS Redshift Spectrum querying' },
      'AWS Redshift security',
      { 'name': 'AWS Aurora security' }
  ]
}

'AWS Redshift security'

{
  'employeeName': 'Bob Smith',
  'projectName': 'AWS Redshift security'
}

{ 'id': 3,
  'name': 'Bob Smith',
  'title': null,
  'projects': [
      { 'name': 'AWS Redshift Spectrum querying' },
      'AWS Redshift security',
      { 'name': 'AWS Aurora security' }
  ]
}

{ 'name': 'AWS Aurora security' }

{
  'employeeName': 'Bob Smith',
  'projectName': 'AWS Aurora security'
}

{ 'id': 6,
  'name': 'Jane Smith',
  'projects': [ 'AWS Redshift security' ]
}

'AWS Redshift security'

{
  'employeeName': 'Jane Smith',
  'projectName': 'AWS Redshift security'
}

SQL allows us to order the output of a query using the ORDER BY clause. However, the SQL data model does not recognize order in the input data. In contrast, many of the new data formats feature arrays; the arrays' elements have an order. We may want to find an array element by its index or, we may want to find the positions of certain elements in their arrays.

Let’s consider again the dataset hr.employeesNest.

The projects attribute is an array of tuples; that is, each tuple has an ordinal associated with it. The following query returns each employee name, along with the first project of the employee.

The query returns

Technically, the structure [<number>] is a kind of path step. For example, notice the 4-step path e.projects[0].name. When e is bound to the first tuple of hr.employeesNest, then the path e.projects results into the array

Consequently applying the [0] step on e.projects (that is, evaluating e.projects[0]) leads to {'name': 'AWS Redshift Spectrum querying'}. Finally, evaluating the .name step on e.projects[0] (that is, evaluating e.projects[0].name) leads to 'AWS Redshift Spectrum querying'.

Let’s assume that each employees' projects are sorted in priority order. The following query finds the names of each employee involved in a security project, the security project, and, its index in the projects array.

Notice the new feature: AT o. While p ranges over the elements of the array e.projects, the variable o is assigned to the index of the element in the array. The query returns:

Consider this dataset that provides the closing prices of multiple ticker symbols.

The following query unpivots the stock ticker/price pairs.

Notice the use of " in this query. The double quotes allow us to disambiguate from date the keyword and "date" the identifier. Double quotes can also specify case sensitivity for attribute lookups.

The query returns

Unpivoting tuples enables the use of attribute names as if they were data. For example, it becomes easy to compute the average price for each symbol as

which returns

Pivoting turns a collection into a tuple. For example, consider the collection

Then the following PIVOT query

produces the tuple

Notice that the PIVOT query looks like a SELECT-FROM-WHERE-... query except that instead of a SELECT clause it has a PIVOT <value expression> AT <attribute expression>. Note also that the PIVOT query does not return a collection of tuples: rather it literally returns a tuple value.

This example also uses the grouping features of PartiQL.

Let us generalize the previous case of pivoting. We have a table of stock prices

and we want to pivot it into a collection of tuples, where each tuple has all the symbol:price pairs for a date, as follows

The following query first creates one group datesPrices for each date. Then the PIVOT subquery pivots the group into the tuple prices.

For example, the datesPrices collection, returned from GROUP AS for sp.date = date(4/1/2019) is

Let’s consider again the dataset hr.employeesNestScalars:

The following query outputs each tuple of hr.employeesNestScalars, except that instead of all projects each tuple has only the security projects of the employee. The important new feature here is the SELECT VALUE <expression>.

The result is

A SELECT VALUE <expression> query (or subquery, as in this example) returns a collection of whatever the <expression> evaluates to.

Notice the difference from SQL’s SELECT, which always produces tuples. If a SQL SELECT appears as a subquery, then the context of the subquery designates whether the subquery’s result should be coerced into a scalar (e.g., when 5 = <subquery>), coerced into a collection of scalars (e.g., when 5 IN <subquery>), etc. None of this applies to SELECT VALUE, which produces a collection and this collection is not coerced.

Another pattern of creating nested results in PartiQL is via the GROUP AS extension to SQL’s GROUP BY. This pattern is more efficient and more intuitive than the use of nested SELECT VALUE queries when the required nesting is not following the nesting of the input. (The example in Creating Nested Results with SELECT VALUE Queries is one where the nesting in the output follows the nesting of the input and thus, an intuitive solution does not involve GROUP BY.)

The following query outputs each security project found in hr.employeesNestScalars along with the list of employee names that work on the project.

The result is

The GROUP AS generalizes SQL’s GROUP BY by making the formulated groups available in their entirety to the query’s SELECT and HAVING clauses. Contrast with SQL’s GROUP BY, where the SELECT and HAVING clauses can have aggregate functions over grouped columns but they cannot get access to the individual values of the grouped columns.

To better understand the workings of GROUP BY ... GROUP AS it is best to think of PartiQL queries as a pipeline of clauses, starting with the FROM, continuing with the GROUP BY and finishing with the SELECT. Each clause is a function that inputs data and outputs data. In that sense, the GROUP BY ... GROUP AS is a function that inputs the result of the FROM and outputs its result to the SELECT.

The following query (conceptually) produces the output of the FROM clause.

We see that the FROM delivers the collection of tuples consisting of an employee e and a project p that were output by the FROM clause, i.e., the LEFT JOIN. This is like SQL’s FROM semantics.

{ 'id': 3,
  'name': 'Bob Smith',
  'title': null,
  'projects': [ 'AWS Redshift Spectrum querying',
                'AWS Redshift security',
                'AWS Aurora security'
              ]
}

'AWS Redshift security'

{ 'id': 3,
  'name': 'Bob Smith',
  'title': null,
  'projects': [ 'AWS Redshift Spectrum querying',
                'AWS Redshift security',
                'AWS Aurora security'
              ]
}

'AWS Aurora security'

{ 'id': 6,
   'name': 'Jane Smith',
   'title': 'Software Eng 2',
   'projects': [ 'AWS Redshift security' ]
}

'AWS Redshift security'

Then the GROUP BY ... GROUP AS ... can be thought of as outputting a table that has one column for each group-by expression (i.e., each security project p) and a last column perProjectGroup whose value (conceptually) is the collection of employee/project e/p tuples that correspond to the group-by expression p. Thus the GROUP BY ... GROUP AS ... output is the table

'AWS Redshift security'

<<
    { e: { 'id': 3, 'name': 'Bob Smith', …​ },
      p: 'AWS Redshift security' },
    { e: { 'id': 6, 'name': 'Jane Smith', …​ },
      p: 'AWS Redshift security' }
>>

'AWS Aurora security'

<<
    { e: { 'id': 3, 'name': 'Bob Smith', …​},
      p: 'AWS Aurora security' },
>>

Finally the SELECT clause inputs the above and outputs the query result.