Path Functions

Nodes live in a tree, and the path column is the address in it. These functions compute with paths and select by position in the hierarchy. Examples use a blog workspace with /hello, /news, /news/first and /news/second.

Two kinds of function appear here. DEPTH, PARENT, ANCESTOR and PATH_STARTS_WITH take a path expression and can be used anywhere. CHILD_OF, DESCENDANT_OF and REFERENCES take only the target and apply to the current row; they belong in WHERE and are served by the path and reference indexes.

DEPTH

Number of segments in a path.

DEPTH(path) → INT

SELECT DEPTH('/') AS root, DEPTH('/news') AS d1, DEPTH('/news/first') AS d2;
-- {"root":0,"d1":1,"d2":2}

SELECT name FROM 'blog' WHERE DEPTH(path) = 2;
-- first, second

The depth column holds the same number for every row, without a function call.

PARENT

The parent path, or the ancestor levels steps up.

PARENT(path) → PATH
PARENT(path, levels) → PATH

SELECT path, PARENT(path) AS parent, PARENT(path, 2) AS grandparent FROM 'blog' ORDER BY path;

{"path":"/hello","parent":"/","grandparent":""}
{"path":"/news","parent":"/","grandparent":""}
{"path":"/news/first","parent":"/news","grandparent":"/"}
{"path":"/news/second","parent":"/news","grandparent":"/"}

The parent of a root-level node is /. Going above the root yields an empty string rather than NULL.

SELECT PARENT(path) AS parent, COUNT(*) AS children FROM 'blog' GROUP BY PARENT(path) ORDER BY parent;
-- {"parent":"/","children":2}, {"parent":"/news","children":2}

ANCESTOR

The prefix of a path with n segments: the ancestor at absolute depth n.

ANCESTOR(path, n) → PATH

SELECT path, ANCESTOR(path, 1) AS section, ANCESTOR(path, 2) AS sub FROM 'blog' ORDER BY path;

{"path":"/hello","section":"/hello","sub":""}
{"path":"/news","section":"/news","sub":""}
{"path":"/news/first","section":"/news","sub":"/news/first"}
{"path":"/news/second","section":"/news","sub":"/news/second"}

When n equals the node's own depth the result is the path itself; when the path is shallower than n, or n is 0, the result is an empty string. ANCESTOR(path, 1) is the usual way to group a workspace by top-level section:

SELECT ANCESTOR(path, 1) AS section, COUNT(*) AS nodes FROM 'blog' GROUP BY ANCESTOR(path, 1);

PATH_STARTS_WITH

Text prefix test on a path. Matches the prefix itself and everything below it.

PATH_STARTS_WITH(path, prefix) → BOOLEAN

SELECT name FROM 'blog' WHERE PATH_STARTS_WITH(path, '/news');
-- news, first, second

The comparison is on characters, so /newsx also starts with /news. Use DESCENDANT_OF when you mean tree membership.

CHILD_OF

Rows that are direct children of a path.

CHILD_OF(parent_path) → BOOLEAN

SELECT name FROM 'blog' WHERE CHILD_OF('/news');
-- first, second

SELECT name FROM 'blog' WHERE CHILD_OF('/');
-- hello, news

CHILD_OF takes one argument, the parent; it always tests the current row. Writing CHILD_OF(path, '/news') is a Function not found error.

DESCENDANT_OF

Rows anywhere under a path, optionally limited to a number of levels.

DESCENDANT_OF(ancestor_path) → BOOLEAN
DESCENDANT_OF(ancestor_path, max_levels) → BOOLEAN

SELECT name FROM 'blog' WHERE DESCENDANT_OF('/news');
-- first, second

SELECT name FROM 'blog' WHERE DESCENDANT_OF('/news', 1);   -- one level only
-- first, second

The ancestor itself is not included. The traversal is depth-first in document order, so parents come before their children, and the __tree_order column is populated for keyset paging (see SELECT). DESCENDANT_OF('/') returns no rows; use a plain scan for the whole workspace.

DESCENDANT_OF composes with the other predicates:

SELECT name FROM 'blog'
WHERE DESCENDANT_OF('/news') AND node_type = 'raisin:Page' AND properties->>'published' = 'true'
ORDER BY name;

A CHILD_OF or DESCENDANT_OF listing that selects and filters only node-record columns (id, path, name, node_type, archetype, created_at, updated_at, created_by, updated_by, published_at, published_by, version, depth, parent_name, locale, __order, __tree_order) does not decode node properties, which makes it about 20% faster. Properties are still loaded for SELECT *, when the select list or a filter reads a property, and when row-level security has grants with conditions.

SELECT path, name, archetype FROM 'blog' WHERE DESCENDANT_OF('/news');   -- properties not decoded

In UPDATE and DELETE, a DESCENDANT_OF or CHILD_OF predicate makes the statement a bulk job (UPDATE).

REFERENCES

Rows whose reference properties point at a target node. The reverse reference index is keyed by the target's id, so the lookup is fast and survives the target being moved.

REFERENCES('workspace:/path') → BOOLEAN

A reference property is stored as {"raisin:ref": id, "raisin:workspace": ws, "raisin:path": path}. Writing one with a path is enough; the server resolves the id:

UPDATE 'blog'
SET properties = properties || '{"related": {"raisin:ref": "/news/second", "raisin:workspace": "blog"}}'
WHERE path = '/news/first';

SELECT properties->'related' AS related FROM 'blog' WHERE path = '/news/first';
-- {"related":{"raisin:ref":"b4363972-fe50-4fe7-8574-c29dd3fc6dfa","raisin:workspace":"blog","raisin:path":"/news/second"}}

SELECT name FROM 'blog' WHERE REFERENCES('blog:/news/second');
-- first

SELECT COUNT(*) AS n FROM 'blog' WHERE REFERENCES('blog:/news/second') AND DESCENDANT_OF('/news');
-- {"n":1}

The argument must carry the workspace prefix; REFERENCES('/news/second') fails with must be in 'workspace:/path' format. It can be a bound parameter (REFERENCES($1) with "blog:/news/second" in params). EXPLAIN shows a ReferenceIndexScan; other predicates in the same WHERE filter its result.

RESOLVE

Load the node a reference points at, as JSON.

RESOLVE(reference_json) → JSONB
RESOLVE(reference_json, depth) → JSONB

SELECT RESOLVE(properties->'related') AS target FROM 'blog' WHERE path = '/news/first';

{"target":{"id":"b4363972-fe50-4fe7-8574-c29dd3fc6dfa","path":"/news/second","name":"second","node_type":"raisin:Page","title":"Second","views":7,"published":true,"tags":["b","c"],"author":{"name":"Ana","city":"Lisbon"}}}

The result flattens the target's columns and properties into one object. RESOLVE must be the outermost expression of its select-list item: RESOLVE(...)->>'name' is not evaluated (Unknown function: RESOLVE), so read the field from the returned object on the client.

A third argument limits which properties are kept on each inlined node, as a comma-separated list; id, name, path and node_type are always kept:

RESOLVE(reference_json, depth, fields) → JSONB

SELECT RESOLVE(properties, 2, 'title,file,alt') AS doc FROM 'blog' WHERE path = '/news/first';

Every target is read at the statement's snapshot, translated into the query's locale, and checked against row-level security. A target that is missing, hidden in the locale, or not readable by the caller leaves the reference as it was; the three cases look the same, so RESOLVE does not reveal whether a node you cannot read exists. A target shared by many rows is read once per statement.

One statement's RESOLVE calls together may read at most 5,000 distinct targets, inline at most 50,000 references and at most 32 MB. Exceeding a limit fails the statement with RESOLVE() budget exceeded, rather than returning a document with some references left unresolved. Lower the depth, pass a fields list, or select fewer rows.

RESOLVE_PATH

The id of the node at a localized path, or NULL.

RESOLVE_PATH(workspace, locale, path) → TEXT

SELECT RESOLVE_PATH('pages', 'fr', '/produits/chaise') AS id FROM 'pages' LIMIT 1;
-- {"id":"…"}

Each segment is matched against the translated node names (__node_name) along the locale's fallback chain, then the canonical name. A path that does not resolve, a node hidden in the locale, and a node the caller cannot read all return NULL.

NEIGHBORS

A table function over graph relations created with RELATE.

NEIGHBORS(start, direction, relation_type) → TABLE

Parameter	Description
start	Node id, path, or 'workspace:/path'
direction	'OUT', 'IN' or 'BOTH'
relation_type	A relation type name, or NULL / '' for all

SELECT n.path, n.relation_type, n.weight
FROM NEIGHBORS('blog:/news/first', 'OUT', NULL) AS n;

The result carries the neighbour's node columns plus relation_type and weight. In testing against the current build, NEIGHBORS returned no rows for relations created with RELATE in the same workspace; GRAPH_TABLE (Graph algorithms) is the working way to traverse relations from SQL.