SQL/PGQ — Graph Pattern Matching

Teide implements the SQL/PGQ (Property Graph Queries) extension, allowing you to define property graphs over relational tables and query them with powerful graph pattern matching. Graph queries compose naturally with standard SQL — you can use GROUP BY, ORDER BY, CTEs, and subqueries on graph results.

CREATE PROPERTY GRAPH

A property graph is a named overlay on existing relational tables. You declare which tables serve as vertex (node) tables and which serve as edge tables, specifying keys, labels, and which columns to expose as properties.

Syntax

CREATE PROPERTY GRAPH graph_name
  VERTEX TABLES (
    table_name
      KEY (column)
      LABEL label_name
      PROPERTIES (col1, col2, ...)
    [, ...]
  )
  EDGE TABLES (
    table_name
      KEY (column)
      SOURCE KEY (column) REFERENCES vertex_table (column)
      DESTINATION KEY (column) REFERENCES vertex_table (column)
      LABEL label_name
      PROPERTIES (col1, col2, ...)
    [, ...]
  )

Example: Social Network

Consider a social network with persons and friendship relationships:

import { Context } from 'teide-js';

const ctx = new Context();

// Create the underlying relational tables
ctx.executeSync(`
  CREATE TABLE persons (
    id INTEGER,
    name VARCHAR,
    age INTEGER,
    city VARCHAR
  )
`);

ctx.executeSync(`
  INSERT INTO persons VALUES
    (1, 'Alice',   30, 'London'),
    (2, 'Bob',     25, 'Paris'),
    (3, 'Charlie', 35, 'London'),
    (4, 'Diana',   28, 'Berlin'),
    (5, 'Eve',     32, 'Paris')
`);

ctx.executeSync(`
  CREATE TABLE friendships (
    id INTEGER,
    person1_id INTEGER,
    person2_id INTEGER,
    since INTEGER
  )
`);

ctx.executeSync(`
  INSERT INTO friendships VALUES
    (1, 1, 2, 2020),
    (2, 1, 3, 2019),
    (3, 2, 4, 2021),
    (4, 3, 5, 2022),
    (5, 4, 5, 2021)
`);

// Define the property graph
ctx.executeSync(`
  CREATE PROPERTY GRAPH social
    VERTEX TABLES (
      persons
        KEY (id)
        LABEL Person
        PROPERTIES (name, age, city)
    )
    EDGE TABLES (
      friendships
        KEY (id)
        SOURCE KEY (person1_id) REFERENCES persons (id)
        DESTINATION KEY (person2_id) REFERENCES persons (id)
        LABEL FriendOf
        PROPERTIES (since)
    )
`);

DROP PROPERTY GRAPH

Removes a property graph definition. The underlying tables are not affected.

ctx.executeSync(`DROP PROPERTY GRAPH social`);

GRAPH_TABLE with MATCH

The GRAPH_TABLE function lets you query a property graph using the MATCH clause for pattern matching. Results are returned as a standard relational table.

Basic 1-Hop Pattern

Find all direct friendships:

const friends = ctx.executeSync(`
  SELECT * FROM GRAPH_TABLE (social
    MATCH (a:Person)-[e:FriendOf]->(b:Person)
    COLUMNS (a.name AS person, b.name AS friend, e.since)
  )
`);

// person   | friend  | since
// ---------|---------|------
// Alice    | Bob     | 2020
// Alice    | Charlie | 2019
// Bob      | Diana   | 2021
// ...

WHERE Filters

Filter graph patterns with WHERE inside the MATCH:

const londonFriends = ctx.executeSync(`
  SELECT * FROM GRAPH_TABLE (social
    MATCH (a:Person)-[e:FriendOf]->(b:Person)
    WHERE a.city = 'London'
    COLUMNS (a.name AS person, b.name AS friend, a.city)
  )
`);

COLUMNS Clause

The COLUMNS clause specifies which properties to project from matched nodes and edges. You can use aliases and expressions just like in a SELECT:

const result = ctx.executeSync(`
  SELECT * FROM GRAPH_TABLE (social
    MATCH (a:Person)-[e:FriendOf]->(b:Person)
    COLUMNS (
      a.name AS person_name,
      b.name AS friend_name,
      a.age - b.age AS age_diff,
      e.since AS friends_since
    )
  )
`);

Variable-Length Paths

Pattern quantifiers let you match paths of variable length, enabling reachability queries and multi-hop traversals.

Bounded Length {min,max}

Match paths between 1 and 3 hops:

const nearby = ctx.executeSync(`
  SELECT * FROM GRAPH_TABLE (social
    MATCH (a:Person)-[:FriendOf]->{1,3}(b:Person)
    WHERE a.name = 'Alice'
    COLUMNS (a.name AS source, b.name AS reachable)
  )
`);

One-or-More +

Match paths of at least one hop (equivalent to {1,}):

const reachable = ctx.executeSync(`
  SELECT DISTINCT * FROM GRAPH_TABLE (social
    MATCH (a:Person)-[:FriendOf]->+(b:Person)
    WHERE a.name = 'Alice'
    COLUMNS (b.name AS reachable_person)
  )
`);

Zero-or-More *

Match paths of zero or more hops (includes the start node itself):

const allReachable = ctx.executeSync(`
  SELECT DISTINCT * FROM GRAPH_TABLE (social
    MATCH (a:Person)-[:FriendOf]->*(b:Person)
    WHERE a.name = 'Alice'
    COLUMNS (b.name AS reachable_person)
  )
`);

Shortest Path

Use ANY SHORTEST to find the shortest path between two nodes. The path_length() function returns the number of hops.

const shortest = ctx.executeSync(`
  SELECT * FROM GRAPH_TABLE (social
    MATCH ANY SHORTEST
      (a:Person)-[:FriendOf]->+(b:Person)
    WHERE a.name = 'Alice' AND b.name = 'Eve'
    COLUMNS (
      a.name AS source,
      b.name AS target,
      path_length() AS hops
    )
  )
`);

// source | target | hops
// -------|--------|-----
// Alice  | Eve    | 2

Edge Directions

SQL/PGQ supports three edge direction patterns:

// Find who considers Alice a friend (reverse direction)
const admirers = ctx.executeSync(`
  SELECT * FROM GRAPH_TABLE (social
    MATCH (a:Person)<-[:FriendOf]-(b:Person)
    WHERE a.name = 'Alice'
    COLUMNS (b.name AS admirer)
  )
`);

// Undirected: find all connections regardless of direction
const connections = ctx.executeSync(`
  SELECT * FROM GRAPH_TABLE (social
    MATCH (a:Person)-[:FriendOf]-(b:Person)
    WHERE a.name = 'Alice'
    COLUMNS (b.name AS connected_to)
  )
`);

Composing with SQL

Graph query results are standard tables, so they compose freely with all SQL features.

GROUP BY on Graph Results

// Count friends per person
const friendCounts = ctx.executeSync(`
  SELECT person, COUNT(*) AS num_friends
  FROM GRAPH_TABLE (social
    MATCH (a:Person)-[:FriendOf]-(b:Person)
    COLUMNS (a.name AS person, b.name AS friend)
  )
  GROUP BY person
  ORDER BY num_friends DESC
`);

CTEs with Graph Tables

// Use a CTE to find well-connected people, then join with their cities
const result = ctx.executeSync(`
  WITH connections AS (
    SELECT person, COUNT(*) AS degree
    FROM GRAPH_TABLE (social
      MATCH (a:Person)-[:FriendOf]-(b:Person)
      COLUMNS (a.name AS person)
    )
    GROUP BY person
  )
  SELECT p.name, p.city, c.degree
  FROM persons p
  JOIN connections c ON p.name = c.person
  WHERE c.degree >= 2
  ORDER BY c.degree DESC
`);

Subqueries with Graph Tables

// Find persons who are reachable from Alice within 2 hops
// and live in a city with more than one person
const result = ctx.executeSync(`
  SELECT * FROM GRAPH_TABLE (social
    MATCH (a:Person)-[:FriendOf]->{1,2}(b:Person)
    WHERE a.name = 'Alice'
      AND b.city IN (
        SELECT city FROM persons
        GROUP BY city HAVING COUNT(*) > 1
      )
    COLUMNS (b.name AS person, b.city)
  )
`);