Graph queries

BigQuery property graphs let you write a JOIN chain as a pattern of nodes and edges in GQL. Querylab.io parses GQL, completes labels and properties from your graph's schema, converts JOIN queries to GRAPH_TABLE and back, and draws node-edge results in a Graph tab.

GRAPH FinGraph
MATCH (sender:Person)-[t:Transfers]->(receiver:Person)
FILTER t.amount > 500
RETURN sender.name AS sender, receiver.name AS receiver, t.amount

Everything on this page is BigQuery’s own syntax (GRAPH … MATCH … RETURN as a standalone statement, GRAPH_TABLE(…) inside SQL, and CREATE PROPERTY GRAPH) except where marked as a Querylab.io feature: the converter, the completions, and the Graph tab. MEASURE properties and GRAPH_EXPAND are a BigQuery preview feature.

The query above is the same as this JOIN, and both return the same rows:

SELECT p1.name AS sender, p2.name AS receiver, t.amount
FROM Person AS p1
JOIN Transfers AS t ON p1.id = t.source_id
JOIN Person AS p2 ON t.dest_id = p2.id
WHERE t.amount > 500

Writing a pattern

A node is parentheses, an edge is brackets with an arrow. Each can carry a variable, a label, both, or neither.

(a:Person)                    -- variable and label
(:Person)                     -- label only
(a IS Person)                 -- IS instead of colon
(a:Person WHERE a.age > 30)   -- inline filter, evaluated as part of the match
()                            -- any node

-[t:Transfers]->              -- outgoing edge
<-[t:Transfers]-              -- incoming edge
-[t:Transfers]-               -- either direction
->                            -- outgoing, anonymous

Chain them for multi-hop paths, and separate patterns with commas:

GRAPH FinGraph
MATCH (a:Person)-[t1:Transfers]->(mid:Person)-[t2:Transfers]->(b:Person),
    (b)-[o:Owns]->(acc:Account)
RETURN a.name AS origin, b.name AS destination, t1.amount + t2.amount AS total, acc.id

Building a linear query from statements

A linear query is GRAPH name, one or more statements, and a RETURN at the end.

FILTER and an inline WHERE differ: MATCH (a:Person WHERE a.age > 30) is part of the match; FILTER a.age > 30 runs on the rows the match produced.

GRAPH FinGraph
MATCH (:Account)-[:Transfers]->(account:Account)
RETURN account, COUNT(*) AS num_incoming
GROUP BY account
NEXT
MATCH (account:Account)<-[:Owns]-(owner:Person)
RETURN account.id AS account_id, owner.name AS owner_name, num_incoming

Using a graph query inside SQL

GRAPH_TABLE(…) turns a graph query into a table source, so you can aggregate, order, or join it with ordinary tables.

SELECT sender, SUM(amount) AS total_sent
FROM GRAPH_TABLE(
FinGraph
MATCH (s:Person)-[t:Transfers]->(r:Person)
RETURN s.name AS sender, t.amount AS amount
)
GROUP BY sender
ORDER BY total_sent DESC
LIMIT 10

Defining a graph

CREATE PROPERTY GRAPH names the node and edge tables, their keys, labels, and which columns are exposed as properties. Querylab.io validates the statement as you type: an edge table without both SOURCE KEY and DESTINATION KEY, or a graph without NODE TABLES, gets an error squiggle.

CREATE PROPERTY GRAPH FinGraph
NODE TABLES (
  Person KEY (id) LABEL Person PROPERTIES (id, name, age),
  Account KEY (id) LABEL Account PROPERTIES (id, balance)
)
EDGE TABLES (
  Transfers KEY (transfer_id)
    SOURCE KEY (source_id) REFERENCES Person (id)
    DESTINATION KEY (dest_id) REFERENCES Person (id)
    LABEL Transfers PROPERTIES (transfer_id, amount, date),
  Owns KEY (account_id)
    SOURCE KEY (owner_id) REFERENCES Person (id)
    DESTINATION KEY (account_id) REFERENCES Account (id)
    LABEL Owns PROPERTIES (since)
);

Graphs you’ve created show in the schema tree under their dataset. Opening one gives a details tab with the DDL, node and edge tables, labels, and a connectivity summary.

MEASURE properties

MEASURE(aggregate) AS alias inside PROPERTIES (…) declares a per-key aggregate as part of the schema; the grouping by the table’s KEY is implicit. Allowed aggregates: SUM, AVG, COUNT (including COUNT(DISTINCT …)), MIN, MAX; the alias is required.

CREATE PROPERTY GRAPH UniversityGraph
NODE TABLES (
  Department KEY (dept_id)
    LABEL Department PROPERTIES (
      dept_id, dept_name, budget,
      MEASURE(SUM(budget)) AS total_budget,
      MEASURE(COUNT(*)) AS dept_count
    ),
  Course KEY (course_id)
    LABEL Course PROPERTIES (
      course_id, enrollment,
      MEASURE(AVG(enrollment)) AS avg_enrollment
    )
);

Measures can’t be read from MATCH / RETURN. Read them through GRAPH_EXPAND, which flattens the graph into Label_column columns, wrapping each measure in AGG(…):

SELECT
Department_dept_name,
AGG(Department_total_budget) AS dept_budget,
AGG(Course_avg_enrollment)   AS avg_class_size
FROM GRAPH_EXPAND("UniversityGraph")
GROUP BY Department_dept_name
ORDER BY dept_budget DESC

Inside PROPERTIES (…), typing M offers one MEASURE(SUM(column)) AS … snippet per allowed aggregate; inside SELECT … FROM GRAPH_EXPAND("g") the column list is the graph’s Label_column names. A window function, a nested MEASURE, or any other function inside MEASURE() is an error.

Converting JOINs to GQL and back

Two code actions, in the right-click menu and the quick-fix lightbulb. Both need the graph’s schema, which Querylab.io reads from your project.

• Convert to GQL GRAPH_TABLE syntax is offered on a SELECT with at least one JOIN whose tables are node or edge tables of a known graph.
• Convert GRAPH_TABLE to standard SQL JOIN is offered with the cursor inside a GRAPH_TABLE(…), or a standalone GRAPH … query.

SELECT p1.name AS sender, p2.name AS receiver, t.amount
FROM Person AS p1
JOIN Transfers AS t ON p1.id = t.source_id
JOIN Person AS p2 ON t.dest_id = p2.id
WHERE t.amount > 500
ORDER BY t.amount DESC
LIMIT 100

SELECT * FROM GRAPH_TABLE(
FinGraph
MATCH (p1:Person)-[t:Transfers]->(p2:Person)
WHERE t.amount > 500
RETURN p1.name AS sender, p2.name AS receiver, t.amount
ORDER BY t.amount DESC
LIMIT 100
)

Set operations, subqueries and PIVOT in the FROM clause, and CTEs other than a recursive path CTE don’t convert; the action isn’t offered. GQL → SQL accepts all four input shapes (bare GRAPH …, GRAPH_TABLE(…), SELECT * FROM GRAPH_TABLE(…), and a SELECT with its own projection) and keeps any trailing WHERE, ORDER BY, LIMIT, OFFSET unchanged.

The same converter, with an editable schema DDL, runs without an account at querylab.io/tools/gql-to-sql.

Completing keywords, labels, and properties

Keyword completions follow the statement grammar: after GRAPH name you get MATCH; after a MATCH you get OPTIONAL MATCH, FILTER, LET, WITH, FOR, ORDER BY, RETURN; inside () and [] you get the label separator, and after RETURN or FILTER the pattern variables.

With Settings → Editor → SQL Editor → Enable Graph Schema Completions on, Querylab.io also fetches property-graph metadata from INFORMATION_SCHEMA, and then labels complete after the colon and properties after a variable’s dot. This needs a BigQuery Enterprise edition.

GRAPH FinGraph
MATCH (p:Person)-[t:Transfers]->(q:Person)
RETURN p.

Errors the editor reports

Viewing results as a graph

When a result column holds graph elements (the JSON BigQuery produces for TO_JSON(node) or TO_JSON(edge), with "kind": "node" or "kind": "edge"), the result panel adds Mode: Graph next to Table and Json.

SELECT TO_JSON(n) AS node, TO_JSON(e) AS edge, TO_JSON(m) AS target
FROM GRAPH_TABLE(
FinGraph
MATCH (n:Person)-[e:Transfers]->(m:Person)
RETURN n, e, m
)

Drag nodes to rearrange, scroll to zoom, drag the background to pan. Zoom and fit-to-view controls sit bottom-left; a minimap appears bottom-right once the graph has more than six nodes.