Skip to content

Tree Grouping

The { } syntax creates nested JSON-style output. Inside braces, ~> nests one level of grouping under another.

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

Sibling Tree Grouping

users(*)
  ~> {
       country,
       "user_names":
          ~> {first_name, last_name},
       "statuses":
          ~> [status]
     } as users_by_country

Metadata Tree Grouping

Metadata-oriented tree grouping turns data into JSON keys. A column's distinct values become the keys of a single object rather than elements of an array. This is analagous to pivoting.

users(*) as u
  |> %(~> u.country:~> u.status:~> {u.first_name})

Tree groups are just values

The enclyphs {} or [] may be usesd in scalar or reduction position. Tree groups work with the rest of the language.

users(*)
  |> %(
       {country, "people": ~> {first_name, last_name}} as people_by_country
       ~>
       count:(*) as user_count,
       avg:(age) as avg_age
     )

Tree Destructuring

// CTE generates the tree-group
temp(*) :
  users(*), id < 4
    ~> {country, "users": ~> {first_name, last_name}} as nested

// now use two-level of deconstruction
temp(*), nested ~= ~> {country, "users": sub_users}
  , sub_users ~= ~> {first_name, last_name}
  |> -(nested)

Narrowing

// CTE generates the tree-group
users(*)
  |> %(country ~> {first_name, last_name} as people) : grouped
grouped(*) |> .people{.first_name, .last_name}

Interior Drill-Down

Unlike narrowing, interior drill-down keeps the columns of the exterior table.

users(*)
  |> %(country ~> {first_name, last_name, age} as people) : by_country
by_country(*).people(*)
  |> %(country ~> sum:(age), avg:(age))