Introduction

SuperDB is a new type of analytics database that promises an easier approach to modern data because it unifies relational tables and eclectic JSON in a powerful, new data model called super-structured data.

Note

The SuperDB implementation is open source and available as a GitHub repository. Pre-built binaries may be downloaded and installed via customary mechanisms.

Super-structured data is

• dynamic so that data collections can vary by type and are not handcuffed by schemas,
• strongly typed ensuring that all the benefits of a comprehensive type apply to dynamic data, and
• self-describing thus obviating the need to define schemas up front.

SuperDB has taken many of the best ideas of current data systems and adapted them for super-structured data with the introduction of:

• the super-structured data model,
• several super-structured serialization formats,
• a SQL-compatible query language adapted for super-structured data,
• a super-structured query engine, and
• a super-structured database format compatible with cloud object stores.

To achieve high performance for the dynamically typed data that lies at the heart of super-structured data, SuperDB has devised a novel vectorized runtime built as a clean slate around algebraic types. This contrasts with the Frankenstein approach taken by other analytics systems that shred variants into relational columns.

Putting JSON into a relational table — whether adding a JSON or variant column to a relational table or performing schema inference that does not always work — is like putting a square peg in a round hole. SuperDB turns this status quo upside down where JSON and schema-constrained relational tables are simply special cases of the more general and holistic super-structured data model.

This leads to ergonomics for SuperDB that are far better for the query language and for managing data end to end because there is not one way for handling relational data and a different way for managing dynamic data — relational tables and eclectic JSON data are treated in a uniform way from the ground up. For exmaple, there’s no need for a set of Parquet input files to all be schema-compatible and it’s easy to mix and match Parquet with JSON across queries.

Super-structured Data

Super-structured data is strongly typed and self describing. While compatible with relational schemas, SuperDB does not require such schemas as they can be modeled as super-structured records.

More specifically, a relational table in SuperDB is simply a collection of uniformly typed records, whereas a collection of dynamic but strongly-typed data can model any sequence of JSON values, e.g., observability data, application events, system logs, and so forth.

Thus, data in SuperDB is

• strongly typed like databases, but
• dynamically typed like JSON.

Self-describing data makes data easier: when transmitting data from one entity to another, there is no need for the two sides to agree up front what the schemas must be in order to communicate and land the data.

Likewise, when extracting and serializing data from a query, there is never any loss of information as the super-structured formats capture all aspects of the strongly-typed data whether in human-readable form, binary row-like form, or columnar-like form.

The super Command

SuperDB is implemented as the standalone, dependency-free super command. super is a little like DuckDB and a little like jq but super-structured data ties these two command styles together with strong typing of dynamic data.

Because super has no dependencies, it’s easy to get going — just install the binary and you’re off and running.

SuperDB separates compute and storage and is decomposed into a runtime system that

• runs directly on any data inputs like files, streams, or APIs, or
• manipulates and queries data in a persistent storage layer — the SuperDB database — that rhymes in design with the emergent lakehouse pattern but is based on super-structured data.

To invoke the SuperDB runtime without a database, just run super without the db subcommand and specify an optional query with -c:

super -c "SELECT 'hello, world'"

To interact with a SuperDB database, invoke the db subcommands of super and/or program against the database API.

Note

The persistent database layer is still under development and not yet ready for turnkey production use.

Why Not Relational?

The fashionable argument against a new system like SuperDB is that SQL and the relational model (RM) are perfectly good solutions that have stood the test of time so there’s no need to replace them. In fact, a recent paper from legendary database experts argues that any attempt to supplant SQL or the RM is doomed to fail because any good ideas that arise from such efforts will simply be incorporated into SQL and the RM.

Yet, the incorporation of the JSON data model into the relational model never fails to disappoint. One must typically choose between creating columns of a JSON or variant type that layers in a parallel set of operators and behaviors that diverge from core SQL semantics, or rely upon schema inference to convert variant data into relational tables, which unfortunately does not always work.

To understand the difficulty of schema inference, consider this simple line of JSON data is in a file called example.json:

{"a":[1,"foo"]}

Note

The literal [1,"foo"] is a contrived example but it adequately represents the challenge of mixed-type JSON values, e.g., an API returning an array of JSON objects with varying shape.

Surprisingly, this simple JSON input causes unpredictable schema inference across different SQL systems. Clickhouse converts the JSON number 1 to a string:

$ clickhouse -q "SELECT * FROM 'example.json'"
['1','foo']

DuckDB does only partial schema inference and leaves the contents of the array as type JSON:

$ duckdb -c "SELECT * FROM 'example.json'"
┌──────────────┐
│      a       │
│    json[]    │
├──────────────┤
│ [1, '"foo"'] │
└──────────────┘

And DataFusion fails with an error:

$ datafusion-cli -c "SELECT * FROM 'example.json'"
DataFusion CLI v46.0.1
Error: Arrow error: Json error: whilst decoding field 'a': expected string got 1

It turns out there’s no easy way to represent this straightforward literal array value [1,'foo'] in these SQLs, e.g., simply including this value in a SQL expression results in errors:

$ clickhouse -q "SELECT [1,'foo']"
Code: 386. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString/Enum and some of them are not. (NO_COMMON_TYPE)

$ duckdb -c "SELECT [1,'foo']"
Conversion Error:
Could not convert string 'foo' to INT32

LINE 1: SELECT [1,'foo']
                  ^

$ datafusion-cli -c "SELECT [1,'foo']"
DataFusion CLI v46.0.1
Error: Arrow error: Cast error: Cannot cast string 'foo' to value of Int64 type

The more recent innovation of an open variant type is more general than JSON but suffers from similar problems. In both these cases, the JSON type and the variant type are not individual types but rather entire type systems that differ from the base relational type system and so are shoehorned into the relational model as a parallel type system masquerading as a specialized type to make it all work.

Maybe there is a better way?

Enter Algebraic Types

What’s missing here is an easy and native way to represent mixed-type entities. In modern programming languages, such entities are enabled with a sum type or tagged union.

While the original conception of the relational data model anticipated “product types” — in fact, describing a relation’s schema in terms of a product type — it unfortunately did not anticipate sum types.

Note

Codd’s original paper on the relational model has a footnote that essentially describes as a product type:

But sum types were notably absent.

Armed with both sum and product types, super-structured data provides a comprehensive algebraic type system that can represent any JSON value as a concrete type. And since relations are simply product types as originally envisioned by Codd, any relational table can be represented also as a super-structured product type. Thus, JSON and relational tables are cleanly unified with an algebraic type system.

In this way, SuperDB “just works” when it comes to processing the JSON example from above:

$ super -c "SELECT * FROM 'example.json'"
{a:[1,"foo"]}
$ super -c "SELECT [1,'foo'] AS a"
{a:[1,"foo"]}

In fact, we can see algebraic types at work here if we interrogate the type of such an expression:

$ super -c "SELECT typeof(a) as type FROM (SELECT [1,'foo'] AS a)"
{type:<[int64|string]>}

In this super-structured representation, the type field is a first-class type value representing an array type of elements having a sum type of int64 and string.

SuperSQL

Since super-structured data is a superset of the relational model, it turns out that a query language for super-structured data can be devised that is a superset of SQL. The SuperDB query language is a Pipe SQL adapted for super-structured data called SuperSQL.

SuperSQL is particularly well suited for data-wrangling use cases like ETL and data exploration and discovery. Syntactic shortcuts, keyword search, and the pipe syntax make interactively querying data a breeze. And language features like recursive functions with re-entrant subqueries allow for traversing nested data in a general and powerful fashion.

Instead of operating upon statically typed relational tables as SQL does, SuperSQL operates upon super-structured data. When such data happens to look like a table, then SuperSQL can work just like SQL:

$ super -c "
SELECT a+b AS x, a-b AS y FROM (
  VALUES (1,2),	(3,0)
) AS T(a,b)
"
{x:3,y:-1}
{x:3,y:3}

But when data does not conform to the relational model, SuperSQL can still handle it with its super-structured runtime:

$ super -c "
SELECT avg(radius) as R, avg(width) as W FROM (
  VALUES
    {kind:'circle',radius:1.5},
    {kind:'rect',width:2.0,height:1.0},
    {kind:'circle',radius:2},
    {kind:'rect',width:1.0,height:3.5}
)
"
{R:1.75,W:1.5}

Things get more interesting when you want to do different types of processing for differently typed entities, e.g., let’s compute an average radius of circles, and double the width of each rectangle. This time we’ll use the pipe syntax with shortcuts and employ first-class errors to flag unknown types:

$ super -c "
values
  {kind:'circle',radius:1.5},
  {kind:'rect',width:2.0,height:1.0},
  {kind:'circle',radius:2},
  {kind:'rect',width:1.0,height:3.5}
| switch kind
    case 'circle' (
        R:=avg(radius)
    )
    case 'rect' (
        width:=width*2
    )
"
{R:1.75}
{kind:"rect",width:4.,height:1.}
{kind:"rect",width:2.,height:3.5}

So what’s going on here? The data model here is acting both as a strongly typed representation of JSON-like sequences as well as a means to represent relational tables. And SuperSQL is behaving like SQL when applied to table-like data, but at the same time is a pipe-syntax language for arbitrarily typed data. The super-structured data model ties it all together.

To make this all work, the runtime must handle arbitrarily typed data. Hence, every operator in SuperSQL has defined behavior for every possible input type. This is the key point of departure for super-structured data: instead of the unit of processing being a relational table, which requires a statically defined schema, the unit of processing is a collection of arbitrarily typed values. In a sense, SuperDB generalizes Codd’s relational algebra to polymorphic operators. All of Codd’s relational operators can be recast in this fashion forming the polymorphic algebra of super-structured data implemented by SuperDB.

Evolving SQL

Despite SQL’s enduring success, it is widely accepted that there are serious flaws in the language and a number of authors argue that SQL should be replaced in its entirety. Among many such works, here are some noteworthy arguments:

• A Critique of the SQL Database Language a 1983 paper by C.J. Date,
• Against SQL by Jamie Brandon,
• A Critique of Modern SQL And A Proposal Towards A Simple and Expressive Query Language by Neumann and Leis.

A very different approach is taken in SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL, which argues that SQL should be merely improved upon and not replaced outright. Here the authors argue that except for compositional syntax, SQL is perfectly reasonable and we should live with its anachronisms (see Section 2.4). Thus, their Pipe SQL specification carries forward SQL eccentricities into their modern adaptation of pipes for SQL.

SuperSQL takes a different approach and seizes the opportunity to modernize the ergonomics of a SQL-compatible query language. While embracing backward compatibility, SuperSQL diverges significantly from SQL anachronisms in the pipe portion of the language by introducing pipe-scoping semantics that coexists next to the relational-scoping semantics of SQL tables and columns.

The vision here is that comprehensive backward compatibility can reside in the SQL operators while a modernized syntax and and improved ergonomics can reside in the pipe operators, e.g.,

• array indexing can be configured as 1-based in SQL clauses but 0-based in pipe operators,
• column names in SQL clauses are case insensitive while record field references are case sensitive in pipe operators,
• complex scoping rules for table aliases and column references are required in relational SQL while binding from names to data in pipe operators is managed in a uniform and simple way as derefenced paths on this,
• the syntactic structure of SQL clauses means all data must conform to a table whereas pipe operators can emit any data type desired in a varying fashion, and
• sum types are integral to piped data allowing mix-typed data processing and results that need not fit in a uniform table.

With this approach, SuperSQL can be adopted and used for existing use cases based on legacy SQL while incrementally expanding and embracing the pipe model tied to super-structured data. Perhaps this could enable a long-term and gradual transition away from relational SQL toward a modern and more ergonomic replacement.

The jury is out as to whether a Pipe SQL for super-structured data is the right approach for curing SQL’s ills, but it certainly provides a framework for exploring entirely new language abstractions while maintaining complete backward compatibility with SQL all in the same query language.

Note

If SuperDB or super-structured data has piqued your interest, you can dive deeper by:

• exploring the SuperSQL query language,
• learning about the super-structured data model and formats underlying SuperDB, or
• browsing the tutorials.

We’d love your feedback and we hope to build a thriving community around SuperDB so please feel free to reach out to us via

• Slack
• GitHub issues, or
• GitHub pull requests.

See you online!

Getting Started

It’s super easy to get going with SuperDB.

Short on time? Just browse the TL;DR.

Otherwise, try out the embedded playground examples throughout the documentation, or

• install super, and
• try it out.

The super command is a single binary arranged into a hierarchy of sub-commands.

SuperDB’s disaggregation of compute and storage is reflected into the design of its command hierarchy: running the top-level super command runs the compute engine only on inputs like files and URLs, while the db subcommands of super operate upon a persistent database.

To get online help, run the super command or any sub-command with -h, e.g.,

super -h

displays help for the top-level command, while

super db load -h

displays help for loading data into a SuperDB database, and so forth.

Note

While super and its accompanying data formats are production quality for many use cases, the project’s persistent database is a bit earlier in development.

TL;DR

TL;DR

Don’t have time to dive into the documentation?

Just skim these one liners to get the gist of what SuperDB can do!

Note that JSON files can include any sequence of JSON values like newline-deliminted JSON though the values need not be newline deliminated.

Query a CSV, JSON, or Parquet file using SuperSQL

super -c "SELECT * FROM file.[csv|csv.gz|json|json.gz|parquet]"

Run a SuperSQL query sourced from an input file

super -I path/to/query.sql

Pretty-print a sample value as super-structured data

super -S -c "limit 1" file.[csv|csv.gz|json|json.gz|parquet]

Compute a histogram of the “data shapes” in a JSON file

super -c "count() by typeof(this)" file.json

Display a sample value of each “shape” of JSON data

super -c "any(this) by typeof(this) | values any" file.json

Search Parquet files easily and efficiently without schema handcuffs

super *.parquet > all.bsup
super -c "? search keywords | other pipe processing" all.bsup

Read a CSV from stdin, process with a query, and write to stdout

cat input.csv | super -f csv -c <query> -

Fuse JSON data into a unified schema and output as Parquet

super -f parquet -o out.parquet -c fuse file.json

Run as a calculator

super -c "1.+(1/2.)+(1/3.)+(1/4.)"

Search all values in a database pool called logs for keyword “alert” and level >= 2

super db -c "from logs | ? alert level >= 2"

Handle and wrap errors in a SuperSQL pipeline

... | super -c "
switch is_error(this) (
    case true ( values error({message:"error into stage N", on:this}) )
    default (
        <non-error processing here>
        ...
    )
)
"
| ...

Embed a pipe query search in SQL FROM clause

super -c "
SELECT union(type) as kinds, network_of(srcip) as net
FROM ( from logs.json | ? example.com AND urgent )
WHERE message_length > 100
GROUP BY net
"

Or write this as a pure pipe query using SuperSQL shortcuts

super -c "
from logs.json
| ? example.com AND urgent
| message_length > 100
| kinds:=union(type), net:=network_of(srcip) by net
"

Installation

Installation

TODO: upon release, update this first paragraph.

Because SuperDB is still under construction, GA releases are not yet available. However, you can install a build of the super command-line tool based on code that’s under active development to start tinkering.

Multiple options for installing super are available:

• Homebrew for Mac or Linux,
• Build from source.

To install the SuperDB Python client, see the Python library documentation.

Homebrew

On macOS and Linux, you can use Homebrew to install super:

brew install --cask brimdata/tap/super

Once installed, run a quick test.

Building From Source

Tip

If you don’t have Go installed, download and install it from the Go install page. Go 1.24 or later is required.

With Go installed, you can easily build super from source:

go install github.com/brimdata/super/cmd/super@main

This installs the super binary in your $GOPATH/bin.

Try It

Once installed, run a quick test.

Hello World

Hello World

To test out the installed super binary, try running Hello World!

First, here is a Unix-y version. Copy this to your shell and run it:

echo '"hello, world"' | super -

You should get:

"hello, world"

In this simple case, there is no query argument specified for super (i.e., no -c argument), which causes super to presume an implied from operator. This from operator scans each of the command-line arguments interpreted as file paths or URLs (or - for standard input).

In this case, the input is read from the implied operator, no further query is applied, and the results are emitted to standard output. This results is the string value "hello, world", serialized in the default SUP format, which is simply the string literal itself.

A SQL version of Hello World is:

super -c "SELECT 'hello, world' as Message"

which outputs

{Message:"hello, world"}

This is single row in a table with one column called Message of type string.

SuperDB Database

The top-level super command runs without any underlying persistent database, but you can also run Hello World with a database.

To create a database and populate it with data, run the following commands:

export SUPER_DB=./scratch
super db init
super db create Demo
echo '{Message:"hello, world"}' | super db load -use Demo -

Now you have a database with a data pool called “Demo” and some data in it. Query this data as follow:

super db -c "from Demo"

and you should see

{Message:"hello, world"}

SuperDB Service

Now that you have a database in the ./scratch directory, you could also run Hello World as a client talking to a SuperDB server instance. Continuing the example above (with the SUPER_DB environment pointing to ./scratch), run a service as follows:

super db serve

This command will block and output logging information to standard output.

In another window (without SUPER_DB defined), run super as a client talking to the service input as follows:

super db -c "from Demo"

Playground

Playground

If you have super installed, a common pattern for experimentation is to “echo” some input to the super -c command, e.g.,

echo <values> | super -c <query> -

But you can also experiment with SuperDB using the browser-embedded playground. The super binary has been compiled into Web assembly and executes a super -c <query> - command like this:

# spq
SELECT upper(message) AS out
# input
{id:0,message:"Hello"}
{id:1,message:"Goodbye"}
# expected output
{out:"HELLO"}
{out:"GOODBYE"}

The QUERY and INPUT panes are editable. So go ahead and experiment. Try changing upper to lower in the query text and you should get this alternative output in the RESULT panel above:

{out:"hello"}
{out:"goodbye"}

The input in the playground examples are generally formatted as SUP but the super playground command autodetects the format, so feel free to experiment with other text formats like CSV or JSON. For example, if you change the input above to

id,message
0,"Hello"
1,"Goodbye"

super will detect this as CSV and you will get the same result.

Examples

To explore a broad range of SuperSQL functionality, try browsing the documentation for pipe operators or functions. Each operator and function has a section of examples with playgrounds where you can edit the example queries and inputs to explore how SuperSQL works. The tutorials section also has many playground examples.

Here are a few examples to get going.

Hello, world

super -s -c "SELECT 'hello, world' as s"

produces this SUP output

{s:"hello, world"}

Some values of available data types

# spq
SELECT in as out
# input
{in:1}
{in:1.5}
{in:[1,"foo"]}
{in:|["apple","banana"]|}
# expected output
{out:1}
{out:1.5}
{out:[1,"foo"]}
{out:|["apple","banana"]|}

The types of various data

# spq
SELECT typeof(in) as typ
# input
{in:1}
{in:1.5}
{in:[1,"foo"]}
{in:|["apple","banana"]|}
# expected output
{typ:<int64>}
{typ:<float64>}
{typ:<[int64|string]>}
{typ:<|[string]|>}

A simple aggregation

# spq
sum(val) by key | sort key
# input
{key:"foo",val:1}
{key:"bar",val:2}
{key:"foo",val:3}
# expected output
{key:"bar",sum:2}
{key:"foo",sum:4}

Read CSV input and cast a to an integer from default float

# spq
a:=a::int64
# input
a,b
1,foo
2,bar
# expected output
{a:1,b:"foo"}
{a:2,b:"bar"}

Read JSON input and cast to an integer from default float

# spq
a:=a::int64
# input
{"a":1,"b":"foo"}
{"a":2,"b":"bar"}
# expected output
{a:1,b:"foo"}
{a:2,b:"bar"}

Make a schema-rigid Parquet file using fuse, then output the Parquet file as SUP

echo '{a:1}{a:2}{b:3}' | super -f parquet -o tmp.parquet -c fuse -
super -s tmp.parquet

produces

{a:1,b:null::int64}
{a:2,b:null::int64}
{a:null::int64,b:3}

Command

  super — invoke or manage SuperDB

Synopsis

super [ -c query ] [ options ] [ file ... ]
super [ options ] <sub-command> ...

Sub-commands

• compile
• db
• dev

Options

TODO: link these short-hand flag descriptions to longer form descriptions

• Output Options
• -aggmem maximum memory used per aggregate function value in MiB, MB, etc
• -c SuperSQL query to execute
• -csv.delim CSV field delimiter
• -e stop upon input errors
• -fusemem maximum memory used by fuse in MiB, MB, etc
• -h display help
• -help display help
• -hidden show hidden options
• -i format of input data
• -I source file containing query text
• -q don’t display warnings
• -sortmem maximum memory used by sort in MiB, MB, etc
• -stats display search stats on stderr
• -version print version and exit

Description

super is the command-line tool for interacting with and managing SuperDB and is organized as a hierarchy of sub-commands similar to docker or kubectl.

For built-in command help and a listing of all available options, simply run super without any arguments.

When invoked at the top level without a sub-command, super executes the SuperDB query engine detached from the database storage layer where the data inputs may be files, HTTP APIs, S3 cloud objects, or standard input.

Optional SuperSQL query text may be provided with the -c argument. If no query is provided, the inputs are scanned and output is produced in accordance with -f to specify a serialization format and -o to specified an optional output (file or directory).

The query text may originate in files using one or more -I arguments. In this case, these source files are concatenated together in order and prepended to any -c query text. -I may be used without -c.

When invoked using the db sub-command, super interacts with an underlying SuperDB database.

The dev sub-command provides dev tooling for the advanced users or developers of SuperDB while the compile command allows detailed interactions with various stages of the query compiler.

Supported Formats

Note

Best performance is achieved when operating on data in binary columnar formats such as CSUP, Parquet, or Arrow.

Input

When run detached from a database, super executes a query over inputs external to the database including

• file system paths,
• standard input, or
• HTTP, HTTPS, or S3 URLs.

These inputs may be specified with the operator within the query text or via the file arguments (including stdin) to the command.

Command-line paths are treated as if a from operator precedes the provided query, e.g.,

super -c "FROM example.json | SELECT a,b,c"

is equivalent to

super -c "SELECT a,b,c" example.json

and both are equivalent to the classic SQL

super -c "SELECT a,b,c FROM example.json"

When multiple input files are specified, they are processed in the order given as if the data were provided by a single, concatenated FROM clause.

If no input is specified, the query is fed a single null value analogous to SQL’s default input of a single empty row of an unnamed table. This provides a convenient means to run standalone examples or compute results like a calculator, e.g.,

super -s -c '1+1'

is shorthand for values 1+1 and emits

2

Format Detection

In general, super just works when it comes to automatically inferring the data formats of its inputs.

For files with a well known extension (like .json, .parquet, .sup etc.), the format is implied by the extension.

For standard input or files without a recognizable extension, super attempts to detect the format by reading and parsing some of the data.

To override these format inference heuristics, -i may be used to specify the input formats of command-line files or the (format) option of a data source specified in a from operator.

When -i is used, all of the input files must have the same format. Without -i, each file format is determined independently so you can mix and match input formats.

For example, suppose this content is in a file sample.csv:

a,b
1,foo
2,bar

and this content is in sample.json

{"a":3,"b":"baz"}

then the command

super -s sample.csv sample.json

would produce this output in the default SUP format

{a:1.,b:"foo"}
{a:2.,b:"bar"}
{a:3,b:"baz"}

Note that the line format cannot be automatically detected and requires -i or (format line) for reading.

TODO: Parquet and CSUP require a seekable input and cannot be operated upon when read on standard input. It seems like this should change given the pipe-able nature of super and the desire to make CSUP be the default output to a non-terminal output.

Output

TODO: make CSUP not BSUP the default output format when not a terminal.

Output is written to standard output by default or, if -o is specified, to the indicated file or directory.

When writing to stdout and stdout is a terminal, the default output format is SUP. Otherwise, the default format is CSUP. These defaults may be overridden with -f, -s, or -S.

Since SUP is a common format choice for interactive use, the -s flag is shorthand for -f sup. Also, -S is a shortcut for -f sup with -pretty 2 as described below.

And since plain JSON is another common format choice, the -j flag is a shortcut for -f json and -J is a shortcut for pretty-printing JSON.

Note

Having the default output format dependent on the terminal status causes an occasional surprise (e.g., forgetting -f or -s in a scripted test that works fine on the command line but fails in CI), this avoids problematic performance where a data pipeline deployed to product accidentally uses SUP instead of CSUP. Since super gracefully handles any input, this would be hard to detect. Alternatively, making CSUP the default would cause much annoyance when binary data is written to the terminal.

If no query is specified with -c, the inputs are scanned without modification and output in the specified format providing a convenient means to convert files from one format to another, e.g.,

super -f arrows -o  out.arrows file1.json file2.parquet file3.csv

Pretty Printing

SUP and plain JSON text may be “pretty printed” with the -pretty option, which takes the number of spaces to use for indentation. As this is a common option, the -S option is a shortcut for -f sup -pretty 2 and -J is a shortcut for -f json -pretty 2.

For example,

echo '{a:{b:1,c:[1,2]},d:"foo"}' | super -S -

produces

{
  a: {
    b: 1,
    c: [
      1,
      2
    ]
  },
  d: "foo"
}

and

echo '{a:{b:1,c:[1,2]},d:"foo"}' | super -f sup -pretty 4 -

produces

{
    a: {
        b: 1,
        c: [
            1,
            2
        ]
    },
    d: "foo"
}

When pretty printing, colorization is enabled by default when writing to a terminal, and can be disabled with -color false.

Pipeline-friendly Formats

Though it’s a compressed format, CSUP and BSUP data is self-describing and stream-oriented and thus is pipeline friendly.

Since data is self-describing you can simply take super-structured output of one command and pipe it to the input of another. It doesn’t matter if the value sequence is scalars, complex types, or records. There is no need to declare or register schemas or “protos” with the downstream entities.

In particular, super-structured data can simply be concatenated together, e.g.,

super -f bsup -c 'values 1, [1,2,3]' > a.bsup
super -f bsup -c "values {s:'hello'}, {s:'world'}" > b.bsup
cat a.bsup b.bsup | super -s -

produces

1
[1,2,3]
{s:"hello"}
{s:"world"}

Schema-rigid Outputs

Certain data formats like Arrow and Parquet are schema rigid in the sense that they require a schema to be defined before values can be written into the file and all the values in the file must conform to this schema.

SuperDB, however, has a fine-grained type system instead of schemas such that a sequence of data values is completely self-describing and may be heterogeneous in nature. This creates a challenge converting the type-flexible super-structured data formats to a schema-rigid format like Arrow and Parquet.

For example, this seemingly simple conversion:

echo '{x:1}{s:"hello"}' | super -o out.parquet -f parquet -

causes this error

parquetio: encountered multiple types (consider 'fuse'): {x:int64} and {s:string}

To write heterogeneous data to a schema-based file format, you must convert the data to a monolithic type. To handle this, you can either fuse the data into a single fused type or you can specify the -split flag to indicate a destination directory that receives a separate output file for each output type.

Fused Data

The fuse operator uses type fusion to merge different record types into a blended type, e.g.,

echo '{x:1}{s:"hello"}' | super -o out.parquet -f parquet -c fuse -
super -s out.parquet

which produces

{x:1,s:null::string}
{x:null::int64,s:"hello"}

The downside of this approach is that the data muts be changed (by inserting nulls) to conform to a single type.

Also, data fusion can sometimes involve sum types that are not representable in a format like Parquet. While a bit cumbersome, you could write a query that adjusts the output be renaming columns so that heterogenous data column types are avoided. This modified data could then be fused without sum types and output to Parquet.

Splitting Schemas

An alternative approach to the schema-rigid limitation of Arrow and Parquet is to create a separate file for each schema.

super can do this too with its -split option, which specifies a path to a directory for the output files. If the path is ., then files are written to the current directory.

The files are named using the -o option as a prefix and the suffix is -<n>.<ext> where the <ext> is determined from the output format and where <n> is a unique integer for each distinct output file.

For example, the example above would produce two output files, which can then be read separately to reproduce the original data, e.g.,

echo '{x:1}{s:"hello"}' | super -o out -split . -f parquet -
super -s out-*.parquet

produces the original data

{x:1}
{s:"hello"}

While the -split option is most useful for schema-rigid formats, it can be used with any output format.

SuperDB Database Metadata Output

TODO: We should get rid of this. Or document it as an internal format. It’s not a format that people should rely upon.

The db format is used to pretty-print lake metadata, such as in super db sub-command outputs. Because it’s super db’s default output format, it’s rare to request it explicitly via -f. However, since it’s possible for super db to generate output in any supported format, the db format is useful to reverse this.

For example, imagine you’d executed a meta-query via super db query -S "from :pools" and saved the output in this file pools.sup.

{
    ts: 2024-07-19T19:28:22.893089Z,
    name: "MyPool",
    id: 0x132870564f00de22d252b3438c656691c87842c2::=ksuid.KSUID,
    layout: {
        order: "desc"::=order.Which,
        keys: [
            [
                "ts"
            ]::=field.Path
        ]::=field.List
    }::=order.SortKey,
    seek_stride: 65536,
    threshold: 524288000
}::=pools.Config

Using super -f db, this can be rendered in the same pretty-printed form as it would have originally appeared in the output of super db ls, e.g.,

super -f db pools.sup

produces

MyPool 2jTi7n3sfiU7qTgPTAE1nwTUJ0M key ts order desc

Line Format

The line format is convenient for interacting with other Unix-style tooling that produces text input and output a line at a time.

When -i line is specified as the input format, data is read a line as a string type.

When -f line is specified as the output format, each value is formatted a line at a time. String values are printed as is with otherwise escaped values formatted as their native character in the output, e.g.,

Non-string values are formatted as SUP.

For example:

echo '"hi" "hello\nworld" { time_elapsed: 86400s }' | super -f line -

produces

hi
hello
world
{time_elapsed:1d}

Because embedded newlines create multi-lined output with -i line, this mode can alter the sequence of values, e.g.,

super -c "values 'foo\nbar' | count()"

results in 1 but

super -f line -c "values 'foo\nbar'" | super -i line -c "count()" -

results in 2.

Debugging

TODO: this belongs in the super-sql section. We can link to it.

If you are ever stumped about how the super compiler is parsing your query, you can always run super -C to compile and display your query in canonical form without running it. This can be especially handy when you are learning the language and its shortcuts.

For example, this query

super -C -c 'has(foo)'

is an implied where operator, which matches values that have a field foo, i.e.,

where has(foo)

while this query

super -C -c 'a:=x+1'

is an implied put operator, which creates a new field a with the value x+1, i.e.,

put a:=x+1

Errors

TODO: this belongs in the super-sql section. We can link to it. TODO: document compile-time errors and reference type checking.

Fatal errors like “file not found” or “file system full” are reported as soon as they happen and cause the super process to exit.

On the other hand, runtime errors resulting from the query itself do not halt execution. Instead, these error conditions produce first-class errors in the data output stream interleaved with any valid results. Such errors are easily queried with the is_error function.

This approach provides a robust technique for debugging complex queries, where errors can be wrapped in one another providing stack-trace-like debugging output alongside the output data. This approach has emerged as a more powerful alternative to the traditional technique of looking through logs for errors or trying to debug a halted query with a vague error message.

For example, this query

echo '1 2 0 3' | super -s -c '10.0/this' -

produces

10.
5.
error("divide by zero")
3.3333333333333335

and

echo '1 2 0 3' | super -c '10.0/this' - | super -s -c 'is_error(this)' -

produces just

error("divide by zero")

compile

Command

  compile — compile a SuperSQL query for inspection and debugging

Synopsis

super compile [ options ] query

Options

• -C display DAG or AST as query text (default “false”)
• -dag display output as DAG (implied by -O or -P) (default “false”)
• -files compile query as if command-line input files are present) (default “false”)
• -I source file containing query text (may be repeated)
• -O display optimized DAG (default “false”)
• -P display parallelized DAG (default “0”)

Additional options of the super top-level command

Description

This command parses a SuperSQL query and emits the resulting abstract syntax tree (AST) or runtime directed acyclic graph (DAG) in the output format desired. Use -dag to specify the DAG form; otherwise, the AST form is assumed.

The -C option causes the output to be shown as query language source instead of the AST. This is particularly helpful to see how SQP queries in their abbreviated form are translated into the exanded, pedantic form of piped SQL. The DAG can also be formatted as query-style text but the resulting text is informational only and does not conform to any query syntax. When -C is specified, the result is sent to stdout and the -f and -o options have no effect.

This command is often used for dev and test but is also useful to advanced users for understanding how SuperSQL syntax is parsed into an AST or compiled into a runtime DAG.

db

Sub-command

  db — invoke SuperDB on a super-structured database

Synopsis

super [ options ] db [ options ] -c <query>
super [ options ] db <sub-command> ...

Sub-commands

• auth
• branch
• compact
• create
• delete
• drop
• init
• load
• log
• ls
• manage
• merge
• query TODO: ref this doc
• rename
• revert
• serve
• use
• vacate
• vacuum
• vector

Options

TODO

Description

super db is a sub-command of super to manage and query SuperDB databases.

You can import data from a variety of formats and it will automatically be committed in super-structured format, providing full fidelity of the original format and the ability to reconstruct the original data without loss of information.

A SuperDB database offers an easy-to-use substrate for data discovery, preparation, and transformation as well as serving as a queryable and searchable store for super-structured data both for online and archive use cases.

While super db is itself a sub-command of super, it invokes a large number of interrelated sub-commands, similar to the docker or kubectl commands.

The following sections describe each of the available commands and highlight some key options. Built-in help shows the commands and their options:

• super db -h with no args displays a list of super db commands.
• super db command -h, where command is a sub-command, displays help for that sub-command.
• super db command sub-command -h displays help for a sub-command of a sub-command and so forth.

By default, commands that display lake metadata (e.g., log or ls) use a text format. However, the -f option can be used to specify any supported output format.

Database Connection

TODO: document database location

Commitish

TODO: document this somewhere maybe not here

Sort Key

auth

Command

  auth — connect to a database and authenticate

Synopsis

super db auth login|logout|method|verify

Options

TODO

Additional options of the db sub-command

Description

TODO: rename this command. it’s really about connecting to a database. authenticating is something you do to connect.

branch

Command

  branch — create a new branch on a pool

Synopsis

super db branch [options] [name]

Options

TODO

Additional options of the db sub-command

Description

The branch command creates a branch with the name name that points to the tip of the working branch or, if the name argument is not provided, lists the existing branches of the selected pool.

For example, this branch command

super db branch -use logs@main staging

creates a new branch called “staging” in pool “logs”, which points to the same commit object as the “main” branch. Once created, commits to the “staging” branch will be added to the commit history without affecting the “main” branch and each branch can be queried independently at any time.

Supposing the main branch of logs was already the working branch, then you could create the new branch called “staging” by simply saying

super db branch staging

Likewise, you can delete a branch with -d:

super db branch -d staging

and list the branches as follows:

super db branch

compact

create

Command

  create — create a new pool in a database

Synopsis

super db create [-orderby key[,key...][:asc|:desc]] <name>

Options

TODO

Additional options of the db sub-command

Description

The create command creates a new data pool with the given name, which may be any valid UTF-8 string.

The -orderby option indicates the sort key that is used to sort the data in the pool, which may be in ascending or descending order.

If a sort key is not specified, then it defaults to the special value this.

TODO: if we have no sort key, then there should be no sort key

A newly created pool is initialized with a branch called main.

Pools can be used without thinking about branches. When referencing a pool without a branch, the tooling presumes the “main” branch as the default, and everything can be done on main without having to think about branching.

delete

Command

  delete — delete data from a pool

Synopsis

super db delete [options] <id> [<id>...]
super db delete [options] -where <filter>

Options

TODO

Additional options of the db sub-command

Description

The delete command removes one or more data objects indicated by their ID from a pool. This command simply removes the data from the branch without actually deleting the underlying data objects thereby allowing time travel to work in the face of deletes. Permanent deletion of underlying data objects is handled by the separate vacuum command.

If the -where flag is specified, delete will remove all values for which the provided filter expression is true. The value provided to -where must be a single filter expression, e.g.:

super db delete -where 'ts > 2022-10-05T17:20:00Z and ts < 2022-10-05T17:21:00Z'

drop

Command

  drop — remove a pool from a database

Synopsis

super db drop [options] <name>|<id>

Options

TODO

Additional options of the db sub-command

Description

The drop command deletes a pool and all of its constituent data. As this is a DANGER ZONE command, you must confirm that you want to delete the pool to proceed. The -f option can be used to force the deletion without confirmation.

init

Command

  init — create and initialize a new database

Synopsis

super db init [path]

Options

TODO

Additional options of the db sub-command

Description

A new database is created and initialized with the init command. The path argument is a storage path and is optional. If not present, the path is determined automatically.

If the database already exists, init reports an error and does nothing.

Otherwise, the init command writes the initial cloud objects to the storage path to create a new, empty database at the specified path.

load

Command

  load — load data into database

Synopsis

super db load [options] input [input ...]

Options

TODO

Additional options of the db sub-command

Description

The load command commits new data to a branch of a pool.

Run super db load -h for a list of command-line options.

Note that there is no need to define a schema or insert data into a “table” as all super-structured data is self describing and can be queried in a schema-agnostic fashion. Data of any shape can be stored in any pool and arbitrary data shapes can coexist side by side.

As with super, the input arguments can be in any supported format and the input format is auto-detected if -i is not provided. Likewise, the inputs may be URLs, in which case, the load command streams the data from a Web server or S3 and into the database.

When data is loaded, it is broken up into objects of a target size determined by the pool’s threshold parameter (which defaults to 500MiB but can be configured when the pool is created). Each object is sorted by the sort key but a sequence of objects is not guaranteed to be globally sorted. When lots of small or unsorted commits occur, data can be fragmented. The performance impact of fragmentation can be eliminated by regularly compacting pools.

For example, this command

super db load sample1.json sample2.bsup sample3.sup

loads files of varying formats in a single commit to the working branch.

An alternative branch may be specified with a branch reference with the -use option, i.e., <pool>@<branch>. Supposing a branch called live existed, data can be committed into this branch as follows:

super db load -use logs@live sample.bsup

Or, as mentioned above, you can set the default branch for the load command via use:

super db use logs@live
super db load sample.bsup

During a load operation, a commit is broken out into units called data objects where a target object size is configured into the pool, typically 100MB-1GB. The records within each object are sorted by the sort key. A data object is presumed by the implementation to fit into the memory of an intake worker node so that such a sort can be trivially accomplished.

Data added to a pool can arrive in any order with respect to its sort key. While each object is sorted before it is written, the collection of objects is generally not sorted.

Each load operation creates a single commit, which includes:

• an author and message string,
• a timestamp computed by the server, and
• an optional metadata field of any type expressed as a Super (SUP) value. This data has the type signature:

{
    author: string,
    date: time,
    message: string,
    meta: <any>
}

where <any> is the type of any optionally attached metadata . For example, this command sets the author and message fields:

super db load -user user@example.com -message "new version of prod dataset" ...

If these fields are not specified, then the system will fill them in with the user obtained from the session and a message that is descriptive of the action.

The date field here is used by the database for time travel through the branch and pool history, allowing you to see the state of branches at any time in their commit history.

Arbitrary metadata expressed as any SUP value may be attached to a commit via the -meta flag. This allows an application or user to transactionally commit metadata alongside committed data for any purpose. This approach allows external applications to implement arbitrary data provenance and audit capabilities by embedding custom metadata in the commit history.

Since commit objects are stored as super-structured data, the metadata can easily be queried by running the log -f bsup to retrieve the log in BSUP format, for example, and using super to pull the metadata out as in:

super db log -f bsup | super -c 'has(meta) | values {id,meta}' -

log

Command

  log — display the commit log

Synopsis

super db log [options] [commitish]

Options

Additional options of the db sub-command

Description

The log command, like git log, displays a history of the commits starting from any commit, expressed as a commitish. If no argument is given, the tip of the working branch is used.

Run super db log -h for a list of command-line options.

To understand the log contents, the load operation is actually decomposed into two steps under the covers: an “add” step stores one or more new immutable data objects in the lake and a “commit” step materializes the objects into a branch with an ACID transaction. This updates the branch pointer to point at a new commit object referencing the data objects where the new commit object’s parent points at the branch’s previous commit object, thus forming a path through the object tree.

The log command prints the commit ID of each commit object in that path from the current pointer back through history to the first commit object.

A commit object includes an optional author and message, along with a required timestamp, that is stored in the commit journal for reference. These values may be specified as options to the load command, and are also available in the database API for automation.

Note

The branchlog meta-query source is not yet implemented.

ls

Command

  ls — list the pools in a database

Synopsis

super db ls [options] [pool]

Options

Additional options of the db sub-command

Description

The ls command lists pools in a database or branches in a pool.

By default, all pools in the database are listed along with each pool’s unique ID and sort key.

If a pool name or pool ID is given, then the pool’s branches are listed along with the ID of their commit object, which points at the tip of each branch.

manage

Command

  manage — run regular maintenance on a database

Synopsis

super db manage [options]

Options

Additional options of the db sub-command

Description

The manage command performs maintenance tasks on a database.

Currently the only supported task is compaction, which reduces fragmentation by reading data objects in a pool and writing their contents back to large, non-overlapping objects.

If the -monitor option is specified and the database is configured via network connection, super db manage will run continuously and perform updates as needed. By default a check is performed once per minute to determine if updates are necessary. The -interval option may be used to specify an alternate check frequency as a duration.

If -monitor is not specified, a single maintenance pass is performed on the database.

By default, maintenance tasks are performed on all pools in the database. The -pool option may be specified one or more times to limit maintenance tasks to a subset of pools listed by name.

The output from manage provides a per-pool summary of the maintenance performed, including a count of objects_compacted.

As an alternative to running manage as a separate command, the -manage option is also available on the serve command to have maintenance tasks run at the specified interval by the service process.

merge

Command

  merge — merged data from one branch to another

Synopsis

super db merge -use logs@updates <branch>

Options

Additional options of the db sub-command

Description

Data is merged from one branch into another with the merge command, e.g.,

super db merge -use logs@updates main

where the updates branch is being merged into the main branch within the logs pool.

A merge operation finds a common ancestor in the commit history then computes the set of changes needed for the target branch to reflect the data additions and deletions in the source branch. While the merge operation is performed, data can still be written concurrently to both branches and queries performed and everything remains transactionally consistent. Newly written data remains in the branch while all of the data present at merge initiation is merged into the parent.

This Git-like behavior for a data lake provides a clean solution to the live ingest problem. For example, data can be continuously ingested into a branch of main called live and orchestration logic can periodically merge updates from branch live to branch main, possibly compacting data after the merge according to configured policies and logic.

query

Command

  query — query a database

Synopsis

super db query [options] <query>

Options

TODO: unify this with super command flags.

-aggmem maximum memory used per aggregate function value in MiB, MB, etc -B allow Super Binary to be sent to a terminal output -bsup.compress compress Super Binary frames -bsup.framethresh minimum Super Binary frame size in uncompressed bytes -color enable/disable color formatting for -S and lake text output -f format for output data [arrows,bsup,csup,csv,json,lake,line,parquet,sup,table,text,tsv,zeek,zjson] -fusemem maximum memory used by fuse in MiB, MB, etc -I source file containing Zed query text -J use formatted JSON output independent of -f option -j use line-oriented JSON output independent of -f option -o write data to output file -persist regular expression to persist type definitions across the stream -pretty tab size to pretty print JSON and Super JSON output -S use formatted Super JSON output independent of -f option -s use line-oriented Super JSON output independent of -f option -sortmem maximum memory used by sort in MiB, MB, etc -split split output into one file per data type in this directory -splitsize if >0 and -split is set, split into files at least this big rather than by data type -stats display search stats on stderr -unbuffered disable output buffering

Additional options of the db sub-command

Description

The query command runs a SuperSQL query with data from a lake as input. A query typically begins with a from operator indicating the pool and branch to use as input.

The pool/branch names are specified with from in the query.

As with super, the default output format is SUP for terminals and BSUP otherwise, though this can be overridden with -f to specify one of the various supported output formats.

If a pool name is provided to from without a branch name, then branch “main” is assumed.

This example reads every record from the full key range of the logs pool and sends the results to stdout.

super db query 'from logs'

We can narrow the span of the query by specifying a filter on the database sort key:

super db query 'from logs | ts >= 2018-03-24T17:36:30.090766Z and ts <= 2018-03-24T17:36:30.090758Z'

Filters on sort keys are efficiently implemented as the data is laid out according to the sort key and seek indexes keyed by the sort key are computed for each data object.

When querying data to the BSUP output format, output from a pool can be easily piped to other commands like super, e.g.,

super db query -f bsup 'from logs' | super -f table -c 'count() by field' -

Of course, it’s even more efficient to run the query inside of the pool traversal like this:

super db query -f table 'from logs | count() by field'

By default, the query command scans pool data in sort-key order though the query optimizer may, in general, reorder the scan to optimize searches, aggregations, and joins. An order hint can be supplied to the query command to indicate to the optimizer the desired processing order, but in general, the sort operator should be used to guarantee any particular sort order.

Arbitrarily complex queries can be executed over the lake in this fashion and the planner can utilize cloud resources to parallelize and scale the query over many parallel workers that simultaneously access the lake data in shared cloud storage (while also accessing locally- or cluster-cached copies of data).

Meta-queries

Commit history, metadata about data objects, database and pool configuration, etc. can all be queried and returned as super-structured data, which in turn, can be fed into analytics. This allows a very powerful approach to introspecting the structure of a lake making it easy to measure, tune, and adjust lake parameters to optimize layout for performance.

These structures are introspected using meta-queries that simply specify a metadata source using an extended syntax in the from operator. There are three types of meta-queries:

• from :<meta> - lake level
• from pool:<meta> - pool level
• from pool[@<branch>]<:meta> - branch level

<meta> is the name of the metadata being queried. The available metadata sources vary based on level.

For example, a list of pools with configuration data can be obtained in the SUP format as follows:

super db query -S "from :pools"

This meta-query produces a list of branches in a pool called logs:

super db query -S "from logs:branches"

You can filter the results just like any query, e.g., to look for particular branch:

super db query -S "from logs:branches | branch.name=='main'"

This meta-query produces a list of the data objects in the live branch of pool logs:

super db query -S "from logs@live:objects"

You can also pretty-print in human-readable form most of the metadata records using the “lake” format, e.g.,

super db query -f lake "from logs@live:objects"

The main branch is queried by default if an explicit branch is not specified, e.g.,

super db query -f lake "from logs:objects"

rename

Command

  rename — rename a database pool

Synopsis

super db rename <existing> <new-name>

Options

Additional options of the db sub-command

Description

The rename command assigns a new name <new-name> to an existing pool <existing>, which may be referenced by its ID or its previous name.

revert

use

Command

  use — set working branch for db commands

Synopsis

super db use [ <commitish> ]

Options

• -use specify commit to use, i.e., pool, pool@branch, or pool@commit

Description

The use command sets the working branch to the indicated commitish. When run with no argument, it displays the working branch and database connection.

For example,

super db use logs

provides a “pool-only” commitish that sets the working branch to logs@main.

If a @branch or commit ID are given without a pool prefix, then the pool of the commitish previously in use is presumed. For example, if you are on logs@main then run this command:

super db use @test

then the working branch is set to logs@test.

To specify a branch in another pool, simply prepend the pool name to the desired branch:

super db use otherpool@otherbranch

This command stores the working branch in $HOME/.super_head.

vacate

vacuum

Command

  vacuum — vacuum deleted storage in database

Synopsis

super db vacuum [ options ]

Options

• -dryrun run vacuum without deleting anything
• -f do not prompt for confirmation
• -use specify commit to use, i.e., pool, pool@branch, or pool@commit

Description

The vacuum command permanently removes underlying data objects that have previously been subject to a delete operation. As this is a DANGER ZONE command, you must confirm that you want to remove the objects to proceed. The -f option can be used to force removal without confirmation. The -dryrun option may also be used to see a summary of how many objects would be removed by a vacuum but without removing them.

vector

serve

Command

  serve — run a SuperDB service endpoint

Synopsis

super db serve [options]

Options

• -auth.audience Auth0 audience for API clients (will be publicly accessible)
• -auth.clientid Auth0 client ID for API clients (will be publicly accessible)
• -auth.domain Auth0 domain (as a URL) for API clients (will be publicly accessible)
• -auth.enabled enable authentication checks
• -auth.jwkspath path to JSON Web Key Set file
• -cors.origin CORS allowed origin (may be repeated)
• -defaultfmt default response format (default “sup”)
• -l [addr]:port to listen on (default “:9867”)
• -log.devmode development mode (if enabled dpanic level logs will cause a panic)
• -log.filemod logger file write mode (values: append, truncate, rotate)
• -log.level logging level
• -log.path path to send logs (values: stderr, stdout, path in file system)
• -manage when positive, run lake maintenance tasks at this interval
• -rootcontentfile file to serve for GET /

Additional options of the db sub-command

Description

TODO: get rid of personality metaphor?

The serve command implements the server personality to service requests from instances of the client personality. It listens for API requests on the interface and port specified by the -l option, executes the requests, and returns results.

The -log.level option controls log verbosity. Available levels, ordered from most to least verbose, are debug, info (the default), warn, error, dpanic, panic, and fatal. If the volume of logging output at the default info level seems too excessive for production use, warn level is recommended.

The -manage option enables the running of the same maintenance tasks normally performed via the separate manage command.

dev

dev

The super dev command contains various subcommands for developers.

Run super dev with no arguments to see what is available.

As the dev tooling becomes stable, these tools will eventually all be documented here.

Options

Output Options

Output Options

• -B allow Super Binary to be sent to a terminal output
• -bsup.compress compress Super Binary frames
• -bsup.framethresh minimum Super Binary frame size in uncompressed bytes (default “524288”)
• -bsup.readmax maximum Super Binary read buffer size in MiB, MB, etc.
• -bsup.readsize target Super Binary read buffer size in MiB, MB, etc.
• -bsup.threads number of Super Binary read threads
• -bsup.validate validate format when reading Super Binary
• -color enable/disable color formatting for -S and db text output
• -csv.delim CSV field delimiter
• -f format for output data
• -J shortcut for -f json -pretty, i.e., multi-line JSON
• -j shortcut for -f json -pretty=0, i.e., line-oriented JSON
• -o write data to output file
• -persist regular expression to persist type definitions across the stream
• -pretty tab size to pretty print JSON and Super JSON output
• -S shortcut for -f sup -pretty, i.e., multi-line SUP
• -s shortcut for -f sup -pretty=0, i.e., line-oriented SUP
• -split split output into one file per data type in this directory
• -splitsize if >0 and -split is set, split into files at least this big rather than by data type
• -unbuffered disable output buffering

SuperSQL

SuperSQL is a Pipe SQL adapted for super-structured data. The language is a superset of SQL query syntax and includes a modern type system with sum types to represent heterogeneous data.

Similar to a Unix pipeline, a SuperSQL query is expressed as a data source followed by a number of operators that manipulate the data:

from source | operator | operator | ...

As with SQL, SuperSQL is declarative and the SuperSQL compiler often optimizes a query into an implementation different from the dataflow implied by the pipeline to achieve the same semantics with better performance.

While SuperSQL at its core is a pipe-oriented language, it is also backward compatible with relational SQL in that any arbitrarily complex SQL query may appear as a single pipe operator anywhere in a SuperSQL pipe query.

In other words, a single pipe operator that happens to be a standalone SQL query is also a SuperSQL pipe query. For example, these are all valid SuperSQL queries:

SELECT 'hello, world'
SELECT * FROM table
SELECT * FROM f1.json JOIN f2.json ON f1.id=f2.id
SELECT watchers FROM https://api.github.com/repos/brimdata/super

Interactive UX

To support an interactive pattern of usage, SuperSQL includes search syntax reminiscent of Web or email keyword search along with operator shortcuts.

With shortcuts, verbose queries can be typed in a shorthand facilitating rapid data exploration. For example, the query

SELECT count(), key
FROM source
GROUP BY key

can be simplified as from source | count() by key.

With search, all of the string fields in a value can easily be searched for patterns, e.g., this query

from source
| ? example.com urgent message_length > 100

searches for the strings “example.com” and “urgent” in all of the string values in the input and also includes a numeric comparison regarding the field message_length.

Pipe Queries

The entities that transform data within a SuperSQL pipeline are called pipe operators and take super-structured input from the upstream operator or data source, operate upon the input, and produce zero or more super-structured values as output.

Unlike relational SQL, SuperSQL pipe queries define their computation in terms of dataflow through the directed graph of operators. But instead of relational tables propagating from one pipe operator to another (e.g., as in ZetaSQL pipe syntax), any sequence of potentially heterogeneously typed data may flow between SuperSQL pipe operators.

When a super-structured sequence conforms to a single, homogeneous record type, then the data is equivalent to a SQL relation. And because any SQL query is also a valid pipe operator, SuperSQL is thus a superset of SQL. In particular, a single operator defined as pure SQL is an acceptable SuperSQL query so all SQL query texts are also SuperSQL queries.

Unlike a Unix pipeline, a SuperSQL query can be forked and joined, e.g.,

from source
| operator
| fork
  ( operator | ... )
  ( operator | ... )
| join on condition
| ...
| switch expr
  case value ( operator | ... )
  case value ( operator | ... )
  default ( operator | ... )
| ...

A query can also include multiple data sources, e.g.,

fork
  ( from source1 | ... )
  ( from source2 | ... )
| ...

Pipe Sources

Like SQL, input data for a query is typically sourced with the from operator.

When from is not present, the file arguments to the super command are used as input to the query as if there is an implied from operator, e.g.,

super -c "op1 | op2 | ..." input.json

is equivalent to

super -c "from input.json | op1 | op2 | ..."

When neither from nor file arguments are specified, a single null value is provided as input to the query.

super -c "pass"

results in

null

This pattern provides a simple means to produce a constant input within a query using the values operator, wherein values takes as input a single null and produces each constant expression in turn, e.g.,

super -c "values 1,2,3"

results in

1
2
3

When running on the local file system, from may refer to a file or an HTTP URL indicating an API endpoint. When connected to SuperDB database, from typically refers to a collection of super-structured data called a pool and is referenced using the pool’s name similar to SQL referencing a relational table by name.

For more detail, see the reference page of the from operator, but as an example, you might use its from to fetch data from an HTTP endpoint and process it with super, in this case, to extract the description and license of a GitHub repository:

super -f line -c "
from https://api.github.com/repos/brimdata/super
| values description,license.name
"

Relational Scoping

In SQL queries, data from tables is generally referenced with expressions that specify a table name and a column name within that table, e.g., referencing a column x in a table T as

SELECT T.x FROM (VALUES (1),(2),(3)) AS T(x)

More commonly, when the column name is unambiguous, the table name can be omitted as in

SELECT x FROM (VALUES (1),(2),(3)) AS T(x)

When SQL queries are nested, joined, or invoked as subqueries, scoping rules define how identifiers and dotted expressions resolve to the different available table names and columns reachable via containing scopes. To support such semantics, SuperSQL implements SQL scoping rules inside of any SQL pipe operator but not between pipe operators.

In other words, table aliases and column references all work within a SQL query written as a single pipe operator but scoping of tables and columns does not reach across pipe operators. Likewise, a pipe query embedded inside of a nested SQL query cannot access tables and columns in the containing SQL scope.

Pipe Scoping

For pipe queries, SuperSQL takes a different approach to scoping called pipe scoping.

Here, a pipe operator takes any sequence of input values and produces any computed sequence of output values and all data references are limited to these inputs and outputs. Since there is just one sequence of values, it may be referenced as special value with a special name, which for SuperSQL is the value this.

This scoping model can be summarized as follows:

• all input is referenced as a single value called this, and
• all output is emitted into a single value called this.

As mentioned above, when processing a set of homogeneously-typed records, the data resembles a relational table where the record type resembles a relational schema and each field in the record models the table’s column. In other words, the record fields of this can be accessed with the dot operator reminiscent of a table.column reference in SQL.

For example, the SQL query from above can thus be written in pipe form using the values operator as:

values {x:1}, {x:2}, {x:3} | select this.x

which results in:

{x:1}
{x:2}
{x:3}

As with SQL table names, where this is implied, it is optional can be omitted, i.e.,

values {x:1}, {x:2}, {x:3} | select x

produces the same result.

Referencing this is often convenient, however, as in this query

values {x:1}, {x:2}, {x:3} | aggregate collect(this)

which collects each input value into an array and emits the array resulting in

[{x:1},{x:2},{x:3}]

Combining Piped Data

If all data for all operators were always presented as a single input sequence called this, then there would be no way to combine data from different entities, which is otherwise a hallmark of SQL and the relational model.

To remedy this, SuperSQL extends pipe scoping to joins and subqueries where multiple entities can be combined into the common value this.

Join Scoping

To combine joined entities into this via pipe scoping, the join operator includes an as clause that names the two sides of the join, e.g.,

... | join ( from ... ) as {left,right} | ...

Here, the joined values are formed into a new two-field record whose first field is left and whose second field is right where the left values come from the parent operator and the right values come from the parenthesized join query argument.

For example, suppose the contents of a file f1.json is

{"x":1}
{"x":2}
{"x":3}

and f2.json is

{"y":4}
{"y":5}

then a join can bring these two entities together into a common record which can then be subsequently operated upon, e.g.,

from f1.json
| cross join (from f2.json) as {f1,f2}

computes a cross-product over all the two sides of the join and produces the following output

{f1:{x:1},f2:{y:4}}
{f1:{x:2},f2:{y:4}}
{f1:{x:3},f2:{y:4}}
{f1:{x:1},f2:{y:5}}
{f1:{x:2},f2:{y:5}}
{f1:{x:3},f2:{y:5}}

A downstream operator can then operate on these records, for example, merging the two sides of the join using spread operators (...), i.e.,

from f1.json
| cross join (from f2.json) as {f1,f2}
| values {...f1,...f2}

produces

{x:1,y:4}
{x:2,y:4}
{x:3,y:4}
{x:1,y:5}
{x:2,y:5}
{x:3,y:5}

In contrast, relational scoping using identifier scoping in a SELECT clause with the table source identified in FROM and JOIN clauses, e.g., this query produces the same result:

SELECT f1.x, f2.y FROM f1.json as f1 CROSS JOIN f2.json as f2

Subquery Scoping

A subquery embedded in an expression can also combine data entities via pipe scoping as in

from f1.json | values {outer:this,inner:[from f2.json | ...]}

Here data from the outer query can be mixed in with data from the inner array subquery embedded in the expression inside of the values operator.

The array subquery produces an array value so it is often desirable to unnest this array with respect to the outer values as in

from f1.json | unnest {outer:this,inner:[from f2.json | ...]} into ( <scope> )

where <scope> can be an arbitrary pipe query that processes each collection of unnested values separately as a unit for each outer value. The into ( <scope> ) body is an optional component of unnest, and if absent, the unnested collection boundaries are ignored and all of the unnested data is output.

With the unnest operator, we can now consider how a correlated subquery from SQL can be implemented purely as a pipe query with pipe scoping. For example,

SELECT (SELECT sum(f1.x+f2.y) FROM f1.json) AS s FROM f2.json

results in

{s:18}
{s:21}

To implement this with pipe scoping, the correlated subquery is carried out by unnesting the data from the subquery with the values coming from the outer scope as in

from f2.json
| unnest {f2:this,f1:[from f1.json]} into ( s:=sum(f1.x+f2.y) )

giving the same result

{s:18}
{s:21}

Strong Typing

Data in SuperSQL is always strongly typed.

Like relational SQL, SuperSQL data sequences can conform to a static schema that is type-checked at compile time. And like document databases and SQL++, data sequences may also be dynamically typed, but unlike these systems, SuperSQL data is always strongly typed.

To perform type checking of dynamic data, SuperSQL utilizes a novel approach based on fused types. Here, the compiler interrogates the data sources for their fused type and uses these types (instead of relational schemas) to perform type checking. This is called fused type checking.

Because a relational schema is a special case of a fused type, fused type checking works for both traditional SQL as well as for super-structured pipe queries. These fused types are maintained in the super-structured database and the binary forms of super-structured file formats provide efficient ways to retrieve their fused type.

For traditional formats like JSON or CSV, the file is read by the compiler and the fused type computed on the fly. When such files are sufficiently large creating too much overhead for the compilation stage, this step may be skipped using a configurable limit and the compilation completed with more limited type checking, instead creating runtime errors when type errors are encountered.

In other words, dynamic data is statically type checked when possible. This works by computing fused types of each operator’s output and propagating these types in a dataflow analysis. When types are unknown, the analysis flexibly models them as having any possible type.

For example, this query produces the expected output

$ super -c "select b from (values (1,2),(3,4)) as T(b,c)"
{b:1}
{b:2}

But this query produced a compile-time error:

$ super -c "select a from (values (1,2),(3,4)) as T(b,c)"
column "a": does not exist at line 1, column 8:
select a from (values (1,2),(3,4)) as T(b,c)
       ~

Now supposing this data is in the file input.json:

{"b":1,"c":2}
{"b":3,"c":4}

If we run this the query from above without type checking data from the source (enabled with the -dynamic flag), then the query runs even though there are type errors. In this case, “missing” values are produced:

$ super -dynamic -c "select a from input.json"
{a:error("missing")}
{a:error("missing")}

Even though the reference to column “a” is dynamically evaluated, all the data is still strongly typed, i.e.,

$ super -c "from input.json | values typeof(this)"
<{b:int64,c:int64}>
<{b:int64,c:int64}>

Data Order

Data sequences from sources may have a natural order. For example, the values in a file being read are presumed to have the order they appear in the file. Likewise, data stored in a database organized by a sort constraint is presumed to have the sorted order.

For order-preserving pipe operators, this order is preserved. For order-creating operators like sort an output order is created independent of the input order. For other operators, the output order is undefined.

Each operator defines whether or not it is order is preserved, created, or discarded.

For example, the where drops values that do not meet the operator’s condition but otherwise preserves data order, whereas the sort creates an output order defined by the sort expressions. The aggregate creates an undefined order at output.

When a pipe query branches as in join, fork, or switch, the order at the merged branches is undefined.

Queries

Queries

The syntactical structure of a query consists of

• an optional concatenation of declarations, followed by
• a sequence of pipe operators separated by a pipe symbol (| or |>).

Any valid SQL query may appear as a pipe operator and thus be embedded in a pipe query. A SQL query expressed as a pipe operator is called a SQL operator.

Operator sequences may be parenthesized and nested to form lexical scopes.

Operators utilize expressions in composable variations to perform their computations and all expressions share a common expression syntax. While operators consume a sequence of values, the expressions embedded within an operator are typically evaluated once for each value processed by the operator.

Scope

A scope is formed by enclosing a set of declarations along with an operator sequence in the parentheses having the structure:

(
    <declarations>
    <operators>
)

Scope blocks may appear

• anywhere a pipe operator may appear,
• as a subquery in an expression, or
• as the body of declared operator.

The parenthesized block forms a lexical scope and the bindings created by declarations within the scope are reachable only within that scope inclusive of other scopes defined within the scope.

A declaration cannot redefine an identifier that was previously defined in the same scope but can override identifiers defined in ancestor scopes.

The topmost scope is the global scope where all declared identifiers are available everywhere and does not include parentheses.

Note that this lexical scope refers only to the declared identifiers. Scoping of references to data input is defined by pipe scoping and relational scoping.

For example, this example of a constant declaration

const PI=3.14
values PI

emits the value 3.14 whereas

( 
  const PI=3.14
  values PI
)
| values this+PI

emits error("missing") because the second reference to PI is not in the scope of the declared constant and thus the identifier is interpreted as a field reference this.pi via pipe scoping.

Identifiers

Identifiers are names that arise in many syntactical structures and may be any sequence of UTF-8 characters. When not quoted, an identifier may be comprised of Unicode letters, $, _, and digits [0-9], but may not start with a digit.

To express an identifier that does not meet the requirements of an unquoted identifier, arbitrary text may be quoted inside of backtick (`) quotes. Escape sequences in backtick-quoted identifiers are interpreted as in string literals. In particular, a backtick (`) character may be included in a backtick string with Unicode escape \u0060.

In SQL expressions, identifiers may also be enclosed in double-quoted strings.

The special value this is also available in SQL but has peculiar semantics due to SQL scoping rules. To reference a column called this in a SQL expression, simply use double quotes, i.e., "this".

An unquoted identifier cannot be true, false, null, NaN, or Inf.

Patterns

For ease of use, several operators utilize a syntax for string entities outside of expression syntax where quotation marks for such entities may be conveniently elided.

For example, when sourcing data from a file on the file system, the file path can be expressed as a text entity and need not be quoted:

from file.json | ...

Likewise, in the search operator, the syntax for a regular expression search can be specified as

search /\w+(foo|bar)/

whereas an explicit function call like regexp must be invoked to utilize regular expressions in expressions as in

where len(regexp(r'\w+(foo|bar)', this)) > 0

Regular Expression

Regular expressions follow the syntax and semantics of the RE2 regular expression library, which is documented in the RE2 Wiki.

When used in an expression, e.g., as a parameter to a function, the RE2 text is simply passed as a string, e.g.,

regexp('foo|bar', this)

To avoid having to add escaping that would otherwise be necessary to represent a regular expression as a raw string, with prefix with r, e.g.,

regexp(r'\w+(foo|bar)', this)

But when used outside of expressions where an explicit indication of a regular expression is required (e.g., in a search or from operator), the RE2 is instead prefixed and suffixed with a /, e.g.,

/foo|bar/

matches the string "foo" or "bar".

Glob

Globs provide a convenient short-hand for regular expressions and follow the familiar pattern of “file globbing” supported by Unix shells. Globs are a simple, special case that utilize only the * wildcard.

Like regular expressions, globs may be used in a search operator or a from operator.

Valid glob characters include letters, digits (excepting the leading character), any valid string escape sequence (along with escapes for *, =, +, -), and the unescaped characters:

_ . : / % # @ ~ *

A glob cannot begin with a digit.

Text Entity

A text entity represents a string where quotes can be omitted for certain common use cases regarding URLs and file paths.

Text entities are syntactically valid as targets of a from operator and as named arguments to from and the load operator.

Specifically, a text entity is one of:

• a string literal (double quoted, single quoted, or raw string),
• a path consisting of a sequence of characters consisting of letters, digits, _, $, ., and /, or
• a simple URL consisting of a sequence of characters beginning with http:// or https://, followed by dotted strings of letters, digits, -, and _, and in turn optionally followed by / and a sequence of characters consisting of letters, digits, _, $, ., and /.

If a URL does not meet the constraints of the simple URL rule, e.g., containing a : or &, then it must be quoted.

Comments

Single-line comments are SQL style begin with two dashes -- and end at the subsequent newline.

Multi-line comments are C style and begin with /* and end with */.

# spq
values 1, 2 -- , 3
/*
| aggregate sum(this)
*/
| aggregate sum(this / 2.0)
# input

# expected output
1.5

Declarations

Declarations

Declarations bind a name in the form of an identifier to various entities and may appear at the beginning of any scope including the main scope.

Declarations may be created for

• constants,
• types,
• queries,
• functions,
• operators, or
• pragmas.

All of the names defined in a given scope are available to other declarations defined in the same scope (as well as containing scopes) independent of the order of declaration, i.e., a declaration may forward-reference another declaration that is defined in the same scope.

A declaration may override another declaration of the same name in a parent scope, but declarations in the same scope with the same name conflict and result in an error.

Constants

Constants

Constants are declared with the syntax

const <id> = <expr>

where <id> is an identifier and <expr> is a constant expression that must evaluate at compile time without referencing any runtime state such as this or a field of this.

Constant declarations must appear in the declaration section of a scope.

A constant can be any expression, inclusive of subqueries and function calls, as long as the expression evaluates to a compile-time constant.

Examples

A simple declaration for the identifier PI

# spq
const PI=3.14159
values 2*PI*r
# input
{r:5}
{r:10}
# expected output
31.4159
62.8318

A constant as a subquery that is independent of external input

# spq
const ABC = [
  values 'a', 'b', 'c'
  | upper(this)
]
values ABC
# input

# expected output
["A","B","C"]

Types

Types

Named types are declared with the syntax

type <id> = <type>

where <id> is an identifier and <type> is a type. This creates a new type with the given name in the type system.

Type declarations must appear in the declaration section of a scope.

Any named type that appears in the body of a type declaration must be previously declared in the same scope or in an ancestor scope, i.e., types cannot contained forward references to other named types. In particular, named types cannot be recursive.

Note

A future version of SuperSQL may include recursive types. This is a research topic for the SuperDB project.

Input data may create named types that conflict with type declarations. In this case, a reference to a declared type in the query text uses the type definition of the nearest containing scope that binds the type name independent of types in the input.

When a named type is referenced as a string argument to cast, then any type definition with that name is ignored and the named type is bound to the type of the argument of cast. This does not affect the binding of the type used in other expressions in the query text.

Types can also be bound to identifiers without creating a named type using a constant declaration binding the name to a type value.

Examples

Cast integers to a network port type

# spq
type port=uint16
values this::port
# input
80
# expected output
80::(port=uint16)

Cast integers to a network port type calling cast with a type value

# spq
type port=uint16
values cast(this, <port>)
# input
80
# expected output
80::(port=uint16)

Override binding to type name with this

# spq
type foo=string
values cast(x, foo), cast(x, this.foo)
# input
{x:1,foo:<float64>}
{x:2,foo:<bool>}
# expected output
"1"::=foo
1.
"2"::=foo
true

A type name argument to cast in the form of a string is independent type declarations

# spq
type foo=string
values {str:cast(this, 'foo'), named:cast(this, foo)}
# input
1
2
# expected output
{str:1::=foo,named:"1"::=foo}
{str:2::=foo,named:"2"::=foo}

Bind a name to a type without creating a named type

# spq
const foo=<string>
values this::foo
# input
1
2
# expected output
"1"
"2"

Queries

Queries

A query may be bound to an identifier as a named query with the syntax

let <name> = ( <query> )

where <name> is an identifier and <query> is any standalone query that sources its own input.

Named queries are similar to common-table expressions (CTE) and may be likewise invoked in a from operator by referencing the query’s name, as in

from <name>

When invoked, a named query behaves like any query evaluated in the main scope and receives a single null value as its input. Thus, such queries typically begin with a from or values operator to provide explicit input.

Named queries can also be referenced within an expression, in which case they are automatically invoked as an expression subquery. As with any expression subquery, multiple values result in an error, so when this is expected, the query reference may be enclosed in brackets to form an array subquery.

To create a query that can be used as an operator and thus can operate on its input, declare an operator.

A common use case for a named query is to compute a complex query that returns a scalar, then embedding that scalar result in an expression. Even though the named query appears syntactically as a sub-query in this case, the result is efficient because the compiler will materialize the result and reuse it on each invocation.

Examples

Simplest named query

# spq
let hello = (values 'hello, world')
from hello
# input

# expected output
"hello, world"

Use an array subquery if multiple values expected

# spq
let q = (values 1,2,3)
values [q]
# input

# expected output
[1,2,3]

Assemble multiple query results into a record

# spq
let q1 = (values 1,2,3)
let q2 = (values 3,4)
let q3 = (values 5)
values {a:[q1],b:[q2],c:q3}
# input

# expected output
{a:[1,2,3],b:[3,4],c:5}

Functions

Functions

New functions are declared with the syntax

fn <id> ( [<param> [, <param> ...]] ) : <expr>

where

• <id> is an identifier representing the name of the function,
• each <param> is an identifier representing a positional argument to the function, and
• <expr> is any expression that implements the function.

Function declarations must appear in the declaration section of a scope.

The function body <expr> may refer to the passed-in arguments by name.

Specifically, the references to the named parameters are field references of the special value this, as in any expression. In particular, the value of this referenced in a function body is formed as record from the actual values passed to the function where the field names correspond to the parameters of the function.

For example, the function add as defined by

fn add(a,b): a+b

when invoked as

values {x:1} | values add(x,1)

is passed the record the {a:x,b:1}, which after resolving x to 1, is {a:1,b:1} and thus evaluates the expression

this.a + this.b

which results in 2.

Any function-as-value arguments passed to a function do not appear in the this record formed from the parameters. Instead, function values are expanded at their call sites in a macro-like fashion.

Functions may be recursive. If the maximum call stack depth is exceeded, the function returns an error value indicating so. Recursive functions that run for an extended period of time without exceeding the stack depth will simply be allowed to run indefinitely and stall the query result.

Subquery Functions

Since the body of a function is any expression and an expression may be a subquery, function bodies can be defined as subqueries. This leads to the commonly used pattern of a subquery function:

fn <id> ( [<param> [, <param> ...]] ) : (
    <query>
)

where <query> is any query and is simply wrapped in parentheses to form the subquery.

As with any subquery, when multiple results are expected, an array subquery may be used by wrapping <query> in square brackets instead of parentheses:

fn <id> ( [<param> [, <param> ...]] ) : [
    <query>
]

Note when using a subquery expression in this fashion, the function’s parameters do not appear in the scope of the expressions embedded in the query. For example, this function results in a type error:

fn apply(a,val): (
  unnest a
  | collect(this+val))
)
values apply([1,2,3], 1)

because the field reference to val within the subquery does not exist. Instead the parameter val can be carried into the subquery using the alternative form of unnest:

fn apply(a,val): (
  unnest {outer:val,item:a}
  | collect(outer+item)
)
values apply([1,2,3], 1)

See the example below.

Examples

A simple function that adds two numbers

# spq
fn add(a,b): a+b
values add(x,y)
# input
{x:1,y:2}
{x:2,y:2}
{x:3,y:3}
# expected output
3
4
6

A simple recursive function

# spq
fn fact(n): n<=1 ? 1 : n*fact(n-1)
values fact(5)
# input

# expected output
120

A subquery function that computes some stats over numeric arrays

# spq
fn stats(numbers): (
    unnest numbers
    | sort this
    | avg(this),min(this),max(this),mode:=collect(this)
    | mode:=mode[len(mode)/2]
) 
values stats(a)
# input
{a:[3,1,2]}
{a:[4]}
# expected output
{avg:2.,min:1,max:3,mode:2}
{avg:4.,min:4,max:4,mode:4}

Function arguments are actually fields in the “this” record

# spq
fn that(a,b,c): this
values that(x,y,3)
# input
{x:1,y:2}
# expected output
{a:1,b:2,c:3}

Functions passed as values do not appear in the “this” record

# spq
fn apply(f,arg):{that:this,result:f(arg)}
fn square(x):x*x
values apply(&square,val)
# input
{val:1}
{val:2}
# expected output
{that:{arg:1},result:1}
{that:{arg:2},result:4}

Function parameters do not reach into subquery scope

# spq
fn apply(a,val): (
  unnest a
  | collect(this+val)
)
values apply([1,2,3], 1)
# input

# expected output
"val" no such field at line 3, column 18:
  | collect(this+val)
                 ~~~

The compound unnest form brings parameters into subquery scope

# spq
fn apply(a,val): (
  unnest {outer:val,item:a}
  | collect(outer+item)
)
values apply([1,2,3], 1)
# input

# expected output
[2,3,4]

Operators

Operators

New operators are declared with the syntax

op <name> [<param> [, <param> ...]] : (
  <query>
)

where

• <name> is an identifier representing the name of the new operator,
• each <param> is an identifier representing a positional parameter to the operator, and
• <query> is any query.

Operator declarations must appear in the declaration section of a scope.

Call

A declared operator is invoked by its name using the call keyword. Operators can be invoked without the call keyword as a shortcut when such use is unambiguous with the built-in operators.

A called instance of a declared operator consumes input, operates on that input, and produces output. The body of the operator declaration with argument expressions substituted into referenced parameters defines how the input is processed.

An operator may also source its own data by beginning the query body with a from operator or SQL statement.

Nested Calls

Operators do not support recursion. They cannot call themselves nor can they form a mutually recursive dependency loop. However, operators can call other operators whose declaration is in scope as long as no dependency loop is formed.

Closure-like Arguments

In contrast to function calls, where the arguments are evaluated at the call site and values are passed to the function, operator arguments are instead passed to the operator body as an expression template and the expression is evaluated in the context of the operator body. That said, any other declared identifiers referenced by these expressions (e.g., constants, functions, named queries, etc.) are bound to those entities using the lexical scope where they are defined rather than the scope of their expanded use in the operator’s definition.

These expression arguments can be viewed as a closure though there is no persistent state stored in the closure. The jq language describes its expression semantics as closures as well, though unlike jq, the operator expressions here are not generators and do not implement backtracking.

Examples

Trivial operator that echoes its input

# spq
op echo: (
  values this
)
echo
# input
{x:1}
# expected output
{x:1}

Simple example that adds a new field to inputs records

# spq
op decorate field, msg: (
  put field:=msg
)
decorate message, "hello"
# input
{greeting: "hi"}
# expected output
{greeting:"hi",message:"hello"}

Error checking works as expected for non-l-values used as l-values

# spq
op decorate field, msg: (
  put field:=msg
)
decorate 1, "hello"
# input
{greeting: "hi"}
# expected output
illegal left-hand side of assignment at line 2, column 7:
  put field:=msg
      ~~~~~~~~~~

Nested calls

# spq
op add4 x: (
  op add2 x: (
    op add1 x: ( x:=x+1 )
    add1 x | add1 x
  )
  add2 x | add2 x
)
add4 a.b
# input
{a:{b:1}}
# expected output
{a:{b:5}}

Pragmas

Pragmas

Pragmas control various language features and appear in a declaration block so their effect is lexically scoped. They have the form

pragma <id> = <expr>

where <id> is an identifier and <expr> is a constant expression that must evaluate at compile time without referencing any runtime state such as this or a field of this.

Pragmas must appear in the declaration section of a scope.

List of Pragmas

Currently, there is just one supported pragma.

• index_base - controls whether index expressions and slice expressions are 0-based or 1-based.
  • 0 for zero-based indexing
  • 1 for one-based indexing

Example

Controlling indexing and slicing

# spq
pragma index_base = 1
values {
  a: this[2:3],
  b: (
    pragma index_base = 0
    values this[0]
  )
}
# input
"bar"
[1,2,3]
# expected output
{a:"a",b:error("missing")}
{a:[2],b:1}

Expressions

Expressions

Expressions are the means the carry out calculations and utilize familiar query-language elements like literal values, function calls, subqueries, and so forth.

Within pipe operators, expressions may reference input values either via the special value this or implied field references to this, while within SQL clauses, input is referenced with table and column references.

For example, values, where, cut, put, sort and so forth all utilize various expressions as part of their semantics.

Likewise, the projected columns of a SELECT from the very same expression syntax used by pipe operators.

While SQL expressions and pipe expressions share an identical syntax, their semantics diverges in some key ways:

• SQL expressions that reference this have semantics that depend on the SQL clause that expression appears in,
• relational tables and/or columns cannot be referenced using aliases in pipe scoping,
• double-quoted string literals may be used in pipe expressions but are interpreted as identifiers in SQL expressions.

Expression Syntax

Expressions are composed from operands and operators over operands.

Operands include

• inputs,
• literals,
• formatted strings
• function calls,
• subqueries, or
• other expressions.

Operators include

• arithmetic to add, subtract, multiply, divide, etc,
• cast to convert values from one type to another,
• comparisons to compare two values resulting in a Boolean,
• concatenation of strings,
• conditionals including C-style ?-: operator and SQL CASE expressions,
• containment to test for the existing value inside an array or set,
• dot to access a field of a record (or a SQL column of a table),
• exists for SQL compatibility to test for non-empty subqueries,
• indexing to select and slice elements from an array, record, map, string, or bytes,
• logic to combine predicates using Boolean logic, and
• slices to extract subsequences from arrays, sets, strings, and bytes.

Identifier Resolution

An identifier that appears as an operand in an expression is resolved to the entity that it represents using lexical scoping.

For identifiers that appear in the context of call syntax, i.e., having the form

<func> ( <args> )

then <func> is one of:

• the name of a built-in function,
• the name of a declared function,
• a lambda expression, or
• a function parameter that resolves to a function reference or lambda expression.

Identifiers that correspond to an in-scope function may also be referenced with the syntax

& <name>

as a function reference and must appear as an argument to an operator or function; otherwise, such expressions are errors.

For identifiers that resolve to in-scope declarations, the resolution is as follows:

• constants resolve to their defined constant values,
• types resolve to their named type,
• queries resolve to an implied subquery invocation, and
• functions and operators produce an error.

For other instances of identifiers, then identifier is presumed to be an input reference and is resolved as such.

Precedence

When multiple operators appear in an unparenthesized sequence, ambiguity may arise by the order of evaluation as expressions are not always evaluated in a strict left-to-right order. Precedence rules determine the operator order when such ambiguity exists where higher precedence operators are evaluated before lower precedence operators.

For example,

1 + 2 * 3

is 7 not 9 because multiplication has higher precedence than addition and the above expression is equivalent to \

1 + ( 2 * 3 )

Operators have the following precedence from highest to lowest:

• [] indexing
• -, + unary sign
• || string concatenation
• *, /, % multiplication, division, modulo
• -, + subtraction, addition
• =, >=, >, <=, <, <>, !=, is comparisons
• not, ! logical NOT, exists existence
• like, in, between comparisons
• and logical AND
• or logical OR
• ?: ternary conditional

Some operators like case expressions do not have any such ambiguity as keywords delineate their sub-expressions and thus do not have any inherent precedence.

Coercion

Note

A forthcoming version of this documentation will describe the coercion rules for automatically casting of values for type compatibility in expressions.

Arithmetic

Arithmetic

Arithmetic operations (*, /, %, +, -) follow customary syntax and semantics and are left-associative with multiplication and division having precedence over addition and subtraction. % is the modulo operator.

Unary Sign

Any number may be signed with a unary operator having the form:

- <expr>

and

+ <expr>

where <expr> is any expression that results in a number type.

Example

# spq
values 2*3+1, 11%5, 1/0, "foo"+"bar", +1, -1
# input

# expected output
7
1
error("divide by zero")
"foobar"
1
-1

Casts

Casts

Casting is the process of converting a value from its current data type to another type using an explicit expression having the form

<expr> :: <type>

where <expr> is any expression and <type> is any type that is compatible with <expr>. When <expr> and <type> are incompatible, structured errors result as described below.

The SQL syntax

CAST(<expr> AS <type>)

is also supported.

To cast to the value form of a type, i.e., a type value, the cast function may be used.

When a cast is successful, the return value of cast always has the target type.

If errors are encountered, then some or all of the resulting value will be embedded with structured errors and the result does not have the target type.

The target type cannot contain an error type. The error function should instead be used to create error values.

Primitive Values

Some primitive values can be cast to other primitive types, but not all possibilities are permitted and instead result in structured errors. Except for union and named types, primitive values cannot be cast to complex types.

The casting rules for primitives are as follows:

• A number may be cast to
  • another number type as long as the numeric value is not outside the scope of the target type, which results in a structured error,
  • type string,
  • type bool where zero is false and non-zero is true,
  • type duration where the number is presumed to be nanoseconds,
  • type time where the number is presumed to be nanoseconds since epoch, or
  • a union or named type.
• A string may be cast to any other primitive type as long as the string corresponds to a valid SuperSQL primitive literal. Time strings in particular may represent typical timestamp formats. When cast to the bytes type, the result is the byte encoding of the UTF-8 string. A string may also be cast to a union or named type. To parse a literal string that is in the SUP or JSON format without having to specify the target type, use the parse_sup function.
• A bool may be cast to
  • a number type where false is zero and true is 1,
  • type string, or
  • a union or named type.
• A time value may be cast to
  • a number type where the numeric value is nanoseconds since epoch
  • type string, or
  • a union or named type.

A null value of type null may be cast to any type.

Note

A future version of this documentation will provide detailed documentation for acceptable date/time strings.

Complex Values

When a complex value has multiple levels of nesting, casting is applied recursively into the value hierarchy. For example, cast is recursively applied to each element in an array of records, then recursively applied to each of those records.

If there is a mismatch between the type of the input value and target type then structured errors appear within the portion of a nested value that is not castable.

The casting rules for complex values are as follows:

• A record may be cast to
  • a record type where any fields not present in the target type are omitted, any fields not present in the input value while present in the target type are set to null, and the value of each input field present in both the input and target are recursively cast to the target’s type of that field,
  • a string type where the string is the input value serialized in the SUP format, or
  • a union or named type.
• An array may be cast to
  • an array type where the elements of the input value are recursively cast to the element type of the target array type,
  • a set type where the elements of the input value are recursively cast to the element type of the target set type and any duplicate values are automatically removed, or
  • a string type where the string is the input value serialized in the SUP format, or
  • a union or named type.
• A set may be cast to
  • a set type where the elements of the input value are recursively cast to the element type of the target set type,
  • an array type where the elements of the input value are recursively cast to the element type of the target array type, or
  • a string type where the string is the input value serialized in the SUP format, or
  • a union or named type.
• A map may be cast to
  • a map type where the keys and values of the input value are recursively cast to the key and value type of the target map type, or
  • a string type where the string is the input value serialized in the SUP format, or
  • a union or named type.
• An enum may be cast to
  • an enum type where the target type includes the symbol of the value being cast, or
  • a string type where the string is the input value serialized in the SUP format, or
  • a union or named type.

Union Types

When casting a value to a union type, the member type of the union is selected to find a best fit of the available types. If no fit exists, a structured error is returned.

If the input type is present in the member types, then the best fit is that type.

Otherwise, the best fit is determined from the input type as follows:

Note

A future version of this documentation will provide detailed documentation for best-fit selection algorithm.

Named Types

When casting to a named type, the cast is carried out using its underlying type then the named type is reattached to the result.

Errors

Casts attempted between a value and a type that are not defined result in a structured error of the form of:

{message:"cannot cast to <target>", on:<val>}

When errors appear within a complex value, the returned value may not be wrapped in a structured error and the problematic portions of the cast can be debugged by inspecting the result for precisely where the errors arose.

For example, this function call

cast({a:"1",b:2}, <{a:int64,b:ip}>)

returns

{a:1,b:error({message:"cannot cast to ip",on:2})}

That is the value for a was successfully cast from string "1“ to integer 1 but the value for b could not be cast to an IP address so a structured error is instead embedded as the value for b.

Examples

Cast various primitives to type ip

# spq
values this::ip
# input
"10.0.0.1"
1
"foo"
# expected output
10.0.0.1
error({message:"cannot cast to ip",on:1})
error({message:"cannot cast to ip",on:"foo"})

Cast array of strings to array of IPs

# spq
values this::[ip]
# input
["10.0.0.1","10.0.0.2"]
# expected output
[10.0.0.1,10.0.0.2]

Cast a record to a different record type

# spq
values this::{b:string}
# input
{a:1,b:2}
{a:3}
{b:4}
# expected output
{b:"2"}
{b:null::string}
{b:"4"}

Multiple syntax options for casting

# spq
values
  80::(port=uint16),
  CAST(80 AS (port=uint16)),
  cast(80::uint16, 'port'),
  cast(cast(80, <uint16>), 'port')
# input

# expected output
80::(port=uint16)
80::(port=uint16)
80::(port=uint16)
80::(port=uint16)

Casting time strings is fairly flexible

# spq
values this::time
# input
"May 8, 2009 5:57:51 PM"
"oct 7, 1970"
# expected output
2009-05-08T17:57:51Z
1970-10-07T00:00:00Z

Cast to a declared type

# spq
type port = uint16
values this::port
# input
80
8080
# expected output
80::(port=uint16)
8080::(port=uint16)

Cast nested records

# spq
values this::{ts:time,r:{x:float64,y:float64}}
# input
{ts:"1/1/2022",r:{x:"1",y:"2"}}
{ts:"1/2/2022",r:{x:3,y:4}}
# expected output
{ts:2022-01-01T00:00:00Z,r:{x:1.,y:2.}}
{ts:2022-01-02T00:00:00Z,r:{x:3.,y:4.}}

Comparisons

Comparisons

Comparison expressions follow customary syntax and semantics and result in a truth value of type bool or an error.

The binary comparison operators have the form

<expr> <op> <expr>

where <op> is one of

• < for less than,
• <= for less than or equal,
• > for greater than
• >= for greater than or equal,
• == or = for equal,
• != or <> for not equal, or
• like or not like for the SQL string pattern matching.

The between comparator has the form

<expr> between <lower> and <upper>

where <expr>, <lower>, and <upper> are any expressions that result in an orderable type and are type compatible. This is shorthand for

<expr> >= <lower> and <expr> <= <upper>

The null comparators have the form

<expr> is null

and is true if <expr> is a null value of any type. Likewise,

<expr> is not null

if true if <expr> is not a null value of any type. As with SQL, any comparison of a null value to any other value is a null value of type bool, i.e., null::bool. This is because comparing an unknown value with any other value has an unknown result.

The like comparator has the form

<expr> like <pattern>

where <expr> is any expression that produces a string type and <pattern> is a constant expression that results in a string type.

Note

Currently, <pattern> must be a constant value and cannot depend on the input. Also, the ilike operator for case-insensitive matching is not yet supported. These capabilities will be included in a future version of SuperSQL.

The like comparator is true if the <pattern> matches <expr> where pattern consists of literal characters, _ for matching any single letter, and % for matching any sequence of characters.

The not like comparator has the form

<expr> not like <pattern>

and is true when the pattern does not match the expression.

String values are compared via byte order in accordance with C/POSIX collation as found in other SQL databases such as Postgres.

Note

SuperSQL does net yet support SQL COLLATE keyword and variations.

When the operands are coercible to like types, the result is the truth value of the comparison. Otherwise, the result is false. To compare values of different types, consider the compare function.

If either operand to a comparison is error("missing"), then the result is error("missing").

Examples

Various scalar comparisons

# spq
values 1 > 2, 1 < 2, "b" > "a", 1 > "a", 1 > x
# input

# expected output
false
true
true
false
error("missing")

Null comparisons

# spq
values {isNull:this is null,isNotNull:this is not null}
# input
1
null
2
error("missing")
# expected output
{isNull:false,isNotNull:true}
{isNull:true,isNotNull:false}
{isNull:false,isNotNull:true}
{isNull:error("missing"),isNotNull:error("missing")}

Comparisons using a like pattern

# spq
values f"{this} like '%abc_e': {this like '%abc_e'}"
# input
"abcde"
"xabcde"
"abcdex"
"abcdd"
null
error("missing")
# expected output
"abcde like '%abc_e': true"
"xabcde like '%abc_e': true"
"abcdex like '%abc_e': false"
"abcdd like '%abc_e': false"
null::string
error("missing")

Concatenation

Concatenation

Strings may be concatenated using the concatenation operator having the form

<expr> || <expr>

where <expr> is any expression that results in a string value.

It is an error to concatenate non-string values. Values may be converted to string using a cast to type string.

Examples

Concatenate two fields

# spq
values a || b
# input
{a:"foo",b:"bar"}
{a:"hello, ",b:"world"}
{a:"foo",b:123}
# expected output
"foobar"
"hello, world"
error("incompatible types")

Cast non-string values to concatenate

# spq
values "foo" || 123::string
# input

# expected output
"foo123"

Conditionals

Conditionals

Conditional expressions compute a result from two or more possibilities determined by Boolean predicates.

Conditionals can be written using SQL-style CASE syntax or C-style ternary expressions.

Case Expressions

SQL-style CASE expressions have two forms.

The first form has the syntax

CASE <expr>
WHEN <expr-1> THEN <result-1>
[ WHEN <expr-2> THEN <result-2> ]
...
[ ELSE <else-result> ]
END

The expression <expr> is evaluated and compared with each subsequent WHEN expression <expr-1>, <expr-2>, etc. until a match is found, in which case, the corresponding expression <result-n> is evaluated for the match, and that value becomes the result of the CASE expression. If there is no match and an ELSE clause is present, the the result is determined by the expression <else-result>. Otherwise, the result is null.

The second form omits <expr> from above and has the syntax

CASE
WHEN <predicate-1> THEN <result-1>
[ WHEN <predicate-2> THEN <result-2> ]
...
[ ELSE <else-result> ]
END

Here, each WHEN expression must be Boolean-valued and <predicate-1>, <predicate-2>, etc. are evaluated in order until a true result is encountered, in which case, the corresponding expression <result-n> is evaluated for the match, and that value becomes the result of the CASE expression. If there is no true result and an ELSE clause is present, the the result is determined by the expression <else-result>. Otherwise, the result is null.

If the predicate expressions are not Boolean valued, then an error results. The error is reported at compile time if possible, but when input is dynamic and the type cannot be statically determined, a structured error is generated at run time as the result of the conditional expression.

Ternary Conditional

The ternary form follows the C language and has syntax

<predicate> ? <true-expr> : <false-expr>

where <predicate> is a Boolean-valued expression and <true-expr> and <false-expr> are any expressions. When <predicate> is true, then <true-expr> is evaluated and becomes the result of the conditional expression; otherwise, <false-expr> becomes the result.

If <predicate> is not a Boolean, then an error results. The error is reported at compile time if possible, but when input is dynamic and the type cannot be statically determined, a structured error is generated at run time as the result of the conditional expression.

Examples

A simple ternary conditional

# spq
values (s=="foo") ? v : -v
# input
{s:"foo",v:1}
{s:"bar",v:2}
# expected output
1
-2

The previous example as a CASE expression

# spq
values CASE WHEN s="foo" THEN v ELSE -v END
# input
{s:"foo",v:1}
{s:"bar",v:2}
# expected output
1
-2

Ternary conditionals can be chained

# spq
values (s=="foo") ? v : (s=="bar") ? -v : v*v
# input
{s:"foo",v:1}
{s:"bar",v:2}
{s:"baz",v:3}
# expected output
1
-2
9

The previous example as a CASE expression

# spq
values
  CASE s
  WHEN "foo" THEN v
  WHEN "bar" THEN -v
  ELSE v*v
  END
# input
{s:"foo",v:1}
{s:"bar",v:2}
{s:"baz",v:3}
# expected output
1
-2
9

Containment

Containment

A containment expression expression tests for the existence of a value in another value and has the form

<item> in <target>

where <item> and <target> are expressions.

The result is Boolean-valued and is true and is true if the <item> expression results in a value that appears somewhere in the <target> expression as an exact match of the item.

In contrast to SQL’s IN operator, the right-hand side can be any value and when the <item> and <target> are equal, the result of in is true, e.g.,

1 in 1

is semantically valid and results in true.

The inverse of in has the syntax

<item> not in <target>

and is true when <item> is not contained in the <target>.

Note

The in operator currently does not support SQL NULL semantics in that 1 not in [2,NULL] is false instead of NULL. This will be fixed in a future version.

When the <target> is a non-array subquery, it is coerced to an array subquery and the in expression is evaluated on the array result of the subquery.

Examples

Test for the value 1 in the input values

# spq
where 1 in this
# input
{a:[1,2]}
{b:{c:3}}
{d:{e:1}}
# expected output
{a:[1,2]}
{d:{e:1}}

Test against a predetermined values with a literal array

# spq
unnest accounts | where id in [1,2]
# input
{accounts:[{id:1},{id:2},{id:3}]}
# expected output
{id:1}
{id:2}

Complex values are recursively searched for containment

# spq
where {s:"foo"} in this
# input
{s:"foo"}
{s:"foo",t:"bar"}
{a:{s:"foo"}}
[1,{s:"foo"},2]
# expected output
{s:"foo"}
{a:{s:"foo"}}
[1,{s:"foo"},2]

Subqueries work the same whether they are standard style or array style

# spq
let vals = (values 1,2,3)
values {a:(this in vals), b:(this in [vals])}
# input
1
2
4
# expected output
{a:true,b:true}
{a:true,b:true}
{a:false,b:false}

Dot

Dot

Records and maps with string keys are dereferenced with the dot operator . as is customary in other languages. The syntax is

<expr> . <id>

where <expr> is an expresson resulting in a dereferenceable value and <id> is an identifier representing the field name of a record or a string key of a map.

Dereferenceable values include records, maps with keys of type string, and type values that are of type record.

The result of a dot expression is

• the value of the indicated field for a record type,
• the value of the indicated entry for a map with string keys, or
• the type value of the indicated field for a record type value.

When a field or key is referenced in a dereferenceable type but that field or key is not present, then error("missing") is the result.

If a non-dereferenceable type is operated upon with the dot operator, then a compile-time error results for statically typed data and a structured error results for dynamic data.

Note that identifiers containing unusual characters can be represented by enclosing the identifier in backtick quotes, e.g.,

x.`a b`

references the field or key whose name is a b.

Alternatively, index syntax may be used to access the field as a string value, e.g.,

x["a b"]

If a field name is not representable as an identifier, then indexing may be used with a quoted string to represent any valid field name. Such field names can be accessed using this with an index-style reference, e.g., this["field with spaces"].

Examples

Derefence a map, a record, and a record type

# spq
values this.x.y
# input
|{"x":{y:1},"y":2}|
{x:{y:1},y:2}
<{x:{y:int64},y:int64}>
# expected output
1
1
<int64>

Use backtick quotes for identifiers with special characters

# spq
values x.`a b`, x["a b"]
# input
{x:{"a b":123}}
# expected output
123
123

Exists

Exists

The exists operator is Boolean-valued function that tests whether a subquery has a non-empty result and has the form

exists ( <query> )

where <query> is any query.

It is a syntactic shortcut for

len([<query>]) != 0

Examples

Simple example showing true for non-empty result

# spq
values exists (values 1,2,3)
# input

# expected output
true

EXISTS is typically used with correlated subqueries but they are not yet supported

# spq
let Orders = (values {product:"widget",customer_id:1})
SELECT 'there are orders in the system' as s
WHERE EXISTS (
    SELECT 1
    FROM Orders
)
# input

# expected output
{s:"there are orders in the system"}

F-Strings

F-Strings

A formatted string (or f-string) is a string literal prefixed with f that includes replacement expressions delimited by curly braces:

f"... { <expr> } ... { <expr> } ..."

The text starting with { and ending at } is substituted with the result of the expression <expr>. As shown, multiple such expressions may be embedded in an f-string. If the expression results in a value that is not a string, then it is implicitly cast to a string.

F-strings may be nested in that the embedded expressions may contain additional f-strings as in

f"an example {upper(f"{foo + bar}")} of nested f-strings"

If any expression results in an error, then the value of the f-string is the first error encountered in left-to-right order.

To represent a literal { character inside an f-string, it must be escaped with a backslash as \{. This escape sequence is valid only in f-strings.

Examples

Some simple arithmetic

# spq
values f"pi is approximately {numerator / denominator}"
# input
{numerator:22.0, denominator:7.0}
# expected output
"pi is approximately 3.142857142857143"

A complex expression with nested f-strings

# spq
values f"oh {this[upper(f"{foo + bar}")]}"
# input
{foo:"hello", bar:"world", HELLOWORLD:"hi!"}
# expected output
"oh hi!"

Curly braces can be escaped

# spq
values f"{this} look like: \{}"
# input
"curly braces"
# expected output
"curly braces look like: {}"

Function Calls

Function Calls

Functions compute a result from zero or more input arguments that are passed by value as positional arguments.

A function call is an expression having the form

<entity> ( [ <arg> [ , <arg> ... ]] )

where the <entity> is either an identifier or a lambda expression. When the <entity> is an identifier, it is one of

• the name of built-in function,
• the name of a declared function that is in scope, or
• a parameter name that resolves to a function reference where the entity called is inside of a declared function.

Each argument <arg> is either

• an expression that is evaluated before the function is called and passed as a value, or
• a function reference.

Functions are not first-class values and cannot be assigned to super-structured values as there are no function values in the super-structured data model. Instead, functions may only be called or passed as a reference to another function.

Lambda Expressions

A lambda expression is an anonymous function having the form

lambda [ <param> [ , <param> ... ]] : <expr>

where <expr> is any expression defining the function and each <param> is an identifier defining the positional parameters of the function.

For example,

lambda x:x+1

is an anonymous function that adds one to its argument.

Like named functions, lambda expressions may only be called or passed to another function as an argument.

For example,

lambda x:x+1 (2)

is the value 3 and

f(lambda x:x+1, 2)

calls the function f with the lambda as its first argument and the value 2 as its second argument.

Function References

The syntax for referencing a function by name is

& <name>

where <name> is an identifier corresponding to either a built-in function or a declared function that is in scope.

Note

Many languages form function references simply by referring to their name without the need for a special symbol like&. However, an ambiguity arises here between a field reference, which is not declared, and a function name.

For example,

&upper

is a reference to the built-in function upper.

Examples

Sample calls to various built-in functions

# spq
values pow(2,3), lower("ABC")+upper("def"), typeof(1)
# input

# expected output
8.
"abcDEF"
<int64>

Calling a lambda function

# spq
values lambda x:x+1 (2)
# input

# expected output
3

Passing a lambda function

# spq
fn square(g,val):g(val)*g(val)
values square(lambda x:x+1, 2)
# input

# expected output
9

Passing function references

# spq
fn inc(x):x+1
fn apply(val,sfunc,nfunc):
  case typeof(val)
  when <string> then sfunc(val)
  when <int64> then nfunc(val)
  else val
  end
values apply(this,&upper,&inc)
# input
1
"foo"
true
# expected output
2
"FOO"
true

Function references may not be assigned to super-structured values

# spq
fn f():null
values {x:&f}
# input

# expected output
parse error at line 2, column 11:
values {x:&f}
      === ^ ===

Index

Index

The index operation is denoted with square brackets and can be applied to any indexable data type and has the form:

<entity> [ <index> ]

where <entity> is an expression resulting in an indexable entity and <index> is an expression resulting in a value that is used to index the indexable entity.

Indexable entities include records, arrays, sets, maps, strings, and bytes.

If <entity> is a record, then the <index> operand must be coercible to a string and the result is the record’s field of that name.

If <entity> is an array, then the <index> operand must be coercible to an integer and the result is the value in the array of that index.

If <entity> is a set, then the <index> operand must be coercible to an integer and the result is the value in the set of that index ordered by total order of values.

If <entity> is a map, then the <index> operand is presumed to be a key and the corresponding value for that key is the result of the operation. If no such key exists in the map, then the result is error("missing").

If <entity> is a string, then the <index> operand must be coercible to an integer and the result is an integer representing the unicode code point at that offset in the string.

If <entity> is type bytes, then the <index> operand must be coercible to an integer and the result is an unsigned 8-bit integer representing the byte value at that offset in the bytes sequence.

Note

Indexing of strings and bytes is not yet implemented.

Index Base

Indexing in SuperSQL are 0-based meaning the first element is at index 0 and the last element is at index n-1 for an entity of size n.

If 1-based indexing is desired, a scoped language pragma may be used to specify either 1-based indexing or mixed indexing. In mixed indexing, 0-based indexing is used for expressions appearing in pipe operators and 1-based indexing is used for expressions appearing in SQL operators.

Note

Mixed indexing is not yet implemented.

Examples

Simple index

# spq
values a[2]
# input
{a:[1,2,3,4]}
{a:|[1,2,3,4]|}
{a:"1234"}
{a:0x01020304}
# expected output
3
3
error("missing")
error("missing")

One-based indexing

# spq
pragma index_base = 1
values a[2]
# input
{a:[1,2,3,4]}
{a:|[1,2,3,4]|}
{a:"1234"}
{a:0x01020304}
# expected output
2
2
error("missing")
error("missing")

Index from end of entity

# spq
values a[-1]
# input
{a:[1,2,3,4]}
{a:|[1,2,3,4]|}
{a:"1234"}
{a:0x01020304}
# expected output
4
4
error("missing")
error("missing")

Inputs

Inputs

Input data is processed by queries through expressions that contain input-data references.

In pipe scoping, input data is always referenced as the special value this.

In relational scoping, input data is referenced by specifying the columns of one or more tables. See the SQL section for details on how columns are bound to identifiers, how table references are resolved, and how this behaves in a SQL expression.

The type of this may be any type. When this is a record, references to fields of the record may be referenced by an identifier that names the field of the implied this value, e.g., x means this.x.

For expressions that appear in a SQL operator, input is presumed to be in the form of records and is referenced using relational scoping. Here, identifiers refer to table aliases and/or column names and are bound to the available inputs based on SQL semantics. When the input schema is known, these references are statically checked and compile-time errors are raised when invalid tables or columns are referenced.

In a SQL operator, if the input is not a record (i.e., not relational), then the input data can still be referred to as the value this and placed into an output relation using SELECT. When referring to non-relational with *, there are no input columns and thus the select value is empty, i.e., the value {}.

When non-record data is referenced in a SQL operator and the input schema is dynamic and unknown, runtime errors like error("missing") will generally arise and be present in the output data.

Examples

A simple reference to this

# spq
values this
# input
1
true
"foo"
# expected output
1
true
"foo"

Referencing a field of this

# spq
values this.x
# input
{x:1,y:4}
{x:2,y:5}
{x:3,y:6}
# expected output
1
2
3

Referencing an implied field of this

# spq
values x
# input
{x:1,y:4}
{x:2,y:5}
{x:3,y:6}
# expected output
1
2
3

Selecting a column of an input table in a SQL operator

# spq
SELECT x
# input
{x:1,y:4}
{x:2,y:5}
{x:3,y:6}
# expected output
{x:1}
{x:2}
{x:3}

Selecting a column of an named input table

# spq
let input = (
  values
    {x:1,y:4},
    {x:2,y:5},
    {x:3,y:6}
)
SELECT x FROM input
# input

# expected output
{x:1}
{x:2}
{x:3}

Literals

Literals

Literal values represent specific instances of a type embedded directly into an expression like the integer 1, the record {x:1.5,y:-4.0}, or the mixed-type array [1,"foo"].

Any valid SUP serialized text is a valid literal in SuperSQL. In particular, complex-type expressions composed recursively of other literal values can be used to construct any complex literal value, e.g.,

• record expressions,
• array expressions,
• set expressions,
• map expressions, and
• error expressions.

Literal values of types enum, union, and named may be created with a cast.

Logic

Logic

The keywords and, or, not, and ! perform logic on operands of type bool. The binary operators and and or operate on Boolean values and result in an error value if either operand is not a Boolean. Likewise, not (and its equivalent !) operates on its unary operand and results in an error if its operand is not type bool. Unlike many other languages, non-Boolean values are not automatically converted to Boolean type using “truthiness” heuristics.

Slices

Slices

A slice expression is a variation of an index index expression that returns a range of values instead of a single value and can be applied to sliceable data types. A slice has the form

<entity> [ <from> : <to> ]

where <entity> is an expression that returns an sliceable value and <from> and <to> are expressions that are coercible to integers.

Sliceable entities include arrays, sets, strings, and bytes.

The value <from> and <to> represent a range of index values to form a subset of elements from the <entity> term provided. The range begins at the <from> position and ends one element before the <to> position. A negative value of <from> or <to> represents a position relative to the end of the value being sliced.

If the <entity> expression is an array, then the result is an array of elements comprising the indicated range.

If the <entity> expression is a set, then the result is a set of elements comprising the indicated range ordered by total order of values.

If the <entity> expression is a string, then the result is a substring consisting of unicode code points comprising the given range.

If the <entity> expression is type bytes, then the result is a bytes sequence consisting of bytes comprising the given range.

Index Base

The index base for slice expressions is determined identically to the index base for indexing. By default, slice indexes are zero based.

Examples

Simple slices

# spq
values a[1:3]
# input
{a:[1,2,3,4]}
{a:|[1,2,3,4]|}
{a:"1234"}
{a:0x01020304}
# expected output
[2,3]
|[2,3]|
"23"
0x0203

1-based slices

# spq
pragma index_base = 1
values a[1:3]
# input
{a:[1,2,3,4]}
{a:|[1,2,3,4]|}
{a:"1234"}
{a:0x01020304}
# expected output
[1,2]
|[1,2]|
"12"
0x0102

Prefix and suffix slices

# spq
values {prefix:a[:2],suffix:a[-2:-1]}
# input
{a:[1,2,3,4]}
{a:|[1,2,3,4]|}
{a:"1234"}
{a:0x01020304}
# expected output
{prefix:[1,2],suffix:[3]}
{prefix:|[1,2]|,suffix:|[3]|}
{prefix:"12",suffix:"3"}
{prefix:0x0102,suffix:0x03}

Subqueries

Subqueries

A subquery is a query embedded in an expression.

When the expression containing the subquery is evaluated, the query is run with an input consisting of a single value equal to the value being evaluated by the expression.

The syntax for a subquery is simply a query in parentheses as in

( <query> )

where <query> is any query, e.g., the query

values {s:(values "hello, world" | upper(this))}

results in in the value {s:"HELLO, WORLD"}.

Except for subqueries appearing as the right-hand side of an in operator, the result of a subquery must be a single value. When multiple values are generated, an error is produced.

For the in operator, any subquery on the right-hand side is always treated as an array subquery, thus providing compatibility with SQL syntax.

Array Subqueries

When multiple values are expected, an array subquery can be used to group the multi-valued result into a single-valued array.

The syntax for an array subquery is simply a query in square brackets as in

[ <query> ]

where <query> is any query, e.g., the query

values {a:[values 1,2,3 | values this+1]}

results in the value {a:[2,3,4]}.

An array subquery is shorthand for

( <query> | collect(this) )

e.g., the array subquery above could also be rewritten as

values {a:(values 1,2,3 | values this+1 | collect(this))}

Independent Subqueries

A subquery that depends on its input as described above is called a dependent subquery.

When the subquery ignores its input value, e.g., when it begins with a from operator, then they query is called an independent subquery.

For efficiency, the system materializes independent subqueries so that they are evaluated just once.

For example, this query

let input = (values 1,2,3)
values 3,4
| values {that:this,count:(from input | count())}

evaluates the subquery from input | count() just once and materializes the result. Then, for each input value 3 and 4, the result is emitted, e.g.,

{that:3,count:3::uint64}
{that:4,count:3::uint64}

Correlated Subqueries

When a subquery appears within a SQL operator, relational scope is active and references to table aliases and columns may reach a scope that is outside of the subquery. In this case, the subquery is a correlated subquery.

Correlated subqueries are not yet supported. They are detected and a compile-time error is reported when encountered.

A correlated subquery can always be rewritten as a pipe subquery using unnest using this pattern:

unnest {outer:this,inner:[<query>]}

where <query> generates the correlated subquery values, then they can be accessed as if the outer field is the outer scope and the inner field is the subquery scope.

Named Subqueries

When a previously declared named query is referenced in an expression, it is automatically evaluated as a subquery, e.g.,

let q = (values 1,2,3 | max(this))
values q+1

outputs the value 4.

When a named query is expected to return multiple values, it should be referenced as an array subquery, e.g.,

let q = (values 1,2,3)
values [q]

outputs the value [1,2,3].

Recursive Subqueries

When subqueries are combined with recursive invocation of the function they appear in, some powerful patterns can be constructed.

For example, the visitor-walk pattern can be implemented using recursive subqueries and function values.

Here’s a template for walk:

fn walk(node, visit):
  case kind(node)
  when "array" then
    [unnest node | walk(this, visit)]
  when "record" then
    unflatten([unnest flatten(node) | {key,value:walk(value, visit)}])
  when "union" then
    walk(under(node), visit)
  else visit(node)
  end

Note

In this case, we are traversing only records and arrays. Support for flattening and unflattening maps and sets is forthcoming.

Here, walk is invoking an array subquery on the unnested entities (records or arrays), calling the walk function recursively on each item, then assembling the results back into an array (i.e., the raw result of the array subquery) or a record (i.e., calling unflatten on the key/value pairs returned in the array).

If we call walk with this function on an arbitrary nested value

fn addOne(node): case typeof(node) when <int64> then node+1 else node end

then each leaf value of the nested value of type int64 would be incremented while the other leaves would be left alone. See the example below.

Examples

Operate on arrays with values shortcuts and arrange answers into a record

# spq
values {
    squares:[unnest this | this*this],
    roots:[unnest this | round(sqrt(this)*100)*0.01]
}
# input
[1,2,3]
[4,5]
# expected output
{squares:[1,4,9],roots:[1.,1.41,1.73]}
{squares:[16,25],roots:[2.,2.24]}

Multi-valued subqueries emit an error

# spq
values (values 1,2,3)
# input

# expected output
error("query expression produced multiple values (consider [subquery])")

Multi-valued subqueries can be invoked as an array subquery

# spq
values [values 1,2,3]
# input

# expected output
[1,2,3]

Right-hand side of “in” operator is always an array subquery

# spq
let data = (values {x:1},{x:2})
where this in (select x from data)
# input
1
2
3
# expected output
1
2

Independent subqueries in SQL operators are supported while correlated subqueries are not

# spq
let input = (values {x:1},{x:2},{x:3})
select *
from input
where x >= (select avg(x) from input)  
# input

# expected output
{x:2}
{x:3}

Correlated subqueries in SQL operators not yet supported

# spq
select *
from (values (1),(2)) a(x)
where exists (
  select 1
  from (values (3),(4)) b(y)
  where x=y
)
# input

# expected output
correlated subqueries not currently supported at line 6, column 9:
  where x=y
        ~

Recursive subqueries inside function implementing the walk-visitor pattern

# spq
fn walk(node, visit):
  case kind(node)
  when "array" then
    [unnest node | walk(this, visit)]
  when "record" then
    unflatten([unnest flatten(node) | {key,value:walk(value, visit)}])
  when "union" then
    walk(under(node), visit)
  else visit(node)
  end
fn addOne(node): case typeof(node) when <int64> then node+1 else node end
values walk(this, &addOne)
# input
1
[1,2,3]
[{x:[1,"foo"]},{y:2}]
# expected output
2
[2,3,4]
[{x:[2,"foo"]},{y:3}]

Types

Types

SuperSQL has a comprehensive types system that adheres to the super-structured data model comprising primitive types, complex types, sum types, named types, the null type, and first-class errors and types.

The syntax of individual literal values as well as types follows the SUP format in that any legal SUP value is also a valid SuperSQL literal.

Likewise, any SUP type is also valid type syntax, which may be used in cast expressions or type declarations.

Note that the type decorators in SUP utilize a double colon (::) syntax that is compatible with cast expressions.

Arguments to functions and operators are all dynamically typed, yet certain functions expect certain specific types or classes of data types. The following names for these categories of types are used in throughout the documentation:

• any - any SuperSQL data type
• float - any floating point type
• int - any signed or unsigned integer type
• number - either float or int
• record - any record type
• set - any set type
• map - any map type
• function - a function reference of lambda expression

To be clear, none of these categorical names are actual types and may not be used in a SuperSQL query. They are simply used to document expected type categories.

Note

In a future version of SuperSQL, user-defined function and operator declarations will include optional type signatures and these names representing type categories may be included in the language for that purpose.

Numbers

Numbers

Numbers in SuperSQL follow the customary semantics and syntax of SQL and other programming languages and include:

• signed integers,
• unsigned integers,
• floating point, and
• decimal.

Signed Integers

A 64-bit signed integer literal of type int64 is formed from an optional minus character (-) followed by a sequence of one or more decimal digits whose value is between -2^63 and 2^63 - 1 inclusively.

Values of signed integer of other widths can be created when reading external data that corresponds to such types or by casting numbers to the desired types. These signed types include:

• int8,
• int16, and
• int32.

Note

The int128 type is not yet implemented in SuperDB.

For backward compatibility with SQL, syntactic aliases for signed integers are defined as follows:

• BIGINT maps to int64
• INT maps to int32
• INTEGER maps to int32
• SMALLINT maps to int16

Unsigned Integers

A sequence of one or more decimal digits that has a value greater than 2^63 - 1 and less than 2^64 exclusively forms an unsigned 64-bit integer literal.

Values of unsigned integer of other widths can be created when reading external data that corresponds to such types or by casting numbers to the desired types. These unsigned types include:

• uint8,
• uint16, and
• uint32.

Note

The uint128 type is not yet implemented in SuperDB.

Floating Point

A sequence of one or more decimal digits followed by a decimal point (.) followed optionally by one or more decimal digits forms a 64-bit IEEE floating point value of type float64. Alternatively, a floating point value may appear in scientific notation having the form of a mantissa number (integer or with decimal point) followed by the character e and in turn followed by a signed integer exponent.

Also Inf, +Inf, -Inf, or NaN are valid 64-bit floating point numbers.

Floating-point values with widths other than float64 can be created when reading external data that corresponds to such other types or by casting numbers to the desired floating point type float32 or float16.

For backward compatibility with SQL, syntactic aliases for signed integers are defined as follows:

• REAL maps to float32
• FLOAT maps to float64
• DOUBLE PRECISION maps to float64

Note

The FLOAT(n) SQL types are not yet implemented by SuperSQL.

Decimal

Note

The decimal type is not yet implemented in SuperSQL.

Coercion

Mixed-type numeric values used in expressions are promoted via an implicit cast to the type that is best compatible with an operation or expected input type. This process is called coercion.

For example, in the expression

1::int8 + 1::int16

the 1::int8 value is cast to 1::int16 and the result is 2::int16.

Similarly, in

values 1::int8, 1::int16 | aggregate sum(this)

the input values to sum() are coerced to int64 and the result is 2::int64.

Note

Further details of coercion rules are forthcoming in a future version of this documentation.

Examples

Signed integers

# spq
values 1, 0, -1, 9223372036854775807
| values f"{this} is type {typeof(this)}"
# input

# expected output
"1 is type <int64>"
"0 is type <int64>"
"-1 is type <int64>"
"9223372036854775807 is type <int64>"

Other signed integer types

# spq
values 1, 200, 70000, 9223372036854775807
| values this::int8, this::int16, this::int32, this::int64
# input

# expected output
1::int8
1::int16
1::int32
1
error({message:"cannot cast to int8",on:200})
200::int16
200::int32
200
error({message:"cannot cast to int8",on:70000})
error({message:"cannot cast to int16",on:70000})
70000::int32
70000
error({message:"cannot cast to int8",on:9223372036854775807})
error({message:"cannot cast to int16",on:9223372036854775807})
error({message:"cannot cast to int32",on:9223372036854775807})
9223372036854775807

Unsigned integers

# spq
values 1, 200, 70000, 9223372036854775807
| values this::uint8, this::uint16, this::uint32, this::uint64
| values f"{this} is type {typeof(this)}"
# input

# expected output
"1 is type <uint8>"
"1 is type <uint16>"
"1 is type <uint32>"
"1 is type <uint64>"
"200 is type <uint8>"
"200 is type <uint16>"
"200 is type <uint32>"
"200 is type <uint64>"
error({message:"cannot cast to uint8",on:70000})
error({message:"cannot cast to uint16",on:70000})
"70000 is type <uint32>"
"70000 is type <uint64>"
error({message:"cannot cast to uint8",on:9223372036854775807})
error({message:"cannot cast to uint16",on:9223372036854775807})
error({message:"cannot cast to uint32",on:9223372036854775807})
"9223372036854775807 is type <uint64>"

Floating-point numbers

# spq
values 1., 1.23, 18446744073709551615., 1.e100, +Inf, -Inf, NaN
| values f"{this} is type {typeof(this)}"
# input

# expected output
"1 is type <float64>"
"1.23 is type <float64>"
"1.8446744073709552e+19 is type <float64>"
"1e+100 is type <float64>"
"+Inf is type <float64>"
"-Inf is type <float64>"
"NaN is type <float64>"

Other floating-point types

# spq
values 1., 1.23, 18446744073709551615., 1.e100, +Inf, -Inf, NaN
| values this::float16, this::float32, this::float64
| values f"{this} is type {typeof(this)}"
# input

# expected output
"1 is type <float16>"
"1 is type <float32>"
"1 is type <float64>"
"1.23046875 is type <float16>"
"1.2300000190734863 is type <float32>"
"1.23 is type <float64>"
"+Inf is type <float16>"
"1.8446744073709552e+19 is type <float32>"
"1.8446744073709552e+19 is type <float64>"
"+Inf is type <float16>"
"+Inf is type <float32>"
"1e+100 is type <float64>"
"+Inf is type <float16>"
"+Inf is type <float32>"
"+Inf is type <float64>"
"-Inf is type <float16>"
"-Inf is type <float32>"
"-Inf is type <float64>"
"NaN is type <float16>"
"NaN is type <float32>"
"NaN is type <float64>"

Strings

Strings

The string type represents any valid UTF-8 string.

For backward compatibility with SQL, syntactic aliases for type string are defined as follows:

• CHARACTER VARYING
• CHARACTER
• TEXT
• VARCHAR

A string is formed by enclosing the string’s unicode characters in quotation marks whereby the following escape sequences allowed:

The backslash character (\) and the control characters (U+0000 through U+001F) must be escaped.

In SQL expressions, the quotation mark is a single quote character (') and in pipe expressions, the quotation mark may be either single quote or double quote (").

In single-quote strings, the single-quote character must be escaped and in double-quote strings, the double-quote character must be escaped.

Raw String

Raw strings or r-strings are expressed as the character r followed by a single- or double-quoted string, where any backslash characters are treated literally and not as an escape sequence. For example, r'foo\bar' is equivalent to 'foo\\bar'.

Unlike other string syntaxes, raw strings may have embedded newlines, e.g., a literal newline inside of the string rather than the character sequence \n as in

r"foo
bar"

Formatted Strings

Formatted strings or f-strings are expressed as the character f followed by a single- or double-quoted string and may contain embedded expressions denoted within curly braces { }.

Examples

Various strings

# spq
values 'hello, world', len('foo'), "SuperDB", "\"quoted\"", 'foo'+'bar'
# input

# expected output
"hello, world"
3
"SuperDB"
"\"quoted\""
"foobar"

String literal vs field identifier in a SQL SELECT statement

# spq
SELECT 'x' as s, "x" as x
# input
{x:1}
{x:2}
# expected output
{s:"x",x:1}
{s:"x",x:2}

Formatted strings

# spq
values f'{x} + {y} is {x+y}'
# input
{x:1,y:3}
{x:2,y:4}
# expected output
"1 + 3 is 4"
"2 + 4 is 6"

Raw strings

# spq
values r'foo\nbar\t'
# input

# expected output
"foo\\nbar\\t"

Raw strings with embedded newline

# spq
values r'foo
bar'
# input

# expected output
"foo\nbar"

Booleans

Booleans

The bool type represents a type that has the values true, false, or null.

For backward compatibility with SQL, BOOLEAN is a syntactic alias for type bool.

Examples

Comparisons produces Boolean values

# spq
values 1==1, 1>2
# input

# expected output
true
false

Booleans are used as predicates

# spq
values 1==1, 1>2
# input

# expected output
true
false

Booleans operators perform logic on Booleans

# spq
values {and:a and b, or:a or b}
# input
{a:false,b:false}
{a:false,b:true}
{a:true,b:true}
# expected output
{and:false,or:false}
{and:false,or:true}
{and:true,or:true}

Boolean aggregate functions

# spq
aggregate andA:=and(a), andB:=and(b), orA:=or(a), orB:=or(b)
# input
{a:false,b:false}
{a:false,b:true}
# expected output
{andA:false,andB:false,orA:false,orB:true}

Bytes

Bytes

The bytes type represents an arbitrary sequence of 8-bit bytes.

The character sequence 0x followed by an even number of hexadecimal digits forms a bytes type.

An empty bytes value is simply 0x followed by no digits.

For backward compatibility with SQL, BYTEA is a syntactic alias for type bytes.

Examples

# spq
values
  0x0102beef,
  'hello, world'::bytes,
  len(0x010203),
  0x,
  null::bytes
# input

# expected output
0x0102beef
0x68656c6c6f2c20776f726c64
3
0x
null::bytes

Networks/IPs

Networks/IPs

The ip type represents an internet address and supports both IPv4 and IPv6 variations. The net type represents an ip value with a contiguous network mask as indicated by the number of bits in the mask.

For backward compatibility with SQL, syntactic aliases for signed integers are defined as follows:

• CIDR maps to net
• INET maps to ip

A 32-bit IPv4 address is formed using dotted-decimal notation, e.g., a string of base-256 decimal numbers separated by . as in 128.32.130.100 or 10.0.0.1.

A 128-bit IPv6 is formed from a sequence of eight groups of four hexadecimal digits separated by colons (:).

For IPv6 addresses, leading zeros in each group can be omitted (e.g., the sequence 2001:0db8 becomes 2001:db8) and consecutive groups of zeros can be compressed using a double colon (::) but this can only be done once to avoid ambiguity, e.g.,

2001:0db8:0000:0000:0000:0000:0000:0001

can be expressed as 2001:db8::1.

A value of type net is formed as an IPv4 or IPv6 address followed by a slash (/) followed by a decimal integer indicating the numbers of bits of contiguous network as in 128.32.130.100/24 or fc00::/7.

Note that unlike other SQL dialects that require IP addresses and networks to be formatted inside quotation marks, SuperSQL treats these data types as first-class elements that need not be quoted.

Examples

# spq
values
  128.32.130.100,
  10.0.0.1,
  ::2001:0db8,
  2001:0db8:0000:0000:0000:0000:0000:0001
| values this, typeof(this)
# input

# expected output
128.32.130.100
<ip>
10.0.0.1
<ip>
::2001:db8
<ip>
2001:db8::1
<ip>

# spq
values 128.32.130.100/24, fc00::/7
| values this, typeof(this)
# input

# expected output
128.32.130.0/24
<net>
fc00::/7
<net>

Date/Times

Date/Times

Warning

These data types are going to change in a forthcoming release of SuperSQL.

The time type represents an unsigned 64-bit number of nanoseconds since epoch.

The duration type represents a signed 64-bit number of nanoseconds.

For backward compatibility with SQL, INTERVAL is a syntactic alias for type duration.

These data types are incompatible with SQL data and time types. A future version of SuperSQL will change the time type to be SQL compatible and add support for other SQL date/time and interval types. At that time, detailed syntax and semantics for these types will be documented here.

Type Values

Type Values

Types in SuperSQL are first class and conform with the type type in the super-structured data model. The type type represents the type of a type value.

A type value is formed by enclosing a type specification in angle brackets (< followed by the type followed by >).

For example, the integer type int64 is expressed as a value with the syntax <int64>.

The syntax for primitive type names are listed in the data model specification and have the same syntax in SuperSQL. Complex types also follow the SUP syntax for types.

Note that the type of a type value is simply type.

Here are a few examples of complex types:

• a simple record type - {x:int64,y:int64}
• an array of integers - [int64]
• a set of strings - |[string]|
• a map of strings keys to integer values - |{string,int64}|
• a union of string and integer - string|int64

Complex types may be composed in a nested fashion, as in [{s:string}|{x:int64}] which is an array of type union of two types of records.

The typeof function returns a value’s type as a value, e.g., typeof(1) is <int64> and typeof(<int64>) is <type>.

Note the somewhat subtle difference between a record value with a field t of type type whose value is type string

{t:<string>}

and a record type used as a value

<{t:string}>

First-class types are quite powerful because types can serve as grouping keys or be used in data shaping logic. A common workflow for data introspection is to first perform a search of exploratory data and then count the shapes of each type of data as follows:

search ... | count() by typeof(this)

Examples

Various type examples using f-string and typeof

# spq
values f"{this} has type {typeof(this)}"
# input
1
"foo"
1.5
192.168.1.1
192.168.1.0/24
[1,"bar"]
|[1,2,3]|
2025-08-21T21:22:18.046568Z
1d3h
<int64>
# expected output
"1 has type <int64>"
"foo has type <string>"
"1.5 has type <float64>"
"192.168.1.1 has type <ip>"
"192.168.1.0/24 has type <net>"
"[1,\"bar\"] has type <[int64|string]>"
"|[1,2,3]| has type <|[int64]|>"
"2025-08-21T21:22:18.046568Z has type <time>"
"1d3h has type <duration>"
"<int64> has type <type>"

Count the different types in the input

# spq
count() by typeof(this) | sort this
# input
1
2
"foo"
10.0.0.1
<string>
# expected output
{typeof:<int64>,count:2::uint64}
{typeof:<string>,count:1::uint64}
{typeof:<ip>,count:1::uint64}
{typeof:<type>,count:1::uint64}

Records

Records

Records conform to the record type in the super-structured data model and follow the syntax of records in the SUP format, i.e., a record type has the form

{ <name> : <type>, <name> : <type>, ... }

where <name> is an identifier or string and <type> is any type.

Any SUP text defining a record value is a valid record literal in the SuperSQL language.

For example, this is a simple record value

{number:1,message:"hello,world"}

whose type is

{number:int64,message:string}

An empty record value and an empty record type are both represented as {}.

Records can be created by reading external data (SUP files, database data, Parquet values, JSON objects, etc) or by constructing instances using record expressions or other SuperSQL functions that produce records.

Record Expressions

Record values are constructed from a record expression that is comprised of zero or more comma-separated elements contained in braces:

{ <element>, <element>, ... }

where an <element> has one of three forms:

• a named field of the form <name> : <expr> where <name> is an identifier or string and <expr> is any expression,
• any expression by itself where the field name is derived from the expression text as defined below, or
• a spread expression of the form ...<expr> where <expr> is an arbitrary expression that should evaluate to a record value.

The spread form inserts all of the fields from the resulting record. If a spread expression results in a non-record type (e.g., errors), then that part of the record is simply elided. Note that the field names for the spread come from the constituent record values.

The fields of a record expression are evaluated left to right and when field names collide the rightmost instance of the name determines that field’s value.

Derived Field Names

When an expression is present without a field name, the field name is derived from the expression text as follows:

• for a dotted path expression, the name is the last element of the path;
• for a function or aggregate function, the name is the name of the function;
• for this, the name is that;
• otherwise, the name is the expression text formatted in a canonical form.

Examples

A simple record literal

# spq
values {a:1,b:2,s:"hello"}
# input

# expected output
{a:1,b:2,s:"hello"}

A record expression with spreads operating on various input values

# spq
values {a:0},{x}, {...r}, {a:0,...r,b:3}
# input
{x:1,y:2,r:{a:1,b:2}}
# expected output
{a:0}
{x:1}
{a:1,b:2}
{a:1,b:3}

A record literal with casts

# spq
values {b:true,u:1::uint8,a:[1,2,3],s:"hello"::=CustomString}
# input

# expected output
{b:true,u:1::uint8,a:[1,2,3],s:"hello"::=CustomString}

A record expression with an unnamed expression

# spq
values {1+2*3}
# input

# expected output
{"1+2*3":7}

Selecting a record expression with an unnamed expression

# spq
select {1+2*3} as x
# input

# expected output
{x:{"1+2*3":7}}

Arrays

Arrays

Arrays conform to the array type in the super-structured data model and follow the syntax of arrays in the SUP format, i.e., an array type has the form

[ <type> ]

where <type> is any type.

Any SUP text defining an array value is a valid array literal in the SuperSQL language.

For example, this is a simple array value

[1,2,3]

whose type is

[int64]

An empty array value has the form [] and an empty array type defaults to an array of type null, i.e., [null], unless otherwise cast, e.g., []::[int64] represents an empty array of integers.

Arrays can be created by reading external data (SUP files, database data, Parquet values, JSON objects, etc) or by constructing instances using array expressions or other SuperSQL functions that produce arrays.

Array Expressions

Array values are constructed from an array expression that is comprised of zero or more comma-separated elements contained in brackets:

[ <element>, <element>, ... ]

where an <element> has one of two forms:

<expr>

or

...<expr>

<expr> may be any valid expression.

The first form is simply an element in the array, the result of <expr>.

The second form is the array spread operator ..., which expects an array or set value as the result of <expr> and inserts all of the values from the result. If a spread expression results in neither an array nor set, then the value is elided.

When the expressions result in values of non-uniform type, then the types of the array elements become a sum type of the types present, tied together with the corresponding union type.

Examples

# spq
values [1,2,3],["hello","world"]
# input

# expected output
[1,2,3]
["hello","world"]

Arrays can be concatenated using the spread operator

# spq
values [...a,...b,5]
# input
{a:[1,2],b:[3,4]}
# expected output
[1,2,3,4,5]

Arrays with mixed type are tied together with a union type

# spq
values typeof([1,"foo"])
# input

# expected output
<[int64|string]>

The collect aggregate function builds an array and uses a sum type for the mixed-type elements

# spq
collect(this) | values this, typeof(this)
# input
1
2
3
"hello"
"world"
# expected output
[1,2,3,"hello","world"]
<[int64|string]>

Sets

Sets

Sets conform to the set type in the super-structured data model and follow the syntax of sets in the SUP format, i.e., a set type has the form

|[ <type> ]|

where <type> is any type.

Any SUP text defining a set value is a valid set literal in the SuperSQL language.

For example, this is a simple set value

|[1,2,3]|

whose type is

|[int64]|

An empty set value has the form |[]| and an empty set type defaults to a set of type null, i.e., |[null]|, unless otherwise cast, e.g., |[]|::[int64] represents an empty set of integers.

Sets can be created by reading external data (SUP files, database data, Parquet values, JSON objects, etc) or by constructing instances using set expressions or other SuperSQL functions that produce sets.

Set Expressions

Set values are constructed from a set expression that is comprised of zero or more comma-separated elements contained in pipe brackets:

|[ <element>, <element>, ... ]|

where an <element> has one of two forms:

<expr>

or

...<expr>

<expr> may be any valid expression.

The first form is simply an element in the set, the result of <expr>.

The second form is the set spread operator ..., which expects an array or set value as the result of <expr> and inserts all of the values from the result. If a spread expression results in neither an array nor set, then the value is elided.

When the expressions result in values of non-uniform type, then the types of the set elements become a sum type of the types present, tied together with the corresponding union type.

Examples

# spq
values |[3,1,2]|,|["hello","world","hello"]|
# input

# expected output
|[1,2,3]|
|["hello","world"]|

Arrays and sets can be concatenated using the spread operator

# spq
values |[...a,...b,4]|
# input
{a:[1,2],b:|[2,3]|}
# expected output
|[1,2,3,4]|

# spq
values [1,2,3],["hello","world"]
# input

# expected output
[1,2,3]
["hello","world"]

Sets with mixed types are tied together with a union type

# spq
values typeof(|[1,"foo"]|)
# input

# expected output
<|[int64|string]|>

The union aggregate function builds a set using a sum type for the mixed-type elements

# spq
union(this) | values this, typeof(this)
# input
1
2
3
1
"hello"
"world"
"hello"
# expected output
|[1,2,3,"hello","world"]|
<|[int64|string]|>

Maps

Maps

Maps conform to the map type in the super-structured data model and follow the syntax of maps in the SUP format, i.e., a map type has the form

|{ <key> : <value> }|

where <key> and <value> are any types and represent the keys and values types of the map.

Any SUP text defining a map value is a valid map literal in the SuperSQL language.

For example, this is a simple map value

|{"foo":1,"bar":2}|

whose type is

|{string:int64}|

An empty map value has the form |{}| and an empty map type defaults to a map with null types, i.e., |{null:null}|, unless otherwise cast, e.g., |{}|::|{string:int64}| represents an empty map of string keys and integer values.

Maps can be created by reading external data (SUP files, database data, Parquet values, etc) or by constructing instances using map expressions or other SuperSQL functions that produce maps.

Map Expressions

Map values are constructed from a map expression that is comprised of zero or more comma-separated key-value pairs contained in pipe braces:

|{ <key> : <value>, <key> : <value> ... }|

where <key> and <value> may be any valid expression.

Note

The map spread operator is not yet implemented.

When the expressions result in values of non-uniform type of either the keys or the values, then their types become a sum type of the types present, tied together with the corresponding union type.

Examples

# spq
values |{"foo":1,"bar"+"baz":2+3}|
# input

# expected output
|{"foo":1,"barbaz":5}|

Look up network of host in a map and annotate if present

# spq
const networks = |{
    192.168.1.0/24:"private net 1",
    192.168.2.0/24:"private net 2",
    10.0.0.0/8:"net 10"
}|
note:=coalesce(networks[network_of(host)], f"unknown network for host {host}")
# input
{host:192.168.1.100}
{host:192.168.2.101}
{host:192.168.3.102}
{host:10.0.0.1}
# expected output
{host:192.168.1.100,note:"private net 1"}
{host:192.168.2.101,note:"private net 2"}
{host:192.168.3.102,note:"unknown network for host 192.168.3.102"}
{host:10.0.0.1,note:"net 10"}

Unions

Unions

The union type provides the foundation for sum types in SuperSQL.

Unions conform with the definition of the union type in the super-structured data model and follow the syntax of unions in the SUP format, i.e., a union type has the form

<type> | <type>, ...

where <type> is any type and the set of types are unique.

A union literal can be created by casting a literal that is a member of a union type to that union type, e.g.,

1::(int64|string)

When the type of the value cast to a union is not a member of that union, an attempt is made to coerce the value to one of available member types.

To precisely such control coercion, an explicit first cast may be used as in

1::int8::(int8|int64|string)

Union values can be created by reading external data (SUP files, database data, JSON objects, etc), by constructing instances with a type cast as above, or with other SuperSQL functions or expressions that produce unions like the fuse operator.

Union values are also created when array, set, and map expressions encounter mix-typed elements that automatically express as union values.

For example,

values typeof([1,"foo"])

results in <[int64|string]>.

Union Value Semantics

Internally, every union value includes a tag indicating which of its member types the value belongs to along with that actual value.

In many languages, such tags are explicit names called a discriminant and the underlying value can be accessed with a dot operator, e.g., u.a where u is a union value and a is the discriminant. When the instance of u does not correspond to the type indicated by a, the result might be null.

In other languages, the discriminant is the type name, e.g., u.(int64).

However, SuperSQL is polymorphic so there is no requirement to explicitly discriminate the member type of a union value. When an expression operator or function references a union value in computation, then the underlying value in its member type is automatically expressed from the union value.

For example, this predicate is true

values 1::(int64|string)==1

because the union value is automatically expressed as 1::int64 by the comparison operator.

Likewise

values 1::(int64|string)+2::(int64|string)

results in 3::int64. Note that because of automatic expression, the union type is not retained here.

Passing a union value to a function, however, does not involve evaluation and thus automatic expression does not occur here, e.g.,

values typeof(1::(int64|string))

is <int64|string> because the union value is not automatically expressed as 1::int64 when it is passed to the typeof function.

When desired, the under function may be used to express the underlying value explicitly. For example,

values typeof(under(1::(int64|string)))

results in <int64>.

Union Dispatch

Languages with sum types often include a construct to dispatch the union to a case for each of its possible types.

Because SuperSQL is polymorphic, union dispatch is not generally needed. Instead, union values are simply operated upon and the “right thing happens”.

That said, union dispatch may be accomplished with the switch operator or a case expression.

For example, switch can be used to route union values to different branches of a query:

values
  {u:1::(int64|string|ip)},
  {u:"foo"::(int64|string|ip)},
  {u:192.168.1.1::(int64|string|ip)}
| switch typeof(under(u))
    case <int64> ( ... )
    case <string> ( ... )
    case <ip> ( ... )
    default ( values error({message: "unknown type", on:this}) )
| ...

Note

Note the presence of a default case above. In statically typed languages with sum types, the compiler can ensure that all possible cases for a union are covered and report an error otherwise. In this case, there would be no need for a default. A future version of SuperSQL will include more comprehensive compile-time type checking and will include a mechanism for explicit union dispatch with static type checking.

A case expression can also be used to dispatch union values inside of an expression as in

values
  {u:1::(int64|string|ip)},
  {u:"foo"::(int64|string|ip)},
  {u:192.168.1.1::(int64|string|ip)}
| values
    case typeof(under(u))
      when <int64> then u+1
      when <string> then upper(u)
      when <ip> then network_of(u)
      else "unknown"
    end

Examples

Cast primitive values to a union type

# spq
values this::(int64|string)
# input
1
"foo"
# expected output
1::(int64|string)
"foo"::(int64|string)

Explicitly express the underlying union value using under

# spq
values under(this)
# input
1::(int64|string)
# expected output
1

Take the type of mixed-type array showing its union-typed construction

# spq
typeof(this)
# input
[1,"foo"]
# expected output
<[int64|string]>

Enums

Enums

The enum type represents a set of symbols, e.g., to represent categories by name. It conforms to the definition of the enum type in the super-structured data model and follows the syntax of enums in the SUP format, i.e., an enum type has the form

enum ( <name>, <name>, ... )

where <name> is an identifier or string.

For example, this is a simple enum type:

enum(hearts,diamonds,spades,clubs)

and this is a value of that type:

'hearts'::enum(hearts,diamonds,spades,clubs)

Enum serialization in the SUP format is fairly verbose as the set of symbols must be enumerated anywhere the type appears. In the binary formats of BSUP and CSUP, the enum symbols are encoded efficiently just once.

Examples

# spq
const suit = <enum(hearts,diamonds,spades,clubs)>
values f"The value {this} {is(this, suit)? 'is' : 'is not'} a suit enum"
# input
"hearts"
"diamonds"::enum(hearts,diamonds,spades,clubs)
# expected output
"The value hearts is not a suit enum"
"The value diamonds is a suit enum"

Errors

Errors

Errors in SuperSQL are first class and conform with the error type in the super-structured data model.

Error types have the form

error ( <type> )

where <type> is any type.

Error values can be created with the error function of the form

error ( <value > )

where <value> is any value.

Error values can also be created by reading external data (SUP files or database data) that contains serialized error values or they can arise when any operator or function encounters an error and produces an error value to describe the condition.

In general, expressions and functions that result in errors simply return a value as an error type as a result. This encourages a powerful flow-style of error handling where errors simply propagate from one operation to the next and land in the output alongside non-error values to provide a very helpful context and rich information for tracking down the source of errors. There is no need to check for error conditions everywhere or look through auxiliary logs to find out what happened.

The value underneath an error can be accessed using the under function.

For example,

values [1,2,3] | put x:=1

produces the error

error({message:"put: not a record",on:[1,2,3]})

and the original value in the on field can be recovered with under, i.e.,

values [1,2,3] | put x:=1 | values under(this).on

produces

[1,2,3]

Structured Errors

First-class errors are particularly useful for creating structured errors. When a SuperSQL query encounters a problematic condition, instead of silently dropping the problematic error and logging an error obscurely into some hard-to-find system log as so many ETL pipelines do, the offending value can be wrapped as an error and propagated to its output.

For example, suppose a bad value shows up:

{kind:"bad", stuff:{foo:1,bar:2}}

A data-shaping query applied to ingested data could catch the bad value (e.g., as a default case in a switch) and propagate it as an error using the expression:

values error({message:"unrecognized input type",on:this})

then such errors could be detected and searched for downstream with the is_error function. For example,

is_error(this)

on the wrapped error from above produces

error({message:"unrecognized input",input:{kind:"bad", stuff:{foo:1,bar:2}}})

There is no need to create special tables in a complex warehouse-style ETL to land such errors as they can simply land next to the output values themselves.

And when transformations cascade one into the next as different stages of an ETL pipeline, errors can be wrapped one by one forming a “stack trace” or lineage of where the error started and what stages it traversed before landing at the final output stage.

Errors will unfortunately and inevitably occur even in production, but having a first-class data type to manage them all while allowing them to peacefully coexist with valid production data is a novel and useful approach that SuperSQL enables.

Missing and Quiet

SuperDB’s heterogeneous data model allows for queries that operate over different types of data whose structure and type may not be known ahead of time, e.g., different types of records with different field names and varying structure. Thus, a reference to a field, e.g., this.x may be valid for some values that include a field called x but not valid for those that do not.

What is the value of x when the field x does not exist?

A similar question faced SQL when it was adapted in various different forms to operate on semi-structured data like JSON or XML. SQL already had the null value so perhaps a reference to a missing value could simply be null.

But JSON also has null, so a reference to x in the JSON value

{"x":null}

and a reference to x in the JSON value

{}

would have the same value of null. Furthermore, an expression like x is null could not differentiate between these two cases.

To solve this problem, the missing value was proposed to represent the value that results from accessing a field that is not present. Thus, x is null and x is missing could disambiguate the two cases above.

SuperSQL, instead, recognizes that the SQL value missing is a paradox: I’m here but I’m not.

In reality, a missing value is not a value. It’s an error condition that resulted from trying to reference something that didn’t exist.

So why should we pretend that this is a bona fide value? SQL adopted this approach because it lacks first-class errors.

But SuperSQL has first-class errors so a reference to something that does not exist is an error of type error(string) whose value is error("missing"). For example,

# spq
values x
# input
{x:1}
{y:2}
# expected output
1
error("missing")

Sometimes you want missing errors to show up and sometimes you don’t. The quiet function transforms missing errors into “quiet errors”. A quiet error is the value error("quiet") and is ignored by most operators, in particular, values, e.g.,

values error("quiet")

produces no output.

Examples

Any value can be an error

# spq
error(this)
# input
0
"foo"
10.0.0.1
{x:1,y:2}
# expected output
error(0)
error("foo")
error(10.0.0.1)
error({x:1,y:2})

Divide by zero error

# spq
1/this
# input
0
# expected output
error("divide by zero")

The error type corresponding to an error value

# spq
typeof(1/this)
# input
0
# expected output
<error(string)>

The quiet function suppresses error values

# spq
values quiet(x)
# input
{x:1}
{y:2}
# expected output
1

Coalesce replaces error("missing") values with a default value

# spq
values coalesce(x, 0)
# input
{x:1}
{y:2}
# expected output
1
0

Named Types

Named Types

A named type provides a means to bind a symbolic name to a type and conforms to the named type in the super-structured data model. The named type syntax follows that of SUP format, i.e., a named type has the form

(<name>=<type>)

where <name> is an identifier or string and <type> is any type.

Named types may be defined in four ways:

• with a type declaration,
• with a cast,
• with a definition inside of another type, or
• by the input data itself.

For example, this expression

80::(port=uint16)

casts the integer 80 to a named type called port whose type is uint16.

Alternatively, named types can be declared with a type statement, e.g.,

type port = int16
values 80::port

produces the value 80::(port=uint16) as above.

Type name definitions can be embedded in another type, e.g.,

type socket = {addr:ip,port:(port=uint16)}

defines a named type socket that is a record with field addr of type ip and field port of type port, where type port is a named type for type uint16 .

Named types may also be defined by the input data itself, as super-structured data is comprehensively self describing. When named types are defined in the input data, there is no need to declare their type in a query. In this case, a SuperSQL expression may refer to the type by the name that simply appears to the runtime as a side effect of operating upon the data.

When the same name is bound to different types, a reference to that name is undefined except for the definitions within a single nested value, in which case, the most recent binding in depth-first order is used to resolve a reference to a type name.

Examples

Filter on a type name defined in the input data

# spq
where typeof(this)==<foo>
# input
1::=foo
2::=bar
3::=foo
# expected output
1::=foo
3::=foo

Emit a type name defined in the input data

# spq
values <foo>
# input
1::=foo
# expected output
<foo=int64>

Emit a missing value for an unknown type name

# spq
values <foo>
# input
1
# expected output
error("missing")

Conflicting named types appear as distinct type values

# spq
count() by typeof(this) | sort this
# input
1::=foo
2::=bar
"hello"::=foo
3::=foo
# expected output
{typeof:<bar=int64>,count:1::uint64}
{typeof:<foo=int64>,count:2::uint64}
{typeof:<foo=string>,count:1::uint64}

Nulls

Nulls

The null type represents a type that has just one value: the special value null.

A value of type null is formed simply from the keyword null representing the null value, which by default, is type null.

While all types include a null value, e.g., null::int64 is the null value whose type is int64, the null type has no other values besides the null value.

In relational SQL, a null typically indicates an unknown value. Unfortunately, this concept is overloaded as unknown values may arise from runtime errors, missing data, or an intentional value of null.

Because SuperSQL has first-class errors (obviating the need to serialize error conditions as fixed-type nulls) and sum types (obviating the need to flatten sum types into columns and occupy the absent component types with nulls), the use of null values is discouraged.

That said, SuperSQL supports the null value for backward compatibility with their pervasive use in SQL, database systems, programming languages, and serialization formats.

As in SQL, to test if a value is null, it cannot be compared to another null value, which by definition, is always false, i.e., two unknown values cannot be known to be equal. Instead the IS NULL operator or coalesce function should be used.

Examples

The null value

# spq
values typeof(null)
# input

# expected output
<null>

Test for null with IS NULL

# spq
values
  this == null,
  this != null,
  this IS NULL,
  this IS NOT NULL
# input

# expected output
null::bool
null::bool
true
false

Missing values are not null values

# spq
values {out:y}
# input
{x:1}
{x:2,y:3}
null
# expected output
{out:error("missing")}
{out:3}
{out:error("missing")}

Use coalesce to easily skip over nulls and missing values

# spq
const DEFAULT = 100
values coalesce(y,x,DEFAULT)
# input
{x:1}
{x:2,y:3}
{x:4,y:null}
null
# expected output
1
3
4
100

All types have a null value

# spq
values cast(null, this)
# input
<int64>
<string>
<int64|string>
<{x:int64,s:string}>
<[string]>
# expected output
null::int64
null::string
null::(int64|string)
null::{x:int64,s:string}
null::[string]

Operators

Operators

The components of a SuperSQL pipeline are called pipe operators. Each operator is identified by its name and performs a specific operation on a sequence of values.

Some operators, like aggregate and sort, read all of their input before producing output, though aggregate can produce incremental results when the grouping key is aligned with the order of the input.

For large queries that process all of their input, time may pass before seeing any output.

On the other hand, most operators produce incremental output by operating on values as they are produced. For example, a long running query that produces incremental output streams its results as produced, i.e., running super to standard output will display results incrementally.

The search and where operators “find” values in their input and drop the ones that do not match what is being looked for.

The values operator emits one or more output values for each input value based on arbitrary expressions, providing a convenient means to derive arbitrary output values as a function of each input value.

The fork operator copies its input to parallel branches of a pipeline, while the switch operator routes each input value to only one corresponding branch (or drops the value) based on the switch clauses.

While the output order of parallel branches is undefined, order may be reestablished by applying a sort at the merge point of the switch or fork.

Field Assignment

Several pipe operators manipulate records by modifying fields or by creating new records from component expressions.

For example,

• the put operator adds or modifies fields,
• the cut operator extracts a subset of fields, and
• the aggregate operator forms new records from aggregate functions and grouping expressions.

In all of these cases, the SuperSQL language uses the syntax := to denote field assignment and has the form:

<field> := <expr>

For example,

put x:=y+1

or

aggregate salary:=sum(income) by state:=lower(state)

This style of “assignment” to a record value is distinguished from the = symbol, which denotes Boolean equality.

The field name and := symbol may also be omitted and replaced with just the expression, as in

aggregate count() by upper(key)

or

put lower(s), a.b.c, x+1

In this case, the field name is derived using the same rules that determine the field name of an unnamed record field.

In the two examples above, the derived names are filled in as follows:

aggregate count:=count() by upper:=upper(key)
put lower:=lower(s), c:=a.b.c, `x+1`:=x+1

Call

In addition to the built-in operators, new operators can be declared that take parameters and operate on input just like the built-ins.

A declared operator is called using the call keyword:

call <id> [<arg> [, <arg> ...]]

where <id> is the name of the operator and each <arg> is an expression or function reference. The number of arguments must match the number of parameters appearing in the operator declaration.

The call keyword is optional when the operator name does not syntactically conflict with other operator syntax.

Shortcuts

When interactively composing queries (e.g., within SuperDB Desktop), it is often convenient to use syntactic shortcuts to quickly craft queries for exploring data interactively as compared to a “coding style” of query writing.

Shortcuts allow certain operator names to be optionally omitted when they can be inferred from context and are available for:

• aggregate,
• put,
• values, and
• where.

For example, the SQL expression

SELECT count(),type GROUP BY type

is more concisely represented in pipe syntax as

aggregate count() by type

but even more succinctly expressed as

count() by type

Here, the syntax of the aggregate operator is unambiguous so the aggregate keyword may be dropped.

Similarly, an expression situated in the position of a pipe operator implies a values shortcut, e.g.,

{a:x+1,b:y-1}

is shorthand for

values {a:x+1,b:y-1}

Note

The values shortcut means SuperSQL provides a calculator experience, e.g., the command super -c '1+1' emits the value 2.

When the expression is Boolean-valued, however, the shortcut is where instead of values providing a convenient means to filter values. For example

x >= 1

is shorthand for

where x >= 1

Finally the put operator can be used as a shortcut where a list of field assignments may omit the put keyword.

For example, the operation

put a:=x+1,b:=y-1

can be expressed simply as

a:=x+1,b:=y-1

To confirm the interpretation of a shortcut, you can always check the compiler’s actions by running super with the -C flag to print the parsed query in a “canonical form”, e.g.,

super -C -c 'x >= 1'
super -C -c 'count() by type'
super -C -c '{a:x+1,b:y-1}'
super -C -c 'a:=x+1,b:=y-1'

produces

where x>=1
aggregate
    count() by type
values {a:x+1,b:y-1}
put a:=x+1,b:=y-1

When composing long-form queries that are shared via SuperDB Desktop or managed in GitHub, it is best practice to include all operator names in the query text.

aggregate

Operator

  aggregate — execute aggregate functions with optional grouping expressions

Synopsis

[aggregate] <agg> [, <agg> ... ] [ by <grouping> [, <grouping> ... ] ]
[aggregate] by <grouping> [, <grouping> ... ]

where <agg> references an aggregate function optionally structured as a field assignment having the form:

[ <field> := ] <agg-func> ( [ all | distinct ] <expr> ) [ where <pred> ]

and <grouping> is a grouping expression field assignment having the form:

[ <field> := ] <expr>

Description

The aggregate operator applies aggregate functions to partitioned groups of its input values to reduce each group to one output value where the result of each aggregate function appears as a field of the result.

Each group corresponds to the unique values of the <grouping> expressions. When there are no <grouping> expressions, the aggregate functions are applied to the entire input optionally filtered by <pred>.

In the first form, the aggregate operator consumes all of its input, applies one or more aggregate functions <agg> to each input value optionally filtered by a where clause and/or organized with the grouping expressions specified after the by keyword, and at the end of input produces one or more aggregations for each unique set of grouping key values.

In the second form, aggregate consumes all of its input, then outputs each unique combination of values of the grouping expressions specified after the by keyword without applying any aggregate functions.

The aggregate keyword is optional since it can be used as a shortcut.

Each aggregate function <agg-func> may be optionally followed by a where clause, which applies a Boolean expression <pred> that indicates, for each input value, whether to include it in the values operated upon by the aggregate function. where clauses are analogous to the where operator but apply their filter to the input argument stream to the aggregate function.

The output values are records formed from the field assignments first from the grouping expressions then from the aggregate functions in left-to-right order.

When the result of aggregate is a single value (e.g., a single aggregate function without grouping expressions or a single grouping expression without aggregates) and there is no field name specified, then the output is that single value rather than a single-field record containing that value.

If the cardinality of grouping expressions causes the memory footprint to exceed a limit, then each aggregate’s partial results are spilled to temporary storage and the results merged into final results using an external merge sort.

Note

Spilling is not yet implemented for the vectorized runtime.

Examples

Average the input sequence

# spq
aggregate avg(this)
# input
1
2
3
4
# expected output
2.5

To format the output of a single-valued aggregation into a record, simply specify an explicit field for the output

# spq
aggregate mean:=avg(this)
# input
1
2
3
4
# expected output
{mean:2.5}

When multiple aggregate functions are specified, even without explicit field names, a record result is generated with field names implied by the functions

# spq
aggregate avg(this),sum(this),count()
# input
1
2
3
4
# expected output
{avg:2.5,sum:10,count:4::uint64}

Sum the input sequence, leaving out the aggregate keyword

# spq
sum(this)
# input
1
2
3
4
# expected output
10

Create integer sets by key and sort the output to get a deterministic order

# spq
set:=union(v) by key:=k | sort
# input
{k:"foo",v:1}
{k:"bar",v:2}
{k:"foo",v:3}
{k:"baz",v:4}
# expected output
{key:"bar",set:|[2]|}
{key:"baz",set:|[4]|}
{key:"foo",set:|[1,3]|}

Use a where clause

# spq
set:=union(v) where v > 1 by key:=k | sort
# input
{k:"foo",v:1}
{k:"bar",v:2}
{k:"foo",v:3}
{k:"baz",v:4}
# expected output
{key:"bar",set:|[2]|}
{key:"baz",set:|[4]|}
{key:"foo",set:|[3]|}

Use a separate where clause on each aggregate function

# spq
set:=union(v) where v > 1,
array:=collect(v) where k=="foo"
  by key:=k
| sort
# input
{k:"foo",v:1}
{k:"bar",v:2}
{k:"foo",v:3}
{k:"baz",v:4}
# expected output
{key:"bar",set:|[2]|,array:null}
{key:"baz",set:|[4]|,array:null}
{key:"foo",set:|[3]|,array:[1,3]}

Results are included for by groupings that generate null results when where clauses are used inside aggregate

# spq
sum(v) where k=="bar" by key:=k | sort
# input
{k:"foo",v:1}
{k:"bar",v:2}
{k:"foo",v:3}
{k:"baz",v:4}
# expected output
{key:"bar",sum:2}
{key:"baz",sum:null}
{key:"foo",sum:null}

To avoid null results for by groupings as just shown, filter before aggregate

# spq
k=="bar" | sum(v) by key:=k | sort
# input
{k:"foo",v:1}
{k:"bar",v:2}
{k:"foo",v:3}
{k:"baz",v:4}
# expected output
{key:"bar",sum:2}

Output just the unique key values

# spq
by k | sort
# input
{k:"foo",v:1}
{k:"bar",v:2}
{k:"foo",v:3}
{k:"baz",v:4}
# expected output
"bar"
"baz"
"foo"

assert

Operator

  assert — test a predicate and produce errors on failure

Synopsis

assert <expr>

Description

The assert operator evaluates the Boolean expression <expr> for each input value, producing its input value if <expr> evaluates to true or a structured error if it does not.

Examples

# spq
assert a > 0
# input
{a:1}
{a:-1}
# expected output
{a:1}
error({message:"assertion failed",expr:"a > 0",on:{a:-1}})

cut

Operator

  cut — extract subsets of record fields into new records

Synopsis

cut <assignment> [, <assignment> ...]

where <assignment> is a field assignment having the form:

[ <field> := ] <expr>

Description

The cut operator extracts values from each input record in the form of one or more field assignments, creating one field for each expression. Unlike the put operator, which adds or modifies the fields of a record, cut retains only the fields enumerated, much like a SQL SELECT clause.

Each left-hand side <field> term must be a field reference expressed as a dotted path or sequence of constant index operations on this, e.g., a.b.

Each right-hand side <expr> can be any expression and is optional.

When the left-hand side assignments are omitted and the expressions are simple field references, the cut operation resembles the Unix shell command, e.g.,

... | cut a,c | ...

If an expression results in error("quiet"), the corresponding field is omitted from the output. This allows you to wrap expressions in a quiet() function to filter out missing errors.

If an input value to cut is not a record, then cut still operates as defined resulting in error("missing") for expressions that reference fields of this.

Note that when the field references are all top level, cut is a special case of values with a record expression having the form:

values {<field>:<expr> [, <field>:<expr>...]}

Examples

A simple Unix-like cut

# spq
cut a,c
# input
{a:1,b:2,c:3}
# expected output
{a:1,c:3}

Missing fields show up as missing errors

# spq
cut a,d
# input
{a:1,b:2,c:3}
# expected output
{a:1,d:error("missing")}

The missing fields can be ignored with quiet

# spq
cut a:=quiet(a),d:=quiet(d)
# input
{a:1,b:2,c:3}
# expected output
{a:1}

Non-record values generate missing errors for fields not present in a non-record this

# spq
cut a,b
# input
1
{a:1,b:2,c:3}
# expected output
{a:error("missing"),b:error("missing")}
{a:1,b:2}

Invoke a function while cutting to set a default value for a field

Tip

This can be helpful to transform data into a uniform record type, such as if the output will be exported in formats such as csv or parquet (see also: fuse).

# spq
cut a,b:=coalesce(b, 0)
# input
{a:1,b:null}
{a:1,b:2}
# expected output
{a:1,b:0}
{a:1,b:2}

Field names are inferred when the left-hand side of assignment is omitted

# spq
cut a,coalesce(b, 0)
# input
{a:1,b:null}
{a:1,b:2}
# expected output
{a:1,coalesce:0}
{a:1,coalesce:2}

drop

Operator

  drop — drop fields from record values

Synopsis

drop <field> [, <field> ...]

Description

The drop operator removes one or more fields from records in the input sequence and copies the modified records to its output. If a field to be dropped is not present, then no effect for the field occurs. In particular, non-record values are copied unmodified.

Examples

Drop a field

# spq
drop b
# input
{a:1,b:2,c:3}
# expected output
{a:1,c:3}

Non-record values are copied to output

# spq
drop a,b
# input
1
{a:1,b:2,c:3}
# expected output
1
{c:3}

fork

Operator

  fork — copy values to parallel pipeline branches

Synopsis

fork
  ( <branch> )
  ( <branch> )
  ...

Description

The fork operator copies each input value to multiple, parallel branches of the pipeline.

The output of a fork consists of multiple branches that must be merged. If the downstream operator expects a single input, then the output branches are combined without preserving order. Order may be reestablished by applying a sort at the merge point.

Examples

Copy input to two pipeline branches and merge

# spq
fork
  ( pass )
  ( pass )
| sort this
# input
1
2
# expected output
1
1
2
2

from

Operator

  from — source data from databases, files, or URLs

Synopsis

from <file> [ ( format <fmt> ) ]
from <pool> [@<commit>]
from <url> [ ( format <fmt> method <method> headers <expr> body <string> ) ]
from eval(<expr>) [ ( format <fmt> method <id> headers <expr> body <string> ) ]

Description

The from operator identifies one or more sources of data as input to a query and transmits that data to its output.

It has two forms:

• a from pipe operator with pipe scoping as described here, or
• a SQL FROM clause with relational scoping.

As a pipe operator, from preserves the order of the data within a file, URL, or a sorted pool but when multiple sources are identified, the data may be read in parallel and interleaved in an undefined order.

Optional arguments to from may be appended as a parenthesized concatenation of arguments.

When reading from sources external to a database (e.g., URLs or files), the format of each data source is automatically detected using heuristics. To manually specify the format of a source and override the autodetection heuristic, a format argument may be appended as an argument and has the form

format <fmt>

where <fmt> is the name of a supported serialization format and is parsed as a text entity.

When from references a file or URL entity whose name ends in a well-known extension (e.g., .json, .sup, etc.), auto-detection is disabled and the format is implied by the extension name.

File-System Operation

When running detached from a database, the target of from is either a text entity or a file system glob.

If a text entity is parseable as an HTTP or HTTPS URL, then the target is presumed to be a URL and is processed accordingly. Otherwise, the target is assumed to be a file in the file system whose path is relative to the directory in which the super command is running.

If the target is a glob, then the glob is expanded and the files are processed in an undefined order. Any operator arguments specified after a glob target are applied to all of the matched files.

Here are a few examples illustrating file references:

from "file.sup"
from file.json
from file*.parq (format parquet)

Database Operation

When running attached to a database (i.e., using super db), the target of from is either a text entity or a regular expression or glob that matches pool names.

If a text entity is parseable as an HTTP or HTTPS URL, then the target is presumed to be a URL and is processed accordingly. Otherwise, the target is assumed to be the name of a pool in the attached database.

Local files are not accessible when attached to a database.

Note that pool names and file names have similar syntax in from but their use is disambiguated by the presence or absence of an attached database.

When multiple data pools are referenced with a glob or regular expression, they are scanned in an undefined order.

The reference string for a pool may also be appended with an @-style commitish, which specifies that data is sourced from a specific commit in a pool’s commit history.

When a single pool name is specified without an @ reference, or when using a glob or regular expression, the tip of the main branch of each pool is accessed.

The format argument is not valid with a database source.

Note

Metadata from database pools also may be sourced using from. This will be documented in a future release of SuperDB.

URL

Data sources identified by URLs can be accessed either when attached or detached from a database.

When the <url> argument begins with http: or https: and has the form of a valid URL, then the source is fetched remotely using the indicated protocol.

As a text entity, typical URLs need not be quoted though URLs with special characters must be quoted.

A format argument may be appended to a URL reference.

Other valid operator arguments control the body and headers of the HTTP request that implement the data retrieval and include:

• method <method>
• headers <expr>
• body <string>

where

• <method> is one of GET, PUT, POST, or DELETE,
• <expr> is a record expression that defines the names and values to be included as HTTP header options, and
• <body> is a text-entity string to be included as the body of the HTTP request.

Currently, the headers expression must evaluate to a compile-time constant though this may change to allow dynamic computation in a future version of SuperSQL.

Expression

The eval() form of from provides a means to read data programmatically from sources based on the <expr> argument to eval, which should return a value of type string. In this case, from reads values from its parent, applies <expr> to each value, and interprets the string result as a target to be processed.

Each string value is interpreted as a from target and must be a file path (when running detached from a database), a pool name (when attached to a database), or a URL forming a sequence of targets which are read and output by the from operator in the order encountered.

Combining Data

To combine data from multiple sources using pipe operators, from may be used in combination with other operators like fork and join.

For example, multiple pools can be accessed in parallel and combined in undefined order:

fork
  ( from PoolOne | op1 | op2 | ... )
  ( from PoolTwo | op1 | op2 | ... )
| ...

or joined according to a join condition:

fork
  ( from PoolOne | op1 | op2 | ... )
  ( from PoolTwo | op1 | op2 | ... )
| join as {left,right} on left.key=right.key
| ...

Alternatively, the right-hand leg of the join may be written as a subquery of join:

from PoolOne | op1 | op2 | ...
| join ( from PoolTwo | op1 | op2 | ... )
    as {left,right} on left.key=right.key
| ...

File Examples

Source structured data from a local file

echo '{greeting:"hello world!"}' > hello.sup
super -s -c 'from hello.sup | values greeting'

=>

"hello world!"

Source data from a local file, but in “line” format

super -s -c 'from hello.sup (format line)'

=>

"{greeting:\"hello world!\"}"

HTTP Example

Source data from a URL

super -s -c 'from https://raw.githubusercontent.com/brimdata/super/main/package.json
       | values name'

=>

"super"

Database Examples

The remaining examples below assume the existence of the SuperDB database created and populated by the following commands:

export SUPER_DB=example
super db -q init
super db -q create -orderby flip:desc coinflips
echo '{flip:1,result:"heads"} {flip:2,result:"tails"}' |
  super db load -q -use coinflips -
super db branch -q -use coinflips trial
echo '{flip:3,result:"heads"}' | super db load -q -use coinflips@trial -
super db -q create numbers
echo '{number:1,word:"one"} {number:2,word:"two"} {number:3,word:"three"}' |
  super db load -q -use numbers -
super db -f text -c '
  from :branches
  | values pool.name + "@" + branch.name
  | sort'

The database then contains the two pools and three branches:

coinflips@main
coinflips@trial
numbers@main

The following file hello.sup is also used.

{greeting:"hello world!"}

Source data from the main branch of a pool

super db -db example -s -c 'from coinflips'

=>

{flip:2,result:"tails"}
{flip:1,result:"heads"}

Source data from a specific branch of a pool

super db -db example -s -c 'from coinflips@trial'

=>

{flip:3,result:"heads"}
{flip:2,result:"tails"}
{flip:1,result:"heads"}

Count the number of values in the main branch of all pools

super db -db example -f text -c 'from * | count()'

=>

5

Join the data from multiple pools

super db -db example -s -c '
  from coinflips
  | join ( from numbers ) on left.flip=right.number
  | values {...left, word:right.word}
  | sort'

=>

{flip:1,result:"heads",word:"one"}
{flip:2,result:"tails",word:"two"}

Use pass to combine our join output with data from yet another source

super db -db example -s -c '
  from coinflips
  | join ( from numbers ) on left.flip=right.number
  | values {...left, word:right.word}
  | fork
    ( pass )
    ( from coinflips@trial
      | c:=count()
      | values f"There were {c} flips" )
  | sort this'

=>

"There were 3 flips"
{flip:1,result:"heads",word:"one"}
{flip:2,result:"tails",word:"two"}

Expression Example

Read from dynamically defined files and add a column

echo '{a:1}{a:2}' > a.sup
echo '{b:3}{b:4}' > b.sup
echo '"a.sup" "b.sup"' | super -s -c "from eval(this) | c:=coalesce(a,b)+1" -

=>

{a:1,c:2}
{a:2,c:3}
{b:3,c:4}
{b:4,c:5}

fuse

Operator

  fuse — coerce all input values into a fused type

Synopsis

fuse

Description

The fuse operator computes a fused type over all of its input then casts all values in the input to the fused type.

This is logically equivalent to:

from input | values cast(this, (from input | aggregate fuse(this)))

Because all values of the input must be read to compute the fused type, fuse may spill its input to disk when memory limits are exceeded.

Note

Spilling is not yet implemented for the vectorized runtime.

Examples

Fuse two records

# spq
fuse
# input
{a:1}
{b:2}
# expected output
{a:1,b:null::int64}
{a:null::int64,b:2}

Fuse records with type variation

# spq
fuse
# input
{a:1}
{a:"foo"}
# expected output
{a:1::(int64|string)}
{a:"foo"::(int64|string)}

Fuse records with complex type variation

# spq
fuse
# input
{a:[1,2]}
{a:["foo","bar"],b:10.0.0.1}
# expected output
{a:[1,2]::[int64|string],b:null::ip}
{a:["foo","bar"]::[int64|string],b:10.0.0.1}

head

Operator

  head — copy leading values of input sequence

Synopsis

head [ <const-expr> ]
limit [ <const-expr> ]

Description

The head operator copies the first N values from its input to its output and ends the sequence thereafter. N is given by <const-expr>, a compile-time constant expression that evaluates to a positive integer. If <const-expr> is not provided, the value of N defaults to 1.

For compatibility with other pipe SQL dialects, limit is an alias for the head operator.

Examples

Grab first two values of arbitrary sequence

# spq
head 2
# input
1
"foo"
[1,2,3]
# expected output
1
"foo"

Grab first two values of arbitrary sequence, using a different representation of two

# spq
const ONE = 1
limit ONE+1
# input
1
"foo"
[1,2,3]
# expected output
1
"foo"

Grab the first record of a record sequence

# spq
head
# input
{a:"hello"}
{a:"world"}
# expected output
{a:"hello"}

join

Operator

  join — combine data from two inputs using a join predicate

Synopsis

<left-input>
| [anti|inner|left|right] join (
  <right-input>
) [as { <left-name>,<right-name> }] [on <predicate> | using ( <field> )]

( <left-input> )
( <right-input> )
| [anti|inner|left|right] join [as { <left-name>,<right-name> }] [on <predicate> | using ( <field> )]

<left-input> cross join ( <right-input> ) [as { <left-name>,<right-name> }]

( <left-input> )
( <right-input> )
| cross join [as { <left-name>,<right-name> }]

Description

The join operator combines values from two inputs according to the Boolean-valued <predicate> into two-field records, one field for each side of the join.

Logically, a cross product of all values is formed by taking each value L from <left-input> and forming records with all of the values R from the <right-input> of the form {<left-name>:L,<right-name>:R}. The result of the join is the set of all such records that satisfy <predicate>.

A using clause may be specified instead of an on clause and is equivalent to an equi-join predicate of the form:

<left-name>.<field> = <right-name>.<field>

<field> must be an l-value, i.e., an expression composed of dot operators and index operators.

If the as clause is omitted, then <left-name> defaults to “left” and <right-name> defaults to “right”.

For a cross join, neither an on clause or a using clause may be present and the condition is presumed true for all values so that the entire cross product is produced.

The output order of the joined values is undefined.

The available join types are:

• inner - as described above
• left - the inner join plus a set of single-field records of the form {<left-name>:L} for each value L in <left-input> absent from the inner join
• right - the inner join plus a set of single-field records of the form {<right-name>:R} for each value R in <right-input> absent from the inner join
• anti - the set of records of the form {<left-name>:L} for which there is no value R in <right-input> where the combined record {<left-name>:L,<right-name>:R} satisfies <predicate>
• cross - the entire cross product is computed

As compared to SQL relational scoping, which utilizes table aliases and column aliases within nested scopes, the pipeline join operator uses pipe scoping to join data. Here, all data is combined into joined records that can be operated upon like any other record without complex scoping logic.

If relational scoping is desired, a SQL JOIN clause can be used instead.

Examples

Join some numbers

# spq
join (values 1,3) on left=right | sort
# input
1
2
3
# expected output
{left:1,right:1}
{left:3,right:3}

Join some records with scalar keys

# spq
join (
    values "foo","baz"
  ) as {recs,key} on key=recs.key
| values recs.value
| sort
# input
{key:"foo",value:1}
{key:"bar",value:2}
{key:"baz",value:3}
# expected output
1
3

Join some records via a using clause

# spq
join (
  values {num:1,word:'one'},{num:2,word:'two'}
) using (num)
| {word:right.word, parity:left.parity}
# input
{num:1, parity:"odd"}
{num:2, parity:"even"}
# expected output
{word:"one",parity:"odd"}
{word:"two",parity:"even"}

Anti-join some numbers

# spq
anti join (values 1,3) on left=right | sort
# input
1
2
3
# expected output
{left:2}

Cross-product join

# spq
cross join (values 4,5) as {a,b} | sort
# input
1
2
3
# expected output
{a:1,b:4}
{a:1,b:5}
{a:2,b:4}
{a:2,b:5}
{a:3,b:4}
{a:3,b:5}

load

Operator

  load — add and commit data to a pool

Synopsis

load <pool>[@<branch>] [ ( [author <author>] [message <message>] [meta <meta>] ) ]

Note

The load operator is exclusively for working with a database and is not available when running super detached from a database.

Description

The load operator populates the specified <pool> with the values it receives as input. Much like how super db load is used at the command line to populate a pool with data from files, streams, and URIs, the load operator is used to save query results from your SuperSQL query to a pool in the same database. <pool> is a string indicating the name or ID of the destination pool. If the optional @<branch> string is included then the data will be committed to an existing branch of that name, otherwise the main branch is assumed. The author, message, and meta strings may also be provided to further describe the committed data, similar to the same super db load options.

Input Data

Examples below assume the existence of a database created and populated by the following commands:

export SUPER_DB=example
super db -q init
super db -q create -orderby flip:asc coinflips
super db branch -q -use coinflips onlytails
echo '{flip:1,result:"heads"} {flip:2,result:"tails"}' |
  super db load -q -use coinflips -
super db -q create -orderby flip:asc bigflips
super db -f text -c '
  from :branches
  | values pool.name + "@" + branch.name
  | sort'

The database then contains the two pools:

bigflips@main
coinflips@main
coinflips@onlytails

Examples

Modify some values, load them into the main branch of our empty bigflips pool, and see what was loaded

super db -db example -c '
  from coinflips
  | result:=upper(result)
  | load bigflips
' > /dev/null

super db -db example -s -c 'from bigflips'

=>

{flip:1,result:"HEADS"}
{flip:2,result:"TAILS"}

Add a filtered subset of records to our onlytails branch, while also adding metadata

super db -db example -c '
  from coinflips
  | result=="tails"
  | load coinflips@onlytails (
      author "Steve"
      message "A subset"
      meta "\"Additional metadata\""
    )
' > /dev/null

super db -db example -s -c 'from coinflips@onlytails'

=>

{flip:2,result:"tails"}

pass

Operator

  pass — copy input values to output

Synopsis

pass

Description

The pass operator outputs a copy of each input value. It is typically used with operators that handle multiple branches of the pipeline such as fork and join.

Examples

Copy input to output

# spq
pass
# input
1
2
3
# expected output
1
2
3

Copy each input value to three parallel pipeline branches and leave the values unmodified on one of them

# spq
fork
  ( pass )
  ( upper(this) )
  ( lower(this) )
| sort
# input
"HeLlo, WoRlD!"
# expected output
"HELLO, WORLD!"
"HeLlo, WoRlD!"
"hello, world!"

put

Operator

  put — add or modify fields of records

Synopsis

[put] <assignment> [, <assignment> ...]

where <assignment> is a field assignment having the form:

[ <field> := ] <expr>

Description

The put operator modifies its input with one or more field assignments.

Each expression <expr> is evaluated based on the input value and the result is either assigned to a new field of the input record if it does not exist, or the existing field is modified in its original location with the result.

New fields are appended in left-to-right order to the right of existing record fields while modified fields are mutated in place.

If multiple fields are written in a single put, all the new field values are computed first and then they are all written simultaneously. As a result, a computed value cannot be referenced in another expression. If you need to re-use a computed result, this can be done by chaining multiple put operators.

The put keyword is optional since it can be used as a shortcut. When used as a shortcut, the <field>:= portion of <assignment> is not optional.

Each left-hand side <field> term must be a field reference expressed as a dotted path or sequence of constant index operations on this, e.g., a.b.

Each right-hand side <expr> can be any expression.

For any input value that is not a record, a structured error is emitted having the form:

error({message:"put: not a record",on:<value>})

where <value> is the offending input value.

Note that when the field references are all top level, put is a special case of values with a record expression using a spread operator of the form:

values {...this, <field>:<expr> [, <field>:<expr>...]}

Examples

A simple put

# spq
put c:=3
# input
{a:1,b:2}
# expected output
{a:1,b:2,c:3}

The put keyword may be omitted

# spq
c:=3
# input
{a:1,b:2}
# expected output
{a:1,b:2,c:3}

A put operation can also be done with a record spread

# spq
values {...this, c:3}
# input
{a:1,b:2}
# expected output
{a:1,b:2,c:3}

Missing fields show up as missing errors

# spq
put d:=e
# input
{a:1,b:2,c:3}
# expected output
{a:1,b:2,c:3,d:error("missing")}

Non-record input values generate errors

# spq
b:=2
# input
{a:1}
1
# expected output
{a:1,b:2}
error({message:"put: not a record",on:1})

rename

Operator

  rename — change the name of record fields

Synopsis

rename <newfield>:=<oldfield> [, <newfield>:=<oldfield> ...]

Description

The rename operator changes the names of one or more fields in the input records from the right-hand side name to the left-hand side name for each assignment listed. When <oldfield> references a field that does not exist, there is no effect and the input is copied to the output.

Non-record input values are copied to the output without modification.

Each <field> must be a field reference as a dotted path and the old name and new name must refer to the same record in the case of nested records. That is, the dotted path prefix before the final field name must be the same on the left- and right-hand sides. To perform more sophisticated renaming of fields, you can use cut, put or record expressions.

If a rename operation conflicts with an existing field name, then the offending record is wrapped in a structured error along with an error message and the error is emitted.

Examples

A simple rename

# spq
rename c:=b
# input
{a:1,b:2}
# expected output
{a:1,c:2}

Nested rename

# spq
rename r.a:=r.b
# input
{a:1,r:{b:2,c:3}}
# expected output
{a:1,r:{a:2,c:3}}

Trying to mutate records with rename produces a compile-time error

# spq
rename w:=r.b
# input
{a:1,r:{b:2,c:3}}
# expected output
left-hand side and right-hand side must have the same depth (w vs r.b) at line 1, column 8:
rename w:=r.b
       ~~~~~~

Record literals can be used instead of rename for mutation

# spq
values {a,r:{c:r.c},w:r.b}
# input
{a:1,r:{b:2,c:3}}
# expected output
{a:1,r:{c:3},w:2}

Alternatively, mutations can be more generic and use drop

# spq
values {a,r,w:r.b} | drop r.b
# input
{a:1,r:{b:2,c:3}}
# expected output
{a:1,r:{c:3},w:2}

Duplicate fields create structured errors

# spq
rename a:=b
# input
{b:1}
{a:1,b:1}
{c:1}
# expected output
{a:1}
error({message:"rename: duplicate field: \"a\"",on:{a:1,b:1}})
{c:1}

shapes

Operator

  shapes — aggregate sample values by type

Synopsis

shapes [ <expr> ]

Description

The shapes operator aggregates the values computed by <expr> by type and produces an arbitrary sample value for each unique type in the input. It ignores null values and errors.

shapes is a shorthand for

where <expr> is not null
| aggregate sample:=any(<expr>) by typeof(this)
| values sample

If <expr> is not present, then this is presumed.

Examples

# spq
shapes | sort
# input
1
2
3
"foo"
"bar"
null
error(1)
# expected output
1
"foo"

# spq
shapes a | sort
# input
{a:1}
{b:2}
{a:"foo"}
# expected output
1
"foo"

search

Operator

  search — select values based on a search expression

Synopsis

search <sexpr>
? <sexpr>

Description

The search operator provides a traditional keyword experience to SuperSQL along the lines of web search, email search, or log search.

A search operation filters its input by applying the search expression <sexpr> to each input value and emitting all values that match.

The search keyword can be abbreviated as ?.

Search Expressions

The search expression syntax is unique to the search operator and provides a hybrid syntax between keyword search and boolean expressions.

A search expression is a Boolean combination of search terms, where a search term is one of:

• a regular expression wrapped in / instead of quotes,
• a glob as described below,
• a textual keyword,
• any literal of a primitive type, or
• any expression predicate.

Regular Expression

A search term may be a regular expression.

To create a regular expression search term, the expression text is prefixed and suffixed with a /. This distinguishes a regular expression from a string literal search, e.g.,

/foo|bar/

searches for the string "foo" or "bar" inside of any string entity while

"foo|bar"

searches for the string "foo|bar".

Glob

A search term may be a glob.

Globs are distinguished from keywords by the presence of any wildcard * character. To search for a string containing such a character, use a string literal instead of a keyword or escape the character as \* in a keyword.

For example,

? foo*baz*.com

Searches for any string that begins with foo, has the string baz in it, and ends with .com.

Note that a glob may look like multiplication but context disambiguates these conditions, e.g.,

a*b

is a glob match for any matching string value in the input, but

a*b==c

is a Boolean comparison between the product a*b and c.

Keyword

Keywords and string literals are equivalent search terms so it is often easier to quote a string search term instead of using escapes in a keyword. Keywords are useful in interactive workflows where searches can be issued and modified quickly without having to type matching quotes.

Keyword search has the look and feel of Web search or email search.

Valid keyword characters include a through z, A through Z, any valid string escape sequence (along with escapes for *, =, +, -), and the unescaped characters:

_ . : / % # @ ~

A keyword must begin with one of these characters then may be followed by any of these characters or digits 0 through 9.

A keyword search is equivalent to

grep(<keyword>, this)

where <keyword> is the quoted string-literal of the unquoted string. For example,

search foo

is equivalent to

where grep("foo", this)

Note that the shorthand ? may be used in lieu of the “search” keyword. For example, the simplest SuperSQL query is perhaps a single keyword search, e.g.,

? foo

As above, this query searches the implied input for values that contain the string “foo”.

Literal

Search terms representing non-string values search for both an exact match for the given value as well as a string search for the term exactly as it appears as typed. Such values include:

• integers,
• floating point numbers,
• time values,
• durations,
• IP addresses,
• networks,
• bytes values, and
• type values.

Search terms representing literal strings behave as a keyword search of the same text.

A search for a value <value> represented as the string <string> is equivalent to

<value> in this or grep(<string>, this)

For example,

search 123 and 10.0.0.1

which can be abbreviated

? 123 10.0.0.1

is equivalent to

where (123 in this or grep("123", this))
  and (10.0.0.1 in this or grep("10.0.0.1", this))

Complex values are not supported as search terms but may be queried with the in operator, e.g.,

{s:"foo"} in this

Expression Predicate

Any Boolean-valued function like is, has, grep, etc. and any comparison expression may be used as a search term and mixed into a search expression.

For example,

? is(this, <foo>) has(bar) baz x==y+z timestamp > 2018-03-24T17:17:55Z

is a valid search expression but

? /foo.*/ x+1

is not.

Boolean Logic

Search terms may be combined into boolean expressions using logical operators and, or, not, and !. and may be elided; i.e., concatenation of search terms is a logical and. not (and its equivalent !) has highest precedence and and has precedence over or. Parentheses may be used to override natural precedence.

Note that the concatenation form of and is not valid in standard expressions and is available only in search expressions.

For example,

? not foo bar or baz

means

((not grep("foo", this)) and grep("bar", this)) or grep("baz", this)

while

? foo (bar or baz)

means

grep("foo", this) and (grep("bar", this) or grep("baz", this))

Examples

A simple keyword search for “world”

# spq
search world
# input
"hello, world"
"say hello"
"goodbye, world"
# expected output
"hello, world"
"goodbye, world"

Search can utilize arithmetic comparisons

# spq
search this >= 2
# input
1
2
3
# expected output
2
3

The “search” keyword can be abbreviated as “?”

# spq
? 2 or 3
# input
1
2
3
# expected output
2
3

A search with Boolean logic

# spq
search this >= 2 AND this <= 2
# input
1
2
3
# expected output
2

The AND operator may be omitted through predicate concatenation

# spq
search this >= 2 this <= 2
# input
1
2
3
# expected output
2

Concatenation for keyword search

# spq
? foo bar
# input
"foo"
"foo bar"
"foo baz bar"
"baz"
# expected output
"foo bar"
"foo baz bar"

Search expressions match fields names too

# spq
? foo
# input
{foo:1}
{bar:2}
{foo:3}
# expected output
{foo:1}
{foo:3}

Boolean functions may be called

# spq
search is(this, <int64>)
# input
1
"foo"
10.0.0.1
# expected output
1

Boolean functions with Boolean logic

# spq
search is(this, <int64>) or is(this, <ip>)
# input
1
"foo"
10.0.0.1
# expected output
1
10.0.0.1

Search with regular expressions

# spq
? /(foo|bar)/
# input
"foo"
{s:"bar"}
{s:"baz"}
{foo:1}
# expected output
"foo"
{s:"bar"}
{foo:1}

A prefix match using a glob

# spq
? b*
# input
"foo"
{s:"bar"}
{s:"baz"}
{foo:1}
# expected output
{s:"bar"}
{s:"baz"}

A suffix match using a glob

# spq
? *z
# input
"foo"
{s:"bar"}
{s:"baz"}
{foo:1}
# expected output
{s:"baz"}

A glob with stars on both sides is like a string search

# spq
? *a*
# input
"foo"
{s:"bar"}
{s:"baz"}
{a:1}
# expected output
{s:"bar"}
{s:"baz"}
{a:1}

skip

Operator

  skip — skip leading values of input sequence

Synopsis

skip <const-expr>

Description

The skip operator skips the first N values from its input. N is given by <const-expr>, a compile-time constant expression that evaluates to a positive integer.

Examples

Skip the first two values of an arbitrary sequence

# spq
skip 2
# input
1
"foo"
[1,2,3]
# expected output
[1,2,3]

sort

Operator

  sort — sort values

Synopsis

sort [-r] [<expr> [asc|desc] [nulls {first|last}] [, <expr> [asc|desc] [nulls {first|last}] ...]]
order by [-r] [<expr> [asc|desc] [nulls {first|last}] [, <expr> [asc|desc] [nulls {first|last}] ...]]

Description

The sort operator sorts its input by reading all values until the end of input, sorting the values according to the provided sort expression(s), and emitting the values in the sorted order.

The sort operator can also be invoked as order by.

The sort expressions act as primary key, secondary key, and so forth. By default, the sort order is ascending, from lowest value to highest. If desc is specified in a sort expression, the sort order for that key is descending.

SuperSQL follows the SQL convention that, by default, null values appear last in either case of ascending or descending sort. This can be overridden by specifying nulls first in a sort expression.

If no sort expression is provided, a sort key is guessed based on heuristics applied to the values present. The heuristic examines the first input record and finds the first field in left-to-right order that is an integer, or if no integer field is found, the first field that is floating point. If no such numeric field is found, sort finds the first field in left-to-right order that is not of the time data type. Note that there are some cases (such as the output of a grouped aggregation performed on heterogeneous data) where the first input record to sort may vary even when the same query is executed repeatedly against the same data. If you require a query to show deterministic output on repeated execution, explicit sort expressions must be provided.

If -r is specified, the sort order for each key is reversed without altering the position of nulls. For clarity when sorting by named fields, specifying desc is recommended instead of -r, particularly when multiple sort expressions are present. However, sort -r provides a shorthand if the heuristics described above suffice but reversed output is desired.

If not all data fits in memory, values are spilled to temporary storage and sorted with an external merge sort.

Note

Spilling is not yet implemented for the vectorized runtime.

SuperSQL’s sort is stable such that values with identical sort keys always have the same relative order in the output as they had in the input, such as provided by the -s option in Unix’s “sort” command-line utility.

During sorting, values are compared via byte order. Between values of type string, this is equivalent to C/POSIX collation as found in other SQL databases such as Postgres.

Note that a total order is defined over the space of all values even between values of different types so sort order is always well-defined even when comparing heterogeneously typed values.

Examples

A simple sort with a null

# spq
sort this
# input
2
null
1
3
# expected output
1
2
3
null

With no sort expression, sort will sort by this for non-records

# spq
sort
# input
2
null
1
3
# expected output
1
2
3
null

The “nulls last” default may be overridden

# spq
sort this nulls first
# input
2
null
1
3
# expected output
null
1
2
3

With no sort expression, sort’s heuristics will find a numeric key

# spq
sort
# input
{s:"bar",k:2}
{s:"bar",k:3}
{s:"foo",k:1}
# expected output
{s:"foo",k:1}
{s:"bar",k:2}
{s:"bar",k:3}

It’s best practice to provide the sort key

# spq
sort k
# input
{s:"bar",k:2}
{s:"bar",k:3}
{s:"foo",k:1}
# expected output
{s:"foo",k:1}
{s:"bar",k:2}
{s:"bar",k:3}

Sort with a secondary key

# spq
sort k,s
# input
{s:"bar",k:2}
{s:"bar",k:3}
{s:"foo",k:2}
# expected output
{s:"bar",k:2}
{s:"foo",k:2}
{s:"bar",k:3}

Sort by secondary key in reverse order when the primary keys are identical

# spq
sort k,s desc
# input
{s:"bar",k:2}
{s:"bar",k:3}
{s:"foo",k:2}
# expected output
{s:"foo",k:2}
{s:"bar",k:2}
{s:"bar",k:3}

Sort with a numeric expression

# spq
sort x+y
# input
{s:"sum 2",x:2,y:0}
{s:"sum 3",x:1,y:2}
{s:"sum 0",x:-1,y:-1}
# expected output
{s:"sum 0",x:-1,y:-1}
{s:"sum 2",x:2,y:0}
{s:"sum 3",x:1,y:2}

Case sensitivity affects sorting “lowest value to highest” in string values

# spq
sort
# input
{word:"hello"}
{word:"Hi"}
{word:"WORLD"}
# expected output
{word:"Hi"}
{word:"WORLD"}
{word:"hello"}

Case-insensitive sort by using a string expression

# spq
sort lower(word)
# input
{word:"hello"}
{word:"Hi"}
{word:"WORLD"}
# expected output
{word:"hello"}
{word:"Hi"}
{word:"WORLD"}

Shorthand to reverse the sort order for each key

# spq
sort -r
# input
2
null
1
3
# expected output
3
2
1
null

switch

Operator

  switch — route values based on cases

Synopsis

switch <expr>
  case <const> ( <branch> )
  case <const> ( <branch> )
  ...
  [ default ( <branch> ) ]

switch
  case <bool-expr> ( <branch> )
  case <bool-expr> ( <branch> )
  ...
  [ default ( <branch> ) ]

Description

The switch operator routes input values to parallel pipe branches based on case matching.

In this first form, the expression <expr> is evaluated for each input value and its result is compared with all of the case values, which must be distinct, compile-time constant expressions. The value is propagated to the matching branch.

In the second form, each case is evaluated for each input value in the order that the cases appear. The first case to match causes the input value to propagate to the corresponding branch. Even if later cases match, only the first branch receives the value.

In either form, if no case matches, but a default is present, then the value is routed to the default branch. Otherwise, the value is dropped.

Only one default case is allowed and it may appear anywhere in the list of cases; where it appears does not influence the result.

The output of a switch consists of multiple branches that must be merged. If the downstream operator expects a single input, then the output branches are combined without preserving order. Order may be reestablished by applying a sort at the merge point.

Examples

Split input into evens and odds

# spq
switch
  case this%2==0 ( {even:this} )
  case this%2==1 ( {odd:this} )
| sort odd,even
# input
1
2
3
4
# expected output
{odd:1}
{odd:3}
{even:2}
{even:4}

Switch on this with a constant case

# spq
switch this
  case 1 ( values "1!" )
  default ( values this::string )
| sort
# input
1
2
3
4
# expected output
"1!"
"2"
"3"
"4"

tail

Operator

  tail — copy trailing values of input sequence

Synopsis

tail [ <const-expr> ]

Description

The tail operator copies the last N values from its input to its output and ends the sequence thereafter. N is given by <const-expr>, a compile-time constant expression that evaluates to a positive integer. If <const-expr> is not provided, the value of N defaults to 1.

Examples

Grab last two values of arbitrary sequence

# spq
tail 2
# input
1
"foo"
[1,2,3]
# expected output
"foo"
[1,2,3]

Grab last two values of arbitrary sequence, using a different representation of two

# spq
const ONE = 1
tail ONE+1
# input
1
"foo"
[1,2,3]
# expected output
"foo"
[1,2,3]

Grab the last record of a record sequence

# spq
tail
# input
{a:"hello"}
{b:"world"}
# expected output
{b:"world"}

top

Operator

  top — output the first N sorted values of input sequence

Synopsis

top [-r] [<const-expr> [<expr> [asc|desc] [nulls {first|last}] [, <expr> [asc|desc] [nulls {first|last}] ...]]]

Description

The top operator returns the first N values from a sequence sorted according to the provided sort expressions. N is given by <const-expr>, a compile-time constant expression that evaluates to a positive integer. If <const-expr> is not provided, N defaults to 1.

The sort expressions <expr> and their parameters behave as they do for sort. If no sort expression is provided, a sort key is selected using the same heuristic as sort.

top is functionally similar to sort but is less resource intensive because only the first N values are stored in memory (i.e., subsequent values are discarded).

Examples

Grab the smallest two values from a sequence of integers

# spq
top 2
# input
1
5
3
9
23
7
# expected output
1
3

Find the two names most frequently referenced in a sequence of records

# spq
count() by name | top -r 2 count
# input
{name:"joe", age:22}
{name:"bob", age:37}
{name:"liz", age:25}
{name:"bob", age:18}
{name:"liz", age:34}
{name:"zoe", age:55}
{name:"ray", age:44}
{name:"sue", age:41}
{name:"liz", age:60}
# expected output
{name:"liz",count:3::uint64}
{name:"bob",count:2::uint64}

uniq

Operator

  uniq — deduplicate adjacent values

Synopsis

uniq [-c]

Description

Inspired by the traditional Unix shell command of the same name, the uniq operator copies its input to its output but removes duplicate values that are adjacent to one another.

This operator is most often used with cut and sort to find and eliminate duplicate values.

When run with the -c option, each value is output as a record with the type signature {value:any,count:uint64}, where the value field contains the unique value and the count field indicates the number of consecutive duplicates that occurred in the input for that output value.

Examples

Simple deduplication

# spq
uniq
# input
1
2
2
3
# expected output
1
2
3

Simple deduplication with count of duplicate values

# spq
uniq -c
# input
1
2
2
3
# expected output
{value:1,count:1::uint64}
{value:2,count:2::uint64}
{value:3,count:1::uint64}

Use sort to deduplicate non-adjacent values

# spq
sort | uniq
# input
"hello"
"world"
"goodbye"
"world"
"hello"
"again"
# expected output
"again"
"goodbye"
"hello"
"world"

Complex values must match fully to be considered duplicate (e.g., every field/value pair in adjacent records)

# spq
uniq
# input
{ts:2024-09-10T21:12:33Z, action:"start"}
{ts:2024-09-10T21:12:34Z, action:"running"}
{ts:2024-09-10T21:12:34Z, action:"running"}
{ts:2024-09-10T21:12:35Z, action:"running"}
{ts:2024-09-10T21:12:36Z, action:"stop"}
# expected output
{ts:2024-09-10T21:12:33Z,action:"start"}
{ts:2024-09-10T21:12:34Z,action:"running"}
{ts:2024-09-10T21:12:35Z,action:"running"}
{ts:2024-09-10T21:12:36Z,action:"stop"}

unnest

Operator

  unnest — expand nested array as values optionally into a subquery

Synopsis

unnest <expr> [ into ( <query> ) ]

Description

The unnest operator transforms the given expression <expr> into a new ordered sequence of derived values.

When the optional argument <query> is present, each unnested sequence of values is processed as a unit by that subquery, which is shorthand for this pattern

unnest [unnest <expr> | <query>]

where the right-hand unnest is an array subquery.

For example,

values [1,2],[3] | unnest this | sum(this)

produces

6

but

values [1,2],[3] | unnest this into (sum(this))

produces

3
3

If <expr> is an array, then the elements of that array form the derived sequence.

If <expr> is a record, it must have two fields of the form:

{<first>: <any>, <second>:<array>}

where <first> and <second> are arbitrary field names, <any> is any SuperSQL value, and <array> is an array value. In this case, the derived sequence has the form:

{<first>: <any>, <second>:<elem0>}
{<first>: <any>, <second>:<elem1>}
...

where the first field is copied to each derived value and the second field is the unnested elements of the array elem0, elem1, etc.

To explode the fields of records or the key-value pairs of maps, use the flatten function, which produces an array that can be unnested.

For example, if this is a record, it can be unnested with unnest flatten(this).

Note

Support for map types in flatten is not yet implemented.

Errors

If a value encountered by unnest does not have either of the forms defined above, then an error results as follows:

error({message:"unnest: encountered non-array value",on:<value>})

where <value> is the offending value.

When a record value is encountered without the proper form, then the error is:

error({message:"unnest: encountered record without two fields",on:<value>})

or

error({message:"unnest: encountered record without an array/set type for second field",on:<value>})

Examples

unnest unrolls the elements of an array

# spq
unnest [1,2,"foo"]
# input

# expected output
1
2
"foo"

The unnest clause is evaluated once per each input value

# spq
unnest [1,2]
# input
null
null
# expected output
1
2
1
2

Unnest traversing an array inside a record

# spq
unnest a
# input
{a:[1,2,3]}
# expected output
1
2
3

Filter the unnested values

# spq
unnest a | this % 2 == 0
# input
{a:[6,5,4]}
{a:[3,2,1]}
# expected output
6
4
2

Aggregate the unnested values

# spq
unnest a | sum(this)
# input
{a:[1,2]}
{a:[3,4,5]}
# expected output
15

Aggregate the unnested values in a subquery

# spq
unnest a into ( sum(this) )
# input
{a:[1,2]}
{a:[3,4,5]}
# expected output
3
12

Access an outer value in a subquery

# spq
unnest {s,a} into ( sum(a) by s )
# input
{a:[1,2],s:"foo"}
{a:[3,4,5],s:"bar"}
# expected output
{s:"foo",sum:3}
{s:"bar",sum:12}

Unnested the elements of a record by flattening it

# spq
unnest {s,f:flatten(r)} into ( values {s,key:f.key[0],val:f.value} )
# input
{s:"foo",r:{a:1,b:2}}
{s:"bar",r:{a:3,b:4}}
# expected output
{s:"foo",key:"a",val:1}
{s:"foo",key:"b",val:2}
{s:"bar",key:"a",val:3}
{s:"bar",key:"b",val:4}

values

Operator

  values — emit values from expressions

Synopsis

[values] <expr> [, <expr>...]

Description

The values operator produces output values by evaluating one or more comma-separated expressions on each input value and sending each result to the output in left-to-right order. Each <expr> may be any valid expression.

The input order convolved with left-to-right evaluation order is preserved at the output.

The values operator name is optional since it can be used as a shortcut. When used as a shortcut, only one expression may be present.

The values abstraction is also available as the SQL VALUES clause, where the tuples that comprise this form must all adhere to a common type signature.

The pipe form of values here is differentiated from the SQL form by the absence of parenthesized expressions in the comma-separated list of expressions, i.e., the expressions in a comma-separated list never require top-level parentheses and the resulting values need not conform to a common type.

The values operator is a go to tool in SuperSQL queries as it allows the flexible creation of arbitrary values from its inputs while the SQL VALUES clause is a go to building block for creating constant tables to insert into or operate upon a database. That said, the SQL VALUES clause can also be comprised of dynamic expressions though it is less often used in this fashion. Nonetheless, this motivated the naming of the more general SuperSQL values operator.

For example, this query uses SQL VALUES to create a static table called points then operate upon each row of points using expressions embodied in dynamic VALUES subqueries placed in a lateral join as follows:

WITH points(x,y) AS (
  VALUES (2,1),(4,2),(6,3)
)
SELECT vals
FROM points CROSS JOIN LATERAL (VALUES (x+y), (x-y)) t(vals)

which produces

3
1
6
2
9
3

Using the values pipe operator, this can be written simply as

values {x:2,y:1},{x:4,y:2},{x:6,y:3}
| values x+y, x-y

Examples

Hello, world

# spq
values "hello, world"
# input

# expected output
"hello, world"

Values evaluates each expression for every input value

# spq
values 1,2
# input
null
null
null
# expected output
1
2
1
2
1
2

Values typically operates on its input

# spq
values this*2+1
# input
1
2
3
# expected output
3
5
7

Values is often used to transform records

# spq
values [a,b],[b,a] | collect(this)
# input
{a:1,b:2}
{a:3,b:4}
# expected output
[[1,2],[2,1],[3,4],[4,3]]

where

Operator

  where — select values based on a Boolean expression

Synopsis

[where] <expr>

Description

The where operator filters its input by applying a Boolean expression <expr> to each input value and dropping each value for which the expression evaluates to false or to an error.

The where keyword is optional since it is a shortcut.

When SuperSQL queries are run interactively, it is highly convenient to be able to omit the “where” keyword, but when where filters appear in query source files, it is good practice to include the optional keyword.

Examples

An arithmetic comparison

# spq
where this >= 2
# input
1
2
3
# expected output
2
3

The “where” keyword may be dropped

# spq
this >= 2
# input
1
2
3
# expected output
2
3

A filter with Boolean logic

# spq
where this >= 2 AND this <= 2
# input
1
2
3
# expected output
2

A filter with array containment logic

# spq
where this in [1,4]
# input
1
2
3
4
# expected output
1
4

A filter with inverse containment logic

# spq
where ! (this in [1,4])
# input
1
2
3
4
# expected output
2
3

Boolean functions may be called

# spq
where has(a)
# input
{a:1}
{b:"foo"}
{a:10.0.0.1,b:"bar"}
# expected output
{a:1}
{a:10.0.0.1,b:"bar"}

Boolean functions with Boolean logic

# spq
where has(a) or has(b)
# input
{a:1}
{b:"foo"}
{a:10.0.0.1,b:"bar"}
# expected output
{a:1}
{b:"foo"}
{a:10.0.0.1,b:"bar"}

SQL Clauses

SQL Operators

TODO: document all the SQL clauses

XXX explain here how a SELECT query is Pipe operator XXX figure out how to document FROM query without select as this overlaps with the pipe operator form of FROM

Identifier Scope

TODO: seciton on scoping

see issue

CTE scoping

Input References

Accessing this

FROM

SELECT

WHERE

GROUP BY

HAVING

FILTER

VALUES

ORDER

LIMIT

JOIN

WITH

UNION

INTERSECT

Functions

Functions

An invocation of a built-in function may appear in any expression. A function takes zero or more positional arguments and always produces a single output value. There are no named function parameters.

A declared function whose name conflicts with a built-in function name overrides the built-in function.

Functions are generally polymorphic and can be called with values of varying type as their arguments. When type errors occur, functions will return structured errors reflecting the error.

Note

Static type checking of function arguments and return values is not yet implemented in SuperSQL but will be supported in a future version.

Throughout the function documentation, expected parameter types and the return type are indicated with type signatures having the form

<name> ( [ <formal> : <type> ] [ , <formal> : <type> ] ) -> <type>

where <name> is the function name, <formal> is an identifier representing the formal name of a function parameter, and <type> is either the name of an actual type or a documentary pseudo-type indicating categories defined as follows:

• any - any SuperSQL data type
• float - any floating point type
• int - any signed or unsigned integer type
• number - any numeric type
• record - any record type

Generics

Generics

coalesce

Function

  coalesce — return first value that is not null, a “missing” error, or a “quiet” error

Synopsis

coalesce(val: any [, ... val: any]) -> any

Description

The coalesce function returns the first of its arguments that is not null, error("missing"), or error("quiet"). It returns null if all its arguments are null, error("missing"), or error("quiet").

Examples

# spq
values coalesce(null, error("missing"), error("quiet"), this)
# input
1
# expected output
1

# spq
values coalesce(null, error("missing"), this)
# input
error("quiet")
# expected output
null

compare

Function

  compare — compare values even when types vary

Synopsis

compare(a: any, b: any [, nullsMax: bool]) -> int64

Description

The compare function returns an integer comparing two values. The result will be 0 if a is equal to b, +1 if a is greater than b, and -1 if a is less than b. compare differs from comparison expressions in that it will work for any type (e.g., compare(1, "1")).

Values are compared via byte order. Between values of type string, this is equivalent to C/POSIX collation as found in other SQL databases such as Postgres.

Note

A future version of SuperSQL will collate values polymorphically using a well-defined total order that embodies the super-structured type order.

nullsMax is an optional value (true by default) that determines whether null is treated as the minimum or maximum value.

Examples

# spq
values compare(a, b)
# input
{a:2,b:1}
{a:2,b:"1"}
# expected output
1
-1

has

Function

  has — test existence of values

Synopsis

has(val: any [, ... val: any]) -> bool

Description

The has function returns false if any of its arguments are error("missing") and otherwise returns true. has(e) is a shortcut for !missing(e).

This function is often used to determine the existence of fields in a record, e.g., has(a,b) is true when this is a record and has the fields a and b, provided their values are not error("missing").

It’s also useful in shaping messy data when applying conditional logic based on the presence of certain fields:

switch
  case has(a) ( ... )
  case has(b) ( ... )
  default ( ... )

Examples

# spq
values {yes:has(foo),no:has(bar)}
# input
{foo:10}
# expected output
{yes:true,no:false}

# spq
values {yes: has(foo[1]),no:has(foo[4])}
# input
{foo:[1,2,3]}
# expected output
{yes:true,no:false}

# spq
values {yes:has(foo.bar),no:has(foo.baz)}
# input
{foo:{bar:"value"}}
# expected output
{yes:true,no:false}

# spq
values {yes:has(foo+1),no:has(bar+1)}
# input
{foo:10}
# expected output
{yes:true,no:false}

# spq
values has(bar)
# input
1
# expected output
false

# spq
values has(x)
# input
{x:error("missing")}
# expected output
false

len

Function

  len — the type-dependent length of a value

Synopsis

len(val: array|bytes|ip|map|net|null|record|set|string|type) -> int64

Description

The len function returns the length of its argument val. The semantics of this length depend on the value’s type.

For values of each of the supported types listed below, len describes the contents of val as indicated.

For values of the type type, len describes the underlying type definition of val as indicated below.

Examples

The length of values of various types

# spq
values {this,kind:kind(this),type:typeof(this),len:len(this)}
# input
[1,2,3]
0x0102ffee
192.168.4.1
2001:0db8:85a3:0000:0000:8a2e:0370:7334
|{"APPL":145.03,"GOOG":87.07}|
192.168.4.0/24
2001:db8:abcd::/64
null
{a:1,b:2}
|["x","y","z"]|
"hello"
# expected output
{that:[1,2,3],kind:"array",type:<[int64]>,len:3}
{that:0x0102ffee,kind:"primitive",type:<bytes>,len:4}
{that:192.168.4.1,kind:"primitive",type:<ip>,len:4}
{that:2001:db8:85a3::8a2e:370:7334,kind:"primitive",type:<ip>,len:16}
{that:|{"APPL":145.03,"GOOG":87.07}|,kind:"map",type:<|{string:float64}|>,len:2}
{that:192.168.4.0/24,kind:"primitive",type:<net>,len:8}
{that:2001:db8:abcd::/64,kind:"primitive",type:<net>,len:32}
{that:null,kind:"primitive",type:<null>,len:0}
{that:{a:1,b:2},kind:"record",type:<{a:int64,b:int64}>,len:2}
{that:|["x","y","z"]|,kind:"set",type:<|[string]|>,len:3}
{that:"hello",kind:"primitive",type:<string>,len:5}

The length of various values of type type

# spq
values {this,kind:kind(this),type:typeof(this),len:len(this)}
# input
<[string]>
<[{a:int64,b:string,c:bool}]>
<enum(HEADS,TAILS)>
<error(string)>
<error({ts:time,msg:string})>
<|{string:float64}|>
<|{string:{x:int64,y:float64}}|>
<int8>
<{a:int64,b:string,c:bool}>
<|[string]|>
<|[{a:int64,b:string,c:bool}]|>
<(int64|float64|string)>
# expected output
{that:<[string]>,kind:"array",type:<type>,len:1}
{that:<[{a:int64,b:string,c:bool}]>,kind:"array",type:<type>,len:3}
{that:<enum(HEADS,TAILS)>,kind:"enum",type:<type>,len:2}
{that:<error(string)>,kind:"error",type:<type>,len:1}
{that:<error({ts:time,msg:string})>,kind:"error",type:<type>,len:2}
{that:<|{string:float64}|>,kind:"map",type:<type>,len:1}
{that:<|{string:{x:int64,y:float64}}|>,kind:"map",type:<type>,len:2}
{that:<int8>,kind:"primitive",type:<type>,len:1}
{that:<{a:int64,b:string,c:bool}>,kind:"record",type:<type>,len:3}
{that:<|[string]|>,kind:"set",type:<type>,len:1}
{that:<|[{a:int64,b:string,c:bool}]|>,kind:"set",type:<type>,len:3}
{that:<int64|float64|string>,kind:"union",type:<type>,len:3}

Unsupported values produce errors

# spq
values {this,kind:kind(this),type:typeof(this),len:len(this)}
# input
true
10m30s
error("hello")
1
2024-07-30T20:05:15.118252Z
# expected output
{that:true,kind:"primitive",type:<bool>,len:error({message:"len: bad type",on:true})}
{that:10m30s,kind:"primitive",type:<duration>,len:error({message:"len: bad type",on:10m30s})}
{that:error("hello"),kind:"error",type:<error(string)>,len:error({message:"len()",on:error("hello")})}
{that:1,kind:"primitive",type:<int64>,len:error({message:"len: bad type",on:1})}
{that:2024-07-30T20:05:15.118252Z,kind:"primitive",type:<time>,len:error({message:"len: bad type",on:2024-07-30T20:05:15.118252Z})}

map

Function

  map — apply a function to each element of an array or set

Synopsis

map(v: array|set, f: function) -> array|set|error

Description

The map function applies a single-argument function f, in the form of an existing function or a lambda expression, to every element in array or set v and returns an array or set of the results.

The function f may reference a declared function or a built-in function using the & syntax as in

&<name>

where <name> is an identifier.

Alternatively, f may be a lambda expression of the form

lambda x: <expr>

where <expr> is any expression depending only on the lambda argument.

Examples

Upper case each element of an array using & for a built-in

# spq
values map(this, &upper)
# input
["foo","bar","baz"]
# expected output
["FOO","BAR","BAZ"]

A user function to convert epoch floats to time values

# spq
fn floatToTime(x): (
  cast(x*1000000000, <time>)
)
values map(this, &floatToTime)
# input
[1697151533.41415,1697151540.716529]
# expected output
[2023-10-12T22:58:53.414149888Z,2023-10-12T22:59:00.716528896Z]

Same as above but with a lambda expression

# spq
values map(this, lambda x:cast(x*1000000000, <time>))
# input
[1697151533.41415,1697151540.716529]
# expected output
[2023-10-12T22:58:53.414149888Z,2023-10-12T22:59:00.716528896Z]

under

Function

  under — the underlying value

Synopsis

under(val: any) -> any

Description

The under function returns the value underlying the argument val:

• for unions, it returns the value as its elemental type of the union,
• for errors, it returns the value that the error wraps,
• for name-typed values, it returns the value with the named type’s underlying type,
• for type values, it removes a named type if one exists; otherwise,
• it returns val unmodified.

Examples

Unions are unwrapped

# spq
values this
# input
1::(int64|string)
"foo"::(int64|string)
# expected output
1::(int64|string)
"foo"::(int64|string)

# spq
values under(this)
# input
1::(int64|string)
"foo"::(int64|string)
# expected output
1
"foo"

Errors are unwrapped

# spq
values this
# input
error("foo")
error({err:"message"})
# expected output
error("foo")
error({err:"message"})

# spq
values under(this)
# input
error("foo")
error({err:"message"})
# expected output
"foo"
{err:"message"}

Values of named types are unwrapped

# spq
values this
# input
80::(port=uint16)
# expected output
80::(port=uint16)

# spq
values under(this)
# input
80::(port=uint16)
# expected output
80::uint16

Values that are not wrapped are unmodified

# spq
values under(this)
# input
1
"foo"
<int16>
{x:1}
# expected output
1
"foo"
<int16>
{x:1}

Errors

error

Function

  error — wrap a value as an error

Synopsis

error(val: any) -> error

Description

The error function returns an error version of any value. It wraps the value val to turn it into an error type providing a means to create structured and stacked errors.

Examples

Wrap a record as a structured error

# spq
values error({message:"bad value", value:this})
# input
{foo:"foo"}
# expected output
error({message:"bad value",value:{foo:"foo"}})

Wrap any value as an error

# spq
values error(this)
# input
1
"foo"
[1,2,3]
# expected output
error(1)
error("foo")
error([1,2,3])

Test if a value is an error and show its type “kind”

# spq
values {this,err:is_error(this),kind:kind(this)}
# input
error("exception")
"exception"
# expected output
{that:error("exception"),err:true,kind:"error"}
{that:"exception",err:false,kind:"primitive"}

Comparison of a missing error results in a missing error even if they are the same missing errors so as to not allow field comparisons of two missing fields to succeed

# spq
badfield:=x | values badfield==error("missing")
# input
{}
# expected output
error("missing")

has_error

Function

  has_error — test if a value is or contains an error

Synopsis

has_error(val: any) -> bool

Description

The has_error function returns true if its argument is or contains an error. has_error is different from is_error in that has_error recursively searches a value to determine if there is any error in a nested value.

Examples

# spq
values has_error(this)
# input
{a:{b:"foo"}}
# expected output
false

# spq
a.x := a.y + 1 | values has_error(this)
# input
{a:{b:"foo"}}
# expected output
true

is_error

Function

  is_error — test if a value is an error

Synopsis

is_error(val: any) -> bool

Description

The is_error function returns true if its argument’s type is an error. is_error(v) is shorthand for kind(v)=="error",

Examples

A simple value is not an error

# spq
values is_error(this)
# input
1
# expected output
false

An error value is an error

# spq
values is_error(this)
# input
error(1)
# expected output
true

Convert an error string into a record with an indicator and a message

# spq
values {err:is_error(this),message:under(this)}
# input
"not an error"
error("an error")
# expected output
{err:false,message:"not an error"}
{err:true,message:"an error"}

missing

Function

  missing — test for the “missing” error

Synopsis

missing(val: any) -> bool

Description

The missing function returns true if its argument is error("missing") and false otherwise.

This function is often used to test if certain fields do not appear as expected in a record, e.g., missing(a) is true either when this is not a record or when this is a record and the field a is not present in this.

It’s also useful for shaping messy data when applying conditional logic based on the absence of certain fields:

switch
  case missing(a) ( ... )
  case missing(b) ( ... )
  default ( ... )

Examples

# spq
values {yes:missing(bar),no:missing(foo)}
# input
{foo:10}
# expected output
{yes:true,no:false}

# spq
values {yes:missing(foo.baz),no:missing(foo.bar)}
# input
{foo:{bar:"value"}}
# expected output
{yes:true,no:false}

# spq
values {yes:missing(bar+1),no:missing(foo+1)}
# input
{foo:10}
# expected output
{yes:true,no:false}

# spq
values missing(bar)
# input
1
# expected output
true

# spq
values missing(x)
# input
{x:error("missing")}
# expected output
true

quiet

Function

  quiet — quiet “missing” errors

Synopsis

quiet(val: any) -> any

Description

The quiet function returns its argument val unless val is error("missing"), in which case it returns error("quiet"). Various operators and functions treat quiet errors differently than missing errors, in particular, dropping them instead of propagating them. Quiet errors are ignored by operators aggregate, cut, and values.

Examples

A quiet error in values produces no output

# spq
values quiet(this)
# input
error("missing")
# expected output

Without quiet, values produces the missing error

# spq
values this
# input
error("missing")
# expected output
error("missing")

The cut operator drops quiet errors but retains missing errors

# spq
cut b:=x+1,c:=quiet(x+1),d:=quiet(a+1)
# input
{a:1}
# expected output
{b:error("missing"),d:2}

Math

abs

Function

  abs — absolute value of a number

Synopsis

abs(n: number) -> number

Description

The abs function returns the absolute value of its argument n, which must be a numeric type.

Examples

Absolute value of various numbers

# spq
values abs(this)
# input
1
-1
0
-1.0
-1::int8
1::uint8
"foo"
# expected output
1
1
0
1.
1::int8
1::uint8
error({message:"abs: not a number",on:"foo"})

ceil

Function

  ceil — ceiling of a number

Synopsis

ceil(n: number) -> number

Description

The ceil function returns the smallest integer greater than or equal to its argument n, which must be a numeric type. The return type retains the type of the argument.

Examples

The ceiling of various numbers

# spq
values ceil(this)
# input
1.5
-1.5
1::uint8
1.5::float32
# expected output
2.
-1.
1::uint8
2.::float32

floor

Function

  floor — floor of a number

Synopsis

floor(n: number) -> number

Description

The floor function returns the greatest integer less than or equal to its argument n, which must be a numeric type. The return type retains the type of the argument.

Examples

The floor of various numbers

# spq
values floor(this)
# input
1.5
-1.5
1::uint8
1.5::float32
# expected output
1.
-2.
1::uint8
1.::float32

log

Function

  log — natural logarithm

Synopsis

log(val: number) -> float64

Description

The log function returns the natural logarithm of its argument val, which takes a numeric type. The return value is a float64 or an error. Negative values result in an error.

Examples

The logarithm of various numbers

# spq
values log(this)
# input
4
4.0
2.718
-1
# expected output
1.3862943611198906
1.3862943611198906
0.999896315728952
error({message:"log: illegal argument",on:-1})

The largest power of 10 smaller than the input

# spq
values (log(this)/log(10))::int64
# input
9
10
20
1000
1100
30000
# expected output
0
1
1
2
3
4

pow

Function

  pow — exponential function of any base

Synopsis

pow(x: number, y: number) -> float64

Description

The pow function returns the value x raised to the power of y. The return value is a float64 or an error.

Examples

# spq
values pow(this, 5)
# input
2
# expected output
32.

round

Function

  round — round a number

Synopsis

round(val: number) -> number

Description

The round function returns the number val rounded to the nearest integer value. which must be a numeric type. The return type retains the type of the argument.

Examples

# spq
values round(this)
# input
3.14
-1.5::float32
0
1
# expected output
3.
-2.::float32
0
1

sqrt

Function

  sqrt — square root of a number

Synopsis

sqrt(val: number) -> float64

Description

The sqrt function returns the square root of its argument val, which must be numeric. The return value is a float64. Negative values result in NaN.

Examples

The square root of various numbers

# spq
values sqrt(this)
# input
4
2.
1e10
-1
# expected output
2.
1.4142135623730951
100000.
NaN

Network

cidr_match

Function

  cidr_match — test if IP is in a network

Synopsis

cidr_match(network: net, val: any) -> bool

Description

The cidr_match function returns true if val contains an IP address that falls within the network given by network. When val is a complex type, the function traverses its nested structure to find any ip values. If network is not type net, then an error is returned.

Examples

Test whether values are IP addresses in a network

# spq
values cidr_match(10.0.0.0/8, this)
# input
10.1.2.129
11.1.2.129
10
"foo"
# expected output
true
false
false
false

It also works for IPs in complex values

# spq
values cidr_match(10.0.0.0/8, this)
# input
[10.1.2.129,11.1.2.129]
{a:10.0.0.1}
{a:11.0.0.1}
# expected output
true
true
false

The first argument must be a network

# spq
values cidr_match([1,2,3], this)
# input
10.0.0.1
# expected output
error({message:"cidr_match: not a net",on:[1,2,3]})