JSON documents and relationships

MATCH (person:Person)-[link:KNOWS]->(friend:Person)
RETURN person.name, friend.name, link.since

A document’s array can supply the rows behind a relationship. Map the parent ID, extract each array element’s target ID and properties, then traverse those edges with ordinary Cypher or Gremlin. JSON functions also work directly in expressions:

RETURN json.value(JSON '{"author":{"name":"Ada"}}', '$.author.name') AS author,
       json.value(JSON '{"count":7}', '$.count', 'int64') AS count

JSON values have their own type. Use a JSON '...' literal or json.parse(text); ordinary strings remain strings. Register a document column with schema type json in a compiler request, or use ir::functions::domain::json_type() for an Arrow schema in Rust.

Turn an array into edges

Suppose each person stores a document like this in profile:

{"friends":[{"id":2,"since":2020},{"id":3,"since":2023}]}

Use jsonb for the PostgreSQL column or JSON for the DuckDB column:

-- PostgreSQL
CREATE TABLE people (id BIGINT PRIMARY KEY, name TEXT, profile JSONB);

-- DuckDB
CREATE TABLE people (id BIGINT PRIMARY KEY, name VARCHAR, profile JSON);

Both tables can hold these example rows:

INSERT INTO people VALUES
  (1, 'Ada', '{"friends":[{"id":2,"since":2020},{"id":3,"since":2023}]}'),
  (2, 'Grace', '{"friends":[]}'),
  (3, 'Katherine', '{"friends":[]}');

The mapping below expands profile.friends into a named relation. Its fields become the source ID, target ID, and relationship properties:

[node.Person]
table = "people"
id = "id"

[node.Person.properties]
name = "name"
profile = "profile"

[collection_sources]
catalog = '''[
  {
    "name": "friend_links",
    "table": "people",
    "expand": "json.elements(profile, '$.friends')",
    "as": "item",
    "parent_columns": {"source_id": "id"},
    "ordinality": "position",
    "fields": {
      "target_id": "json.value(item.value, '$.id', 'int64')",
      "since": "json.value(item.value, '$.since', 'int64')"
    }
  }
]'''

[edge.KNOWS]
table = "friend_links"
src = "source_id"
dst = "target_id"
src_label = "Person"
dst_label = "Person"
edge_id = ["source_id", "position"]

[edge.KNOWS.properties]
since = "since"

Load the mapping with GraphMapping::from_toml and register the people schema or provider, as in the mapping reference. For the JSON compiler, put the same collection descriptor in the collection_sources array and use data_type: "json" for profile.

The opening query now returns Ada’s two friendships. Repeated array elements remain separate occurrences. position starts at one and distinguishes those edges; use an embedded stable relationship ID instead if identity must survive array reordering. json.elements itself exposes a zero-based index field.

Missing paths, SQL-null documents, and empty arrays produce no rows. A present value of the wrong kind is an error: json.elements requires an array. Set "outer": true on a collection source to retain parents with no child rows; the generated child fields and ordinality are null. Null endpoints do not form ordinary graph edges. A collection can use another collection as its source to expand nested documents.

Read and construct documents

MATCH (p:Person)
WHERE json.exists(p.profile, '$.friends')
RETURN p.name,
       json.array_length(p.profile, '$.friends') AS friend_count,
       json.query(p.profile, '$.friends') AS friends

json.query returns JSON. json.value returns a scalar string by default; pass a literal type name such as int64, float64, or boolean for a typed result. Missing paths return SQL null. An existing JSON null is preserved by json.query, while json.value returns SQL null for JSON null, objects, and arrays.

Construct values without string concatenation:

RETURN json.object('name', 'Ada', 'tags', json.array('graph', 'sql')) AS document

Strings stay JSON strings; JSON arguments embed their document value. SQL-null constructor values become JSON null. Object keys must be non-null strings. Duplicate object keys keep the last value.

json.transform converts a document to an explicitly typed nested value:

RETURN json.transform(
  JSON '{"id":7,"tags":["graph","sql"]}',
  JSON '{"id":"int64","tags":["string"]}'
) AS record

A schema object declares fields, a one-element schema array declares a list, and a type-name string declares a scalar. Missing fields become typed nulls. Invalid type conversions produce an error. The schema can also be supplied as a constant string.

Use documents in relationship rules

For example, map a document node’s payload and a requirement node’s criteria as JSON properties, then define containment:

[edge.MATCHES]
source = "Document"
target = "Requirement"
predicate = "json.contains(source.payload, target.criteria)"

MATCH (document:Document)-[:MATCHES]->(requirement:Requirement)
RETURN document.id, requirement.id

Containment checks the requested structure. Objects match a subset of their fields; arrays match an unordered subset, ignoring repeated requested values. {"a":{"b":1}} does not contain {"b":1} at the root. Use json.query first when containment should be tested within a particular subtree.

Function catalog

The row functions return lists of records when used as scalar expressions and can supply collection-source rows. Each record contains value, index, key, path, parent_path, and depth. Aggregates retain null inputs as JSON null; empty input returns SQL null. Specify ordering when array order or the last value of a repeated object key matters.

Execution and supported paths

OrchidDB has native implementations of these functions. PostgreSQL and DuckDB lower supported operations into their own JSON expressions and row-producing operations, so document work can compose with surrounding joins and filters. Both adapters support document reads, constructors, equality, containment, merge patch, aggregates, and collection expansion. DuckDB also lowers nested json.transform results and text validation. PostgreSQL lowers path mutations and scalar/list transforms; transforms containing structs execute natively. DuckDB path mutations currently execute natively. SQL generation reports unsupported combinations explicitly; backend errors are not retried with different semantics.

Use definite paths such as $.friends[0].id, $["key.with.dots"], or JSON Pointer /friends/0/id for portable SQL examples. The native path evaluator also supports wildcards, recursive descent, unions, slices, and basic filters. Array positions are zero based; negative JSONPath positions count backward. Mutation functions require definite paths. See the detailed JSON reference for path grammar, null behavior, mutation rules, and engine capability limits.