Spanner Graph queries overview

This document provides an overview using the graph query language with Spanner Graph, including its syntax for graph pattern matching, and shows you how to run queries against your graph. With Spanner Graph, you can run queries to find patterns, traverse relationships, and gain insights from your property graph data.

The examples in this document use the graph schema that you create in Set up and query Spanner Graph. This schema is illustrated in the following diagram:

Run a Spanner Graph query

You can use the Google Cloud console, Google Cloud CLI, client libraries, the REST API, or the RPC API to run a Spanner Graph query.

Visualize Spanner Graph query results

You can view a visual representation of your Spanner Graph query results in Spanner Studio in the Google Cloud console. A query visualization lets you see how the returned elements (nodes and edges) are connected. This can reveal patterns, dependencies, and anomalies that are difficult to see when you view the results in a table. To view a visualization of a query, the query must return full nodes in JSON format. Otherwise, you can see the query results in only tabular format. For more information, see Use Spanner Graph query visualizations.

Spanner Graph query structure

A Spanner Graph query consists of several components, such as the property graph name, node and edge patterns, and quantifiers. You use these components to create a query that finds specific patterns in your graph. Each component is described in the Graph pattern matching section of this document.

The query in Figure 2 demonstrates the basic structure of a Spanner Graph query. The query starts by specifying the target graph, FinGraph, using the GRAPH clause. The MATCH clause then defines the pattern to search for. In this case, it's a Person node connected to an Account node through an Owns edge. The RETURN clause specifies which properties of the matched nodes to return.

Graph pattern matching

Graph pattern matching finds specific patterns within your graph. The most basic patterns are element patterns, such as node patterns that match nodes and edge patterns that match edges.

Node patterns

A node pattern matches nodes in your graph. This pattern contains matching parentheses, which might optionally include a graph pattern variable, a label expression, and property filters.

Find all nodes

The following query returns all nodes in the graph. The variable n, a graph pattern variable, binds to the matching nodes. In this case, the node pattern matches all nodes in the graph.

GRAPH FinGraph MATCH (n) RETURN LABELS(n) AS label, n.id; 

This query returns label and id:

Find all nodes with a specific label

The following query matches all nodes in the graph that have the Person label. The query returns the label and the id, name properties of the matched nodes.

GRAPH FinGraph MATCH (p:Person) RETURN LABELS(p) AS label, p.id, p.name; 

This query returns the following properties of the matched nodes:

Find all nodes matching a label expression

You can create a label expression with one or more logical operators. For example, the following query matches all nodes in the graph that have either the Person or Account label. The graph pattern variable n exposes all properties from nodes with the Person or Account label.

GRAPH FinGraph MATCH (n:Person|Account) RETURN LABELS(n) AS label, n.id, n.birthday, n.create_time; 

In the following results of this query:

• All nodes have the id property.
• Nodes matching the Account label have the create_time property, but don't have the birthday property. The birthday property is NULL for these nodes.
• Nodes matching the Person label have the birthday property, but don't have the create_time property. The create_time property is NULL for these nodes.

Find all nodes matching the label expression and property filter

This query matches all nodes in the graph that have the Person label and where the property id is equal to 1.

GRAPH FinGraph MATCH (p:Person {id: 1}) RETURN LABELS(p) AS label, p.id, p.name, p.birthday; 

Here are the query results:

You can use the WHERE clause to form more complex filtering conditions on labels and properties.

The following query uses the WHERE clause to form a more complex filtering condition on properties. It matches all nodes in the graph that have the Person label, and the property birthday is before 1990-01-10.

GRAPH FinGraph MATCH (p:Person WHERE p.birthday < '1990-01-10') RETURN LABELS(p) AS label, p.name, p.birthday; 

Here are the query results:

Edge patterns

An edge pattern matches edges or relationships between nodes. Edge patterns are enclosed in square brackets ([]) and include symbols such as -, ->, or <- to indicate directions. An edge pattern might optionally include a graph pattern variable to bind to matching edges.

Find all edges with matching labels

This query returns all edges in the graph with the Transfers label. The query binds the graph pattern variable e to the matching edges.

GRAPH FinGraph MATCH -[e:Transfers]-> RETURN e.Id as src_account, e.order_number 

Here are the query results:

Find all edges matching the label expression and property filter

This query's edge pattern uses a label expression and a property filter to find all edges labeled with Transfers that match a specified order_number.

GRAPH FinGraph MATCH -[e:Transfers {order_number: "304120005529714"}]-> RETURN e.Id AS src_account, e.order_number 

Here are the query results:

Find all edges using any direction edge pattern

You can use the any direction edge pattern (-[]-) in a query to match edges in either direction. The following query finds all transfers with a blocked account.

GRAPH FinGraph MATCH (account:Account)-[transfer:Transfers]-(:Account {is_blocked:true}) RETURN transfer.order_number, transfer.amount; 

Here are the query results:

Path patterns

A path pattern is built from alternating node and edge patterns.

Find all paths from a specific node using a path pattern

The following query finds all transfers to an account initiated from an account owned by Person with id equal to 2.

Each matched result represents a path from Person {id: 2} through a connected Account using the Owns edge, into another Account using the Transfers edge.

GRAPH FinGraph MATCH  (p:Person {id: 2})-[:Owns]->(account:Account)-[t:Transfers]->  (to_account:Account) RETURN  p.id AS sender_id, account.id AS from_id, to_account.id AS to_id; 

Here are the query results:

Quantified path patterns

A quantified pattern repeats a pattern within a specified range.

Match a quantified edge pattern

To find paths of a variable length, you can apply a quantifier to an edge pattern. The following query demonstrates this by finding destination accounts that are one to three transfers away from a source Account with an id of 7.

The query applies the quantifier {1, 3} to the edge pattern -[e:Transfers]->. This instructs the query to match paths that repeat the Transfers edge pattern one, two, or three times. The WHERE clause is used to exclude the source account from the results. The ARRAY_LENGTH function is used to access the group variable e. For more information, see access group variable.

GRAPH FinGraph MATCH (src:Account {id: 7})-[e:Transfers]->{1, 3}(dst:Account) WHERE src != dst RETURN src.id AS src_account_id, ARRAY_LENGTH(e) AS path_length, dst.id AS dst_account_id; 

Here are the query results:

Some rows in the results are repeated. This is because multiple paths that match the pattern can exist between the same source and destination nodes, and the query returns all of them.

Match a quantified path pattern

The following query finds paths between Account nodes with one to two Transfers edges through intermediate accounts that are blocked.

The parenthesized path pattern is quantified, and its WHERE clause specifies conditions for the repeated pattern.

GRAPH FinGraph MATCH  (src:Account)  ((a:Account)-[:Transfers]->(b:Account {is_blocked:true}) WHERE a != b){1,2}  -[:Transfers]->(dst:Account) RETURN src.id AS src_account_id, dst.id AS dst_account_id; 

Here are the query results:

Group variables

A graph pattern variable declared in a quantified pattern becomes a group variable when accessed outside that pattern. It then binds to an array of matched graph elements.

You can access a group variable as an array. Its graph elements are preserved in the order of their appearance along the matched paths. You can aggregate a group variable using horizontal aggregation.

Access group variable

In the following example, the variable e is accessed as follows:

• A graph pattern variable bound to a single edge in the WHERE clause e.amount > 100 when it's within the quantified pattern.
• A group variable bound to an array of edge elements in ARRAY_LENGTH(e) in the RETURN statement when it's outside the quantified pattern.
• A group variable bound to an array of edge elements, which is aggregated by SUM(e.amount) outside the quantified pattern. This is an example of horizontal aggregation.

GRAPH FinGraph MATCH  (src:Account {id: 7})-[e:Transfers WHERE e.amount > 100]->{0,2}  (dst:Account) WHERE src.id != dst.id LET total_amount = SUM(e.amount) RETURN  src.id AS src_account_id, ARRAY_LENGTH(e) AS path_length,  total_amount, dst.id AS dst_account_id; 

Here are the query results:

Path search prefixes

To limit matched paths within groups that share source and destination nodes, you can use the ANY or ANY SHORTEST path search prefix. You can only apply these prefixes before an entire path pattern, and you can't apply them inside parentheses.

Match using ANY

The following query finds all reachable unique accounts that are one or two Transfers away from a given Account node.

The ANY path search prefix ensures that the query returns only one path between a unique pair of src and dst Account nodes. In the following example, although you can reach the Account node with {id: 16} in two different paths from the source Account node, the query returns only one path.

GRAPH FinGraph MATCH ANY (src:Account {id: 7})-[e:Transfers]->{1,2}(dst:Account) LET ids_in_path = ARRAY_CONCAT(ARRAY_AGG(e.Id), [dst.Id]) RETURN src.id AS src_account_id, dst.id AS dst_account_id, ids_in_path; 

Here are the query results:

Graph patterns

A graph pattern consists of one or more path patterns, separated by a comma (,). Graph patterns can contain a WHERE clause, which lets you access all the graph pattern variables in the path patterns to form filtering conditions. Each path pattern produces a collection of paths.

Match using a graph pattern

The following query identifies intermediary accounts and their owners involved in transactions amounts exceeding 200, through which funds are transferred from a source account to a blocked account.

The following path patterns form the graph pattern:

• The first pattern finds paths where the transfer occurs from one account to a blocked account using an intermediate account.
• The second pattern finds paths from an account to its owning person.

The variable interm acts as a common link between the two path patterns, which requires interm to reference the same element node in both path patterns. This creates an equi-join operation based on the interm variable.

GRAPH FinGraph MATCH  (src:Account)-[t1:Transfers]->(interm:Account)-[t2:Transfers]->(dst:Account),  (interm)<-[:Owns]-(p:Person) WHERE dst.is_blocked = TRUE AND t1.amount > 200 AND t2.amount > 200 RETURN  src.id AS src_account_id, dst.id AS dst_account_id,  interm.id AS interm_account_id, p.id AS owner_id; 

Here are the query results:

Linear query statements

You can chain multiple graph statements together to form a linear query statement. The statements are executed in the same order as they appear in the query.

• Each statement takes the output from the previous statement as input. The input is empty for the first statement.

• The output of the last statement is the final result.

For example, you can use linear query statements to find the maximum transfer to a blocked account. The following query finds the account and its owner with the largest outgoing transfer to a blocked account.

GRAPH FinGraph MATCH (src_account:Account)-[transfer:Transfers]->(dst_account:Account {is_blocked:true}) ORDER BY transfer.amount DESC LIMIT 1 MATCH (src_account:Account)<-[owns:Owns]-(owner:Person) RETURN src_account.id AS account_id, owner.name AS owner_name; 

The following table illustrates this process by showing the intermediate results passed between each statement. For brevity, only some properties are shown.

 MATCH (src_account:Account) -[transfer:Transfers]-> (dst_account:Account {is_blocked:true}) 

 ORDER BY transfer.amount DESC 

 LIMIT 1 

 MATCH (src_account:Account) <-[owns:Owns]- (owner:Person) 

 RETURN src_account.id AS account_id, owner.name AS owner_name 

Here are the query results:

Return statement

The RETURN statement specifies what to return from the matched patterns. It can access graph pattern variables and include expressions and other clauses, such as ORDER BY and GROUP BY.

Spanner Graph doesn't support returning graph elements as query results. To return the entire graph element, use the TO_JSON function or SAFE_TO_JSON function. Of these two functions, we recommend that you use SAFE_TO_JSON.

Return graph elements as JSON

GRAPH FinGraph MATCH (n:Account {id: 7}) -- Returning a graph element in the final results is NOT allowed. Instead, use -- the TO_JSON function or explicitly return the graph element's properties. RETURN TO_JSON(n) AS n; 

GRAPH FinGraph MATCH (n:Account {id: 7}) -- Certain fields in the graph elements, such as TOKENLIST, can't be returned -- in the TO_JSON function. In those cases, use the SAFE_TO_JSON function instead. RETURN SAFE_TO_JSON(n) AS n; 

Here are the query results:

Composing larger queries with NEXT keyword

You can chain multiple graph linear query statements using the NEXT keyword. The first statement receives an empty input, and the output of each subsequent statement becomes the input for the next.

The following example finds the owner of the account with the most incoming transfers by chaining multiple graph linear statements. You can use the same variable, for example, account, to refer to the same graph element across multiple linear statements.

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

Here are the query results:

Functions and expressions

You can use all GoogleSQL functions (both aggregate and scalar functions), operators, and conditional expressions in Spanner Graph queries. Spanner Graph also supports graph-specific functions and operators.

Built-in functions and operators

The following functions and operators are used in GQL:

• PROPERTY_EXISTS(n, birthday): Returns whether n has the birthday property.
• LABELS(n): Returns the labels of n as defined in the graph schema.
• PROPERTY_NAMES(n): Returns the property names of n.
• TO_JSON(n): Returns n in JSON format. For more information, see the TO_JSON function.

the PROPERTY_EXISTS predicate, LABELS function, and TO_JSON function, as well as other built-in functions like ARRAY_AGG and CONCAT.

GRAPH FinGraph MATCH (person:Person)-[:Owns]->(account:Account) RETURN person, ARRAY_AGG(account.nick_name) AS accounts GROUP BY person NEXT RETURN  LABELS(person) AS labels,  TO_JSON(person) AS person,  accounts,  CONCAT(person.city, ", ", person.country) AS location,  PROPERTY_EXISTS(person, is_blocked) AS is_blocked_property_exists,  PROPERTY_EXISTS(person, name) AS name_property_exists LIMIT 1; 

Here are the query results:

Subqueries

A subquery is a query nested in another query. The following lists Spanner Graph subquery rules:

• A subquery is enclosed within a pair of braces {}.
• A subquery might start with the leading GRAPH clause to specify the graph in scope. The specified graph doesn't need to be the same as the one used in the outer query.
• When the GRAPH clause is omitted in the subquery, the following occurs:
  • The graph in scope is inferred from the closest outer query context.
  • The subquery must start from a graph pattern matching statement with MATCH.
• A graph pattern variable declared outside the subquery scope can't be declared again inside the subquery, but it can be referred to in expressions or functions inside the subquery.

Use a subquery to find the total number of transfers from each account

The following query illustrates the use of the VALUE subquery. The subquery is enclosed in braces {} prefixed by the VALUE keyword. The query returns the total number of transfers initiated from an account.

GRAPH FinGraph MATCH (p:Person)-[:Owns]->(account:Account) RETURN p.name, account.id AS account_id, VALUE {  MATCH (a:Account)-[transfer:Transfers]->(:Account)  WHERE a = account  RETURN COUNT(transfer) AS num_transfers } AS num_transfers; 

Here are the query results:

For a list of supported subquery expressions, see Spanner Graph subqueries.

Use a subquery to find accounts owned by each person

The following query uses the CALL statement with an inline subquery. The MATCH (p:Person) statement creates a table with a single column named p. Each row in this table contains a Person node. The CALL (p) statement executes the enclosed subquery for each row in this working table. The subquery finds accounts owned by each matched person p. Multiple accounts for the same person are ordered by account ID.

The example declares the outer-scoped node variable p from the MATCH (p:Person) clause. The CALL (p) statement references this variable. This declaration lets you redeclare or multiply-declare the node variable in a path pattern of the subquery. This ensures that the inner and outer p node variables bind to the same Person node in the graph. If the CALL statement doesn't declare the node variable p, the subquery treats the redeclared variable p as a new variable. This new variable is independent of the outer-scoped variable, and the subquery doesn't multiply-declare it because it returns different results. For more information, see CALL statement.

GRAPH FinGraph MATCH (p:Person) CALL (p) {  MATCH (p)-[:Owns]->(a:Account)  RETURN a.Id AS account_Id  ORDER BY account_Id } RETURN p.name AS person_name, account_Id ORDER BY person_name, account_Id; 

Result

Query parameters

You can query Spanner Graph with parameters. For more information, see the syntax and learn how to query data with parameters in the Spanner client libraries.

The following query illustrates the use of query parameters.

GRAPH FinGraph MATCH (person:Person {id: @id}) RETURN person.name; 

Query graphs and tables together

You can use Graph queries in conjunction with SQL to access information from your Graphs and Tables together in a single statement.

The GRAPH_TABLE operator takes a linear graph query and returns its result in a tabular form that can be integrated into a SQL query. This interoperability lets you enrich graph query results with non-graph content and the other way around.

For example, you can create a CreditReports table and insert a few credit reports, as shown in the following example:

CREATE TABLE CreditReports (  person_id INT64 NOT NULL,  create_time TIMESTAMP NOT NULL,  score INT64 NOT NULL, ) PRIMARY KEY (person_id, create_time); 

INSERT INTO CreditReports (person_id, create_time, score) VALUES  (1,"2020-01-10 06:22:20.222", 700),  (2,"2020-02-10 06:22:20.222", 800),  (3,"2020-03-10 06:22:20.222", 750); 

Next, you can identify specific persons through graph pattern matching in GRAPH_TABLE and join the graph query results with the CreditReports table to retrieve credit scores.

SELECT  gt.person.id,  credit.score AS latest_credit_score FROM GRAPH_TABLE(  FinGraph  MATCH (person:Person)-[:Owns]->(:Account)-[:Transfers]->(account:Account {is_blocked:true})  RETURN DISTINCT person ) AS gt JOIN CreditReports AS credit  ON gt.person.id = credit.person_id ORDER BY credit.create_time; 

Here are the query results:

What's next

Learn best practices for tuning queries.