Bigquery Graph

Graph Query Language (GQL) Query Generation Guidelines

You are querying a property graph consisting of nodes and edges. You MUST exclusively use the BigQuery GoogleSQL GQL standard, which is the only supported graph query language and implements the ISO GQL standard.

You MUST NEVER, under any circumstances, generate or consider Cypher queries. Any deviation from the BigQuery GoogleSQL GQL standard is strictly prohibited.

Reference Directory

• Semantic Graph Guidelines: Guidelines for generating SQL queries against a semantic graph.
• Graph Schema Best Practices: Best practices for defining BigQuery property graph schemas.
• Graph Schema DDL Reference: DDL syntax reference for property graphs.
• Graph Limitations & Feature Parity: Current limitations and feature parity for BigQuery Graph.
• Graph Schema DDL Advisor: Assists in defining and optimizing schemas.

Pre-generation Checklist

Before generating any GQL, you MUST:

1. Identify Output Intent: Determine if the user intends to visualize a graph network (requires TO_JSON()) or view tabular data (requires specific properties).
2. Verify Language Standard: Confirm the query will use BigQuery GoogleSQL GQL. NEVER use Cypher.

Core Directives for Agent Query Generation

When generating graph queries, you must adhere to the following global directives:

1. Default Query Construction (Standalone GQL): Write standalone GQL queries using the RETURN statement natively. Explicitly avoid using the GRAPH_TABLE table-valued function unless the user constraints actively require standard SQL relational integration or aggregation.
2. Keyword Escaping: You MUST enforce backticks (`) around any reserved SQL and GQL keywords such as 'order', 'begin' and 'path' used as identifiers (e.g., column names, label names, variable names).
3. Strictly Follow Graph Schema: Ensure all labels (e.g., :Person, :Account) and properties (e.g., n.id, e.amount) used in the query strictly match the provided graph schema. Do NOT guess or hallucinate schema elements.
4. Result Uniqueness: Use the DISTINCT keyword automatically in your RETURN or COLUMNS clause if the user prompt implies they want to retrieve unique information.
5. Graph Path Variables: When a query involves "paths", "path traversal", "path finding", or finding relationships between nodes, you MUST assign the matched pattern to a path variable (e.g., MATCH p = ...).

Basic GQL Query Construction

A linear query statement in BigQuery GQL executes clauses sequentially. The output of one clause provides the input (the "working table") to the next.

Common sequential statements include:

• MATCH: Identifies topological patterns in the graph.
• WITH: Projects variables from current scope into the next scope, optionally sorting, limiting or grouping.
• LET: Defines a new variable or alias within the query scope.
• FILTER: Filters intermediate graph mappings.
• RETURN: Ends a GQL query or subquery, projecting the final graph variables.
• ORDER BY, LIMIT, OFFSET: Control sorting and pagination.

Chaining with NEXT: Multiple linear statements can be composed into a compound query using the NEXT keyword. The results of the first statement pipe into the statement following NEXT.

Example 1: Sequential Statements

This query demonstrates filtering and projecting data through a single linear statement sequence.

GRAPH <project>.<dataset>.<graph>
MATCH (src:Account)-[t:Transfers]->(dst:Account)
LET transfer_amount = t.amount
FILTER transfer_amount > 1000
WITH src, dst, transfer_amount
ORDER BY transfer_amount DESC
LIMIT 50
RETURN src.id AS source, dst.id AS destination, transfer_amount

Example 2: Chaining with NEXT

This query demonstrates using NEXT to pipe the results of one graph pattern match into a subsequent pattern match.

GRAPH <project>.<dataset>.<graph>
MATCH (blocked:Account WHERE blocked.is_frozen = true)
RETURN blocked.id AS frozen_id
NEXT
MATCH (a:Account)-[t:Transfers]->(b:Account)
FILTER a.id = frozen_id
RETURN a.id AS source, b.id AS destination, t.amount AS amount

Graph Pattern Matching

A graph pattern matches topologies within a BigQuery property graph. Patterns consist of vertices (nodes) and connecting edges.

Node Patterns

Node patterns are enclosed in parentheses (). They identify entities in the graph and can optionally bind to a variable or specify label and property filters.

• MATCH (n): Matches any node and binds it to the variable n.
• MATCH (p:Person): Matches nodes explicitly labeled with Person.
• MATCH (p:Person|Account): Uses a label expression | (OR) to match nodes that have either the Person or Account label.
• MATCH (p:Person {id: 1}): Matches nodes that satisfy a specific property filter.
• MATCH (p:Person WHERE p.age > 18): Matches nodes applying a WHERE condition on properties.

Important Note on Label Expressions: BigQuery matches a node if it possesses any of the labels listed in an OR (|) expression. You cannot use & directly in label expressions.

Edge Patterns

Edge patterns represent the relationships between nodes. They are enclosed in square brackets [] and connected using arrows (-, ->, <-) to denote directionality.

• MATCH (a)-[e]->(b): Matches any directed edge from a to b, binding the edge to e.
• MATCH (a)-[e:Transfers]->(b): Directed edge specifically labeled Transfers.
• MATCH (a)-[e:Transfers {amount: 50}]->(b): Edge with a specific property filter applied.
• MATCH (a)-[e:Transfers]-(b): Matches an undirected (any direction) edge between a and b. Use preferred explicit direction when possible for better performance.

Pattern Joins and Commas

A complex graph pattern consists of one or more path patterns separated by commas ,. When multiple comma-separated patterns are used:

• If they do not share any variables, they result in a cross join.
• If they share a common variable, BigQuery automatically performs an equijoin on that variable.

-- Equijoin example where 'interim' connects the two paths
GRAPH <project>.<dataset>.<graph>
MATCH (src:Account)-[t1:Transfers]->(interim:Account),
      (interim)<-[:Owns]-(p:Person)
RETURN src.id AS account_id, p.name AS owner_name

Variable-Length Paths and Quantifiers

You can find multi-hop connections by appending a quantifier to an edge pattern, defining variable-length paths. - {m, n}: Specifies that the edge pattern must be repeated between m and n times (e.g., {1,3}).

Group Variables: When an edge variable is quantified (e.g., [e:Transfers]->{1,3}), the variable e becomes a "group variable." This represents an array of the matched edges in the path. You must use array functions to interact with it, such as ARRAY_LENGTH(e) or horizontal aggregation like SUM(e.amount).

Path Search Prefixes

Variable-length paths can result in exponential combinations and repeating paths. You can constrain the search between source and destination pairs using search prefixes placed immediately before the path pattern:

• ANY: Returns exactly one arbitrary matching path between each unique pair of source and destination nodes.
• ANY SHORTEST: Returns a single path for each unique pair, specifically choosing from those with the minimum number of edges (hops).
• ANY CHEAPEST: Returns a single path with the minimum total cost, computed by aggregating COST expressions defined on the edges.

GRAPH <project>.<dataset>.<graph>
MATCH ANY SHORTEST
  (a:Account {id: 123})-[e:Transferred]->{1,3}(b:Account {id: 456})
RETURN e

GQL Functions and Operators

BigQuery property graphs support specialized native functions for interrogating graph elements and extracting path metadata. These functions can be used directly within MATCH, WHERE, LET, and RETURN/COLUMNS clauses.

Path Extraction Functions

When an entire path pattern is bound to a variable (e.g., MATCH p = (...)), you can extract specific metadata and elements from it:

• PATH_FIRST(p): Extracts and returns the starting node of path p.
• PATH_LAST(p): Extracts and returns the terminal (ending) node of path p.
• PATH_LENGTH(p): Returns an INT64 count representing the number of edge hops in path p.
• NODES(p): Returns an array of node elements, ordered by their sequence in the path.
• EDGES(p): Returns an array of edge elements, ordered by their sequence in the path.

GRAPH <project>.<dataset>.<graph>
MATCH p = (a:Account)-[t:Transfers]->{1,3}(b:Account)
RETURN PATH_LENGTH(p) AS hops, TO_JSON(NODES(p)) AS path_nodes

Element Traversal and Inspection Functions

These functions operate on individual node or edge element variables:

• DESTINATION_NODE_ID(e): Retrieves the unique internal string identifier of an edge e's destination node.
• SOURCE_NODE_ID(e): Retrieves the unique internal string identifier of an edge e's source node.
• ELEMENT_ID(x): Returns the unique internal identifier for the given node or edge x.
• LABELS(x): Returns an array of string labels bound to a node or edge element x.

Output Formatting: Graph Visualization vs. Tabular Data

When constructing the RETURN clause, strictly distinguish between graph visualization intent and tabular data intent based on the user's objective.

Path Variables

You can assign an entire matched pattern sequence to a path variable using the assignment operator =. This allows you to reference the entire topological sequence later in the query.

MATCH p = (a:Person)-[e:Knows]->(b:Person)

In this example, p represents the full path, encapsulating the nodes a and b and the edge e.

1. Graph Visualization Intent

Use this when the user wants to see relationships, paths, topology, networks, connectivity or entire entities (nodes/edges) as a whole.

• Trigger & Keywords: "visualize", "show the graph", "network", "connections", "find the path", "relationship between X and Y".
• Default JSON Serialization (TO_JSON): Unless specific properties (e.g., n.name) or path metrics (e.g., PATH_LENGTH(p)) are explicitly requested, you MUST wrap all graph topology outputs (nodes, edges, and path variables) in the standard TO_JSON() function. This ensures compatibility with graphing UI components that expect full JSON objects.
• Example: RETURN TO_JSON(src) AS source, TO_JSON(p) AS full_path
• Limit: Always append LIMIT 500 to the query to prevent overwhelming the UI with too many nodes/edges, unless the user explicitly requests a different number.

GRAPH <project>.<dataset>.<graph>
MATCH p = (src:Person)-[e:Knows]->(dst:Person)
RETURN
    TO_JSON(src) AS source_node,
    TO_JSON(e) AS relationship,
    TO_JSON(dst) AS destination_node,
    TO_JSON(p) AS full_path
LIMIT 500