Cross-language Queries

A LANG <name> block hands part of a query to another language. The block can appear anywhere a subquery or atomic expression is allowed. It ends when its enclosing scope ends, typically at a closing parenthesis, or at an explicit LANG EXIT. Blocks nest to any depth.

Every recipe below assumes an engine with the languages it uses loaded:

const db = new DortDB({
  mainLang: SQL(),
  additionalLangs: [Cypher({ defaultGraph: 'social' }), XQuery()],
  optimizer: { rules: defaultRules },
});

For a first end-to-end walkthrough with runnable data, start with Mixing Languages. One algebra, one plan, and one optimizer pass run across the boundary. Architecture explains why.

Use a graph query as a SQL table

A block that produces rows can be joined like any other subquery. Alias it as you would a derived table:

SELECT people.name AS name, friends.cnt AS friend_count
FROM people
JOIN (
  LANG cypher
  MATCH (p:Person)-[:KNOWS]->(f)
  RETURN p.id AS pid, count(f) AS cnt
) AS friends ON people.id = friends.pid
ORDER BY friends.cnt DESC

Note that ORDER BY names friends.cnt rather than the friend_count alias: with two sources in scope an unqualified column is ambiguous. See SQL restrictions.

Use a SQL result inside Cypher

The outer language is not privileged. Any loaded language can host any other. This Cypher query unwraps rows produced by SQL:

UNWIND (
  LANG sql
  SELECT people.id FROM people ORDER BY people.name
) AS pid
RETURN pid

To make a whole query use a non-default language without changing the engine config, either open with a top-level LANG line or pass the mainLang option.

Filter on a value computed in another language

An inner block can read the scopes that surround it, which is what makes correlated cross-language subqueries possible. Here the Cypher block uses the outer people.id, so it is evaluated per row:

SELECT people.name AS name
FROM people
WHERE (
  LANG cypher
  MATCH (p:Person {id: people.id})-[:KNOWS]->(f)
  RETURN count(f)
) > 1

The block returns a single value here rather than rows, so it can stand where a scalar is expected. Whether a block produces rows or values is a property of the language and the query. See Unified Object Representation.

Reach an outer value from embedded SQL

In SQL an unqualified column is assumed to belong to the current FROM table. When SQL is embedded in a language that does not prefix its identifiers, such as Cypher, that assumption gets in the way: you need to name a value from the enclosing scope, not a column of the current table. Prefix it with the nonlocal schema:

MATCH (p:Person)
WITH p.id AS pid, p.name AS name
RETURN name, (
  LANG sql
  SELECT people.city FROM people WHERE people.id = nonlocal.pid
) AS city

Omitting nonlocal fails quietly

Written as WHERE people.id = pid, the same query resolves pid against people instead of the Cypher scope. It parses, it runs, and every row comes back with city set to null. If a cross-language predicate matches nothing, check this first.