Skip to content

Logo

The following examples are features that are not present in SQL. They are not impossible to do in SQL, but just painful.

Delightql offers these features to make the developer experience a little bit more pleasant.

This list is not comprehensive. See the language reference.

PROJECT OUT

Remove columns from a relation.

users(*)
 |> -(email)

MAP COVER

Run a function over a set of columns but leave all other columns in place

users(*)
 |> $(upper:())(first_name, last_name)

RENAME COVER

Rename some columns but leave all other columns in place.

users(*)
 |> *(first_name as fname, last_name as lname)

INDEX SELECTING

Choose columns by their ordinal index. Useful for ETL and nasty tables.

users(*)
 |> ( |1| as new, |3|, |5| )

users(*) |> ( |-1| )

RANGE SELECTING

Choose columns by ordinal range.

users(*)
 |> ( |1:3| )

users(*)
 |> ( |5:| )

TREE-GROUPING

Embrace zeroth-normal form data by rolling up multiple levels of aggregation into a tree.

users(*)
  ~> {country, "users": ~> {first_name, last_name}} as users_by_country

COMMON FUNCTION EXPRESSIONS

Define functions at the top of your query and use them. Example shows functional piping too.

normalize:(age):
  age
  >> :(@ - 18)
  >> max:(0)
  >> min:(100)
categorize_age:(age):
  _:(age >= 65 -> "Senior"; age >= 18 -> "Adult"; _ -> "Minor")
profile_summary:(first, last, age):
  age >> normalize:() >> categorize_age:()
  >> :"Profile: {first} {last} - Category: {@}"
// main query
users(*)
 |> (id,
     first_name >> profile_summary:(last_name, age) as summary)

REGEX SELECTING

Select your columns based on a pattern of their name.

users(*)
 |> (/_name/)

users(*)
 |> -(/_name/)

ORDINAL REORDER

Rearrange the columns' ordinality.

users(*) |> *[first_name as 2, last_name as -1]

RAGGED UNION

UNION almost matching schemas together with null padding and matching by name.

users_2022(*) ; users_2023(*)

PIVOT

_(name, subject, score @
  "Alice", "Maths", 90;
  "Alice", "Music", 85;
  "Bob", "Maths", 75;
  "Bob", "Music", 80),
  subject in ("Maths"; "Music")
  |> %(name ~> score of subject)

MELT

products(*),
_(attribute, value @
  "price", price;
  "cost", cost)
|> (id, name, attribute, value)