Select, Update, Insert & Upsert

Query, mutate, and manage table data with Rayfall's declarative query syntax. All query forms compile to a fused DAG for morsel-driven parallel execution.

Select

The select form is the primary way to query tables. It supports projection, filtering, computed columns, and group-by aggregation through a single dictionary argument.

Basic Projection

Select all columns from a table (identity query):

; Create a sample table
(set t (table [sym price volume]
    (list [AAPL GOOG MSFT] [150 280 420] [500 400 900])))

; Select all rows and columns
(select {from: t})
; sym  price volume
; ---  ----- ------
; AAPL   150    500
; GOOG   280    400
; MSFT   420    900

Select specific columns with computed expressions:

(select {from: t cols: {sym: sym notional: (* price volume)}})
; sym  notional
; ---  --------
; AAPL    75000
; GOOG   112000
; MSFT   378000

Filtering with where:

The where: clause accepts any predicate expression. Predicates are pushed down through the DAG for early elimination of rows:

(select {from: t where: (> price 200)})
; sym  price volume
; ---  ----- ------
; GOOG   280    400
; MSFT   420    900

Combine multiple conditions:

(select {from: t where: (and (> price 100) (< volume 600))})
; sym  price volume
; ---  ----- ------
; AAPL   150    500
; GOOG   280    400

Group-by Aggregation

The by: clause groups rows by one or more key columns. Aggregation functions in cols: operate within each group:

(set trades (table [sym side price qty]
    (list [AAPL AAPL GOOG GOOG AAPL]
          [Buy Sell Buy Buy Buy]
          [150 152 280 282 149]
          [100 200 50 75 300])))

; Group by sym, aggregate price and qty
(select {from: trades by: {sym: sym}
         cols: {avg_price: (avg price)
                total_qty: (sum qty)
                n: (count price)}})
; sym  avg_price total_qty  n
; ---  --------- ---------  -
; AAPL     150.3       600  3
; GOOG       281       125  2

Group by multiple keys:

(select {from: trades by: {sym: sym side: side}
         cols: {total: (sum qty)}})
; sym  side total
; ---  ---- -----
; AAPL Buy    400
; AAPL Sell   200
; GOOG Buy    125

Filter + Group-by

Combine where: and by: in the same query. The filter is applied before grouping:

(select {from: trades
         where: (== side 'Buy)
         by: {sym: sym}
         cols: {buy_qty: (sum qty)}})
; sym  buy_qty
; ---  -------
; AAPL     400
; GOOG     125

Update

The update form modifies columns in-place on a quoted table variable. It supports the same where: and by: clauses as select.

Column Mutation

Update one or more columns with a new expression. The table is modified in-place when the first argument is a quoted symbol:

(set tab (table [sym price volume]
    (list [AAPL GOOG MSFT] [102 203 99] [500 400 900])))

; Increment volume by 1
(update {volume: (+ 1 volume) from: 'tab})
; sym  price volume
; ---  ----- ------
; AAPL   102    501
; GOOG   203    401
; MSFT    99    901

Conditional Update

Apply updates only to rows matching a predicate:

; Apply a custom function to price where volume == 901
(update {price: ((fn [x] (+ x 11)) price) from: 'tab where: (== volume 901)})
; sym  price volume
; ---  ----- ------
; AAPL   102    501
; GOOG   203    401
; MSFT   110    901

Set a column to a constant for matching rows:

(update {price: 0 from: 'tab where: (== volume 901)})

Update with Group-by

Combine where: and by: for grouped conditional updates:

(update {price: 0 from: 'tab by: sym where: (> volume 400)})

Insert

The insert function appends rows to a table. It accepts rows as a list of atoms, a list of vectors, a dictionary, or another table. Column order is flexible — missing columns receive null values.

Insert from List

(set t (table [ID Name Value]
    (list [1 2 3] ['Alice 'Bob 'Charlie] [10.0 20.0 30.0])))

; Insert a single record
(set t (insert t (list 4 'David 40.0)))

; Insert multiple records (list of vectors)
(set t (insert t (list [5 6] ['Eve 'Frank] [50.0 60.0])))

Insert from Dictionary

Dictionaries allow columns in any order. Missing columns are filled with null:

; Columns in different order
(set t (insert t (dict [Value Name ID] (list 120.0 'Leo 12))))

; Partial columns — Value will be null
(set t (insert t (dict [ID Name] (list 14 'Nancy))))

Insert from Table

; Append another table (column order can differ)
(set t (insert t (table [Value ID Name]
    (list [180.0 190.0] [18 19] ['Rose 'Sam]))))

In-place Insert

Pass a quoted table symbol for in-place mutation:

; Mutate table in-place (no set needed)
(insert 't (dict [Value Name ID] (list 200.0 'Tom 20)))

Upsert

The upsert function inserts new rows or updates existing ones based on a key column index. The second argument is the 0-based column index used as the match key.

Insert New Rows

(set t (table [ID Name Value]
    (list [1 2 3] ['Alice 'Bob 'Charlie] [10.0 20.0 30.0])))

; Upsert on column 1 (ID). ID=4 is new, so insert.
(set t (upsert t 1 (list 4 'David 40.0)))

Update Existing Rows

; ID=2 exists — update in place
(set t (upsert t 1 (list 2 'Bobby 25.0)))
; ID Name    Value
; -- ----    -----
;  1 Alice    10.0
;  2 Bobby    25.0  ← updated
;  3 Charlie  30.0
;  4 David    40.0

Upsert from Dictionary

Like insert, columns can be in any order:

; Columns reordered — upsert matches on ID
(set t (upsert t 1 (dict [Name Value ID] (list 'Bobby2 22.0 2))))

; Mixed insert + update in one call
(set t (upsert t 1 (dict [Value ID Name]
    (list [35.0 140.0] [3 14] ['Charlie2 'Nancy]))))

In-place Upsert

(upsert 't 1 (dict [Value Name ID] (list 170.0 'Quinn 17)))

Delete

The delete function removes rows matching a predicate. It returns a new table without the matched rows.

(set t (table [x y] (list [1 2 3 4] [A B C D])))

; Delete rows where x > 2
(set t (delete t (> x 2)))
; x y
; - -
; 1 A
; 2 B

Quick Reference