writing-graph-queries

DuckPGQ Query Reference

Graph schema

-- Vertex: nodes table. Edge: edges table (source_id -> target_id).
-- Edge direction: source REFERENCES target (stg_orders -> raw_orders = stg depends on raw).
-- Upstream = follow -> . Downstream = follow <- .
CREATE PROPERTY GRAPH sqlprism_graph
  VERTEX TABLES (nodes)
  EDGE TABLES (edges SOURCE KEY (source_id) REFERENCES nodes (node_id)
    DESTINATION KEY (target_id) REFERENCES nodes (node_id))

Core syntax

Use COLUMNS (...) — never RETURN. Use {min,max} bounds — never unbounded +.

FROM GRAPH_TABLE (sqlprism_graph MATCH <pattern> COLUMNS (<expressions>))

Query patterns

Vertices:

FROM GRAPH_TABLE (sqlprism_graph MATCH (n:nodes) COLUMNS (n.node_id, n.name, n.kind))

Single-hop edges:

FROM GRAPH_TABLE (sqlprism_graph
  MATCH (a:nodes)-[e:edges]->(b:nodes)
  COLUMNS (a.name AS source, b.name AS target, e.relationship))

Filtered (upstream of a model):

FROM GRAPH_TABLE (sqlprism_graph
  MATCH (a:nodes WHERE a.name = 'stg_orders')-[e:edges]->(b:nodes)
  COLUMNS (a.name AS model, b.name AS depends_on))

Multi-hop (bounded, 1-3 hops):

FROM GRAPH_TABLE (sqlprism_graph
  MATCH (a:nodes WHERE a.name = 'stg_orders')-[e:edges]->{1,3}(b:nodes)
  COLUMNS (a.name AS model, b.name AS upstream))

Shortest path (forward direction only):

FROM GRAPH_TABLE (sqlprism_graph
  MATCH p = ANY SHORTEST
    (a:nodes WHERE a.name = 'stg_orders')-[e:edges]->{1,10}(b:nodes WHERE b.name = 'raw_orders')
  COLUMNS (a.name, b.name, path_length(p)))

Analytics functions

These are table functions, not GRAPH_TABLE queries:

SELECT * FROM pagerank(sqlprism_graph, nodes, edges)
-- Returns (rowid, score)

SELECT * FROM weakly_connected_component(sqlprism_graph, nodes, edges)
-- Returns (node_id, component_id)

SELECT * FROM local_clustering_coefficient(sqlprism_graph, nodes, edges)
-- Returns (node_id, lcc)

More examples: See EXAMPLES.md for real-world queries (downstream consumers, PageRank with JOINs, fan-in/fan-out, root sources, leaf models).