PebbleScript JSON Notation

The two forms have different jobs:

JSON Notation is never written to a PSOSF archive or an Excel workbook. A tool that receives JSON Notation converts it to textual PebbleScript before writing.

Why a second form? Textual PebbleScript passes arguments by position. In pebblestream:join("Rates", "id", "rate-id", "date", "rate-date"), nothing but position says which values pair up. JSON Notation names every argument and groups repeated ones, so the same directive can be validated exactly with JSON Schema and is harder for an agent to get wrong.

PebbleScript is declarative

PebbleScript describes what a worksheet is, not a sequence of steps to perform. Where a directive is written, and in what order, has no effect on when it runs: the same directives produce the same result whether they appear in one annotation or spread across several columns, and in any order.

Transformative directives on a worksheet always run in this fixed order:

1. from
2. cartesian-product
3. join
4. outer-join
5. filter
6. sort
7. group, together with the directives that run within it:
   coalesce,
   sum,
   ratio,
   mult,
   equal and
   regex. The order among these does not matter. They may read the same column, but no two may write to the same column.
8. group-unique (deprecated)
9. group-sum (deprecated)
10. group-ratio (deprecated)
11. header-map
12. split (deprecated)
13. prune, which always runs last

All transformative directives run before the worksheet's formulas. A worksheet is computed in three stages:

1. The transformative directives run, in the order above.
2. Formula columns are filled down to the transformed rows and calculated.
3. Annotative directives that check cell values run on the completed worksheet.

For example, on a worksheet with join, filter and group, rows are joined first, then filtered, then grouped, however the three are written. So a filter can test a column brought in by the join, but it always sees rows before they are grouped and cannot test a group's total. To remove groups by their total, use prune, which runs after grouping: pebblestream:prune("total", ">=0") keeps only groups with a non-negative total. Neither filter nor prune can test a formula column, since both run before formulas are calculated; to remove rows by a formula's result, compute it on one worksheet and filter on a second worksheet that reads from it.

After the transformative directives run, every formula column is filled down to the transformed rows automatically. This is implied by the design; don't write match-rows to get it. An explicit match-rows cannot be combined with a transformative directive on the same worksheet.

Annotative directives that check cell values, such as fail, warn, not-blank, unique and allowed-values, run on the completed worksheet, after its transformative directives.

fail and warn are evaluated on formula cells. They annotate a formula column and test that column's formulas after the formulas have been filled down to the transformed rows. They never check a cell produced directly by a transformative directive. To check a value a transformative directive produces, such as a group's total, add a formula column that tests it and put fail or warn on that column:

type and total are produced by the group step; total is negative is a formula, filled down to one row per group, and fail stops the run for any group whose total is negative.

Annotative directives that do not check cell values, such as hint, out and the template directives, have no order. A worksheet may have fail and warn on as many columns as it needs.

When writing JSON Notation, order directives in the sequence above for readability. The order of items in an annotation array preserves where comments sit; it never changes what the worksheet computes.

The position of the remaining transformative directives in this order will be documented as it is confirmed.

Declared columns determine the result

A worksheet's headers determine which columns appear after its transformation. A column from the source worksheet appears in the result only if the worksheet declares it as a header; any other source column is left out. The one requirement is that every column a transformative directive writes to must be declared.

In the fail example above, total must be declared because sum writes to it. type, the grouping column, is not a target, so it may be left out; include it when consumers of the output need to know which group each row belongs to. The same holds for header-map: each new header it writes must be declared, while source columns it does not touch are optional.

For example, take two worksheets with two columns each:

A worksheet that takes their cartesian product can declare just one of the four columns:

Its result is the single fund column. The rows are still the full product, one per fund and period, so Fund A and Fund B each appear twice. Leaving columns out changes which columns appear, not how many rows there are.

Directives

A directive is a JSON object. The directive key names it, without the pebblestream: prefix. Every other key is an argument.

pebblestream:join("Rates", "id", "rate-id", "date", "rate-date")

{
  "directive": "join",
  "sheet": "Rates",
  "on": [
    {"column": "id", "source-column": "rate-id"},
    {"column": "date", "source-column": "rate-date"}
  ]
}

Rules:

• Keys are lowercase and hyphenated.
• Each directive accepts only the keys listed for it in the catalog. Unknown keys are an error.
• An argument repeated in text (such as the sheets in from) is a JSON array. A repeated group of arguments (such as the column pairs in join) is an array of objects.
• An optional argument is omitted when absent. It is never null.
• Header names, sheet names and other text are JSON strings. Numbers are JSON numbers.
• Any argument may instead be a cell reference.

Cell references

Any argument can be a cell reference instead of a literal value. In JSON Notation a reference is an object with a single ref key holding the reference in Excel's A1 notation:

pebblestream:filter("amount", ">0", "fund", Inputs!B2)
pebblestream:generate-rows('Run Settings'!$B$1)

{"directive": "filter",
 "conditions": [{"column": "amount", "predicate": ">0"},
                {"column": "fund", "predicate": {"ref": "Inputs!B2"}}]}

{"directive": "generate-rows", "count": {"ref": "'Run Settings'!$B$1"}}

The object form keeps a reference distinct from a string that happens to look like one: {"ref": "Inputs!B2"} is a reference, while "Inputs!B2" is the literal text Inputs!B2.

Rules:

• ref holds one cell, optionally qualified by a sheet name and optionally absolute (B2, $B$2, Inputs!B2). A sheet name containing spaces or punctuation is enclosed in single quotes, with any single quote inside it doubled, as in Excel.
• A reference can stand anywhere a literal can, including inside arrays ({"directive": "from", "sheets": [{"ref": "Setup!A2"}, "Accounts"]}) and inside repeated groups.
• In the catalog's key tables, each key's type describes the literal it accepts. Every key also accepts a reference.

Annotations

An annotation is the full content of one column's header note: directives and free-form comments, in order. In JSON Notation it is an array whose items are either a directive object or a comment object.

Totals of all ledger balances, by account type.
pebblestream:from("Accounts")
pebblestream:group("type")
pebblestream:sum("amount")
Rebuilt nightly from the general ledger extract.

[
  {"comment": "Totals of all ledger balances, by account type."},
  {"directive": "from", "sheets": ["Accounts"]},
  {"directive": "group", "columns": ["type"]},
  {"directive": "sum", "column": "amount"},
  {"comment": "Rebuilt nightly from the general ledger extract."}
]

A comment object holds exactly one line of text. Its text must not contain a line break, and must not begin (after leading whitespace) with pebblestream:, since that line would be read back as a directive.

An empty annotation is an empty array.

Converting between JSON and text

JSON to text

Each item becomes one line, in order. A comment item becomes its text. A directive item becomes:

1. pebblestream: followed by the directive name.
2. If the directive has no arguments present, nothing more. This is the bare form, e.g. pebblestream:unique.
3. Otherwise, the arguments in the directive's positional order, separated by , and enclosed in parentheses. Repeated groups are flattened in array order, and an absent optional argument is left out.
4. Strings are enclosed in double quotes; numbers are written without quotes; cell references are written without quotes, exactly as held in ref.

The colon form (pebblestream:name:a,b) is deprecated and is never produced.

Text to JSON

A converter accepts every form textual PebbleScript allows: bare, parentheses, and the deprecated colon form. Which form was used is not recorded in JSON Notation.

A quoted argument becomes a string, an unquoted number becomes a number, and an unquoted cell reference becomes a {"ref": ...} object.

Round trips

• JSON → text → JSON returns the same JSON, apart from key order.
• Text → JSON → text returns the same directives, in canonical form: a directive written with the colon form comes back with parentheses, and pebblestream:unique() comes back as pebblestream:unique.

Because the second round trip normalizes the text, converters that move annotations between PSOSF and Excel copy the text verbatim and never pass it through JSON Notation. Normalization happens only when an agent's JSON is written as new text.

Directive catalog

Each entry shows the textual form and its JSON Notation, followed by the directive's keys in positional order.

Argument names marked provisional are placeholders. The directive's arguments are positional in text, and their meanings have not yet been named; the names will change before this notation is final.

Sources

from

pebblestream:from("Accounts", "Adjustments")

{"directive": "from", "sheets": ["Accounts", "Adjustments"]}

cartesian-product

pebblestream:cartesian-product("Funds", "Periods")

{"directive": "cartesian-product", "sheets": ["Funds", "Periods"]}

join and outer-join

pebblestream:join("Rates", "id", "rate-id")
pebblestream:outer-join("Rates", "id", "rate-id", "date", "rate-date")

{"directive": "join", "sheet": "Rates",
 "on": [{"column": "id", "source-column": "rate-id"}]}

{"directive": "outer-join", "sheet": "Rates",
 "on": [{"column": "id", "source-column": "rate-id"},
        {"column": "date", "source-column": "rate-date"}]}

Selecting, ordering and renaming rows

filter and prune

pebblestream:filter("amount", ">0", "type", "corporate")

{"directive": "filter",
 "conditions": [{"column": "amount", "predicate": ">0"},
                {"column": "type", "predicate": "corporate"}]}

prune has the same keys and works the same way as filter, but always runs last, after every other transformative directive and before formulas are calculated. Use filter to remove rows before they are joined or grouped, and prune to remove rows from the transformed result, for example groups whose total fails a test.

sort

Columns are sorted in the order given: by the first column, then by the second within it, and so on. Each column sorts ascending unless it carries the :desc suffix; :asc may be written explicitly.

pebblestream:sort("Last", "First", "Date":desc)

{"directive": "sort",
 "columns": [{"column": "Last"},
             {"column": "First"},
             {"column": "Date", "order": "desc"}]}

An order key is written as a suffix only when present, so {"column": "Date", "order": "asc"} becomes "Date":asc and {"column": "Date"} becomes "Date". Both sort ascending, and each round-trips to itself.

header-map

pebblestream:header-map("Acct No", "account", "Bal", "amount")

{"directive": "header-map",
 "mappings": [{"column": "Acct No", "new-column": "account"},
              {"column": "Bal", "new-column": "amount"}]}

Grouping

group

pebblestream:group("fund", "type")

{"directive": "group", "columns": ["fund", "type"]}

group-unique, group-sum and group-ratio (deprecated)

These directives are deprecated. Don't use them in new worksheets. Existing worksheets that contain them still convert, with the same keys as group:

pebblestream:group-sum("fund")

{"directive": "group-sum", "columns": ["fund"]}

sum, min, max, average, mult, equal and ratio

pebblestream:sum("amount")
pebblestream:sum("amount", "total")

{"directive": "sum", "column": "amount"}
{"directive": "sum", "column": "amount", "new-column": "total"}

min, max, average, mult, equal and ratio have the same keys.

Within the group step, several directives may read the same column, but no two may write to the same column. A directive writes to new-column when it is given, and otherwise to column. So sum and ratio can both read amount if they write to different new columns:

[
  {"directive": "sum", "column": "amount", "new-column": "total"},
  {"directive": "ratio", "column": "amount", "new-column": "share"}
]

But two directives writing to total, or two directives on amount that both omit new-column, are an error.

balance

pebblestream:balance("amount", "debit", "credit")

{"directive": "balance", "column": "amount", "new-column-1": "debit", "new-column-2": "credit"}

coalesce and expand

pebblestream:coalesce

{"directive": "coalesce"}

No keys. expand is the same.

yield and npv

pebblestream:yield("cash-flow", "date", "price", "settlement", "yield")

{"directive": "yield",
 "column-1": "cash-flow", "column-2": "date",
 "column-3": "price", "column-4": "settlement",
 "new-column": "yield"}

npv has the same keys.

pivot

pebblestream:pivot("account", "amount", "period", "text", "Q1")

{"directive": "pivot",
 "column": "account", "value": "amount", "pivot-column": "period",
 "filter-type": "text", "filter-value": "Q1"}

Allocation and paths

apportion and relieve

pebblestream:apportion("partner", "amount", "ratio", "period")

{"directive": "apportion",
 "column-1": "partner", "column-2": "amount", "column-3": "ratio", "column-4": "period"}

relieve has the same keys.

paths

pebblestream:paths("parent", "child", "weight")

{"directive": "paths", "column-1": "parent", "column-2": "child", "column-3": "weight"}

paths-operation

pebblestream:paths-operation("product", "effective-ownership")

{"directive": "paths-operation", "operation": "product", "new-column": "effective-ownership"}

Generating rows

match-rows

pebblestream:match-rows("Accounts")

{"directive": "match-rows", "sheet": "Accounts"}

generate-rows

pebblestream:generate-rows(12)

{"directive": "generate-rows", "count": 12}

stop

stop annotates a column, and may appear on more than one column of a worksheet. The first column to evaluate to true, in execution order, stops the worksheet's execution. Execution order follows the dependencies between formulas, not the columns' positions, so a stop column to the left of another is not necessarily evaluated first.

The optional number is the most rows the worksheet will generate. When it is omitted, the limit configured for the runtime applies. Always set it, so the worksheet's behavior doesn't depend on how the runtime is configured.

pebblestream:stop(1000)
pebblestream:stop

{"directive": "stop", "max-rows": 1000}
{"directive": "stop"}

Annotating columns and worksheets

Directives without arguments

These directives take no arguments. In JSON Notation they have only the directive key, and in text they are written bare.

pebblestream:not-blank

{"directive": "not-blank"}

unique, grouped, warn, fail, not-blank, ignore, sorted, do-not-compile, out

fail and warn may each appear on more than one column of a worksheet.

allowed-values

pebblestream:allowed-values("individual", "corporate")

{"directive": "allowed-values", "values": ["individual", "corporate"]}

hint

pebblestream:hint("Use the closing balance, not the average balance.")

{"directive": "hint", "text": "Use the closing balance, not the average balance."}

identical-columns

pebblestream:identical-columns("Budget", "Actuals")

{"directive": "identical-columns", "sheets": ["Budget", "Actuals"]}

Templates

template-name

pebblestream:template-name("Capital Account Statement")

{"directive": "template-name", "name": "Capital Account Statement"}

template-out-style

pebblestream:template-out-style("number.percentage", "number.currency")

{"directive": "template-out-style", "styles": ["number.percentage", "number.currency"]}

Template directives without arguments

block-id, template-row, template-column, leftmost-column, rightmost-column, top-row, bottom-row, block-row-count, row-oriented-headers, column-oriented-headers, template-out-header, template-out-cell, template-out-down, template-out-right, template-out-group, template-out-value-type, template-out-generate-clean

pebblestream:template-out-down

{"directive": "template-out-down"}

Validation

Because every argument is named and every repeated group is an array of objects, each directive's JSON Notation can be described exactly in JSON Schema. For join:

{
  "type": "object",
  "properties": {
    "directive": {"const": "join"},
    "sheet": {"anyOf": [{"$ref": "#/$defs/sheet"}, {"$ref": "#/$defs/cell-ref"}]},
    "on": {
      "type": "array",
      "minItems": 1,
      "items": {
        "type": "object",
        "properties": {
          "column": {"anyOf": [{"$ref": "#/$defs/header"}, {"$ref": "#/$defs/cell-ref"}]},
          "source-column": {"anyOf": [{"$ref": "#/$defs/header"}, {"$ref": "#/$defs/cell-ref"}]}
        },
        "required": ["column", "source-column"],
        "additionalProperties": false
      }
    }
  },
  "required": ["directive", "sheet", "on"],
  "additionalProperties": false
}

Here #/$defs/cell-ref is an object with one required key, ref, whose value matches the A1 notation described under Cell references, and no other keys.

JSON Schema checks the shape of a directive. It cannot check rules that depend on the spreadsheet, such as whether source-column exists on the sheet named in sheet, or whether a directive's requires and excludes are satisfied by the other directives on the worksheet. The compiler checks those; submit the worksheet to the compiler service's validate endpoint before deploying.

Directives not yet in this notation

div/0, text, number, boolean, date, id, workflow-each, workflow-group, workflow-all, the Board directives and the connector directives will be added once their signatures are confirmed. header-count and split are deprecated and have no JSON Notation. regex is disabled.