This document describes how to query property graphs in Spanner Graph. The examples in this section use the graph schema that you create in Set up and query Spanner Graph, which is illustrated in the following diagram:
Run a Spanner Graph query
You can run Spanner Graph queries in the following ways:
The Google Cloud console
Submit a query on the Spanner Studio page. To access the Spanner Studio page, click Spanner Studio from the Database overview page or Table overview page. For more information about accessing Spanner Studio, see Manage your data using the Google Cloud console.
The
gcloud spanner
command-line toolSubmit a command by using the
gcloud spanner databases execute-sql
command.The
executeSql
andexecuteStreamingSql
REST APIThe
ExecuteSql
andExecuteStreamingSql
RPC API
Spanner Graph query structure
This section describes each query component in detail.
The following example illustrates the basic structure of a Spanner Graph query.
Spanner Graph lets you create multiple graphs inside a database.
The query starts by specifying the target graph, FinGraph
, using
the GRAPH
clause.
Graph pattern matching
Graph pattern matching finds specific patterns within your graph. The most basic patterns are Element patterns (node patterns and edge patterns), which match graph elements (nodes and edges, respectively). Element patterns can be composed into path patterns and more complex patterns.
Node patterns
A node pattern is a pattern that matches nodes from your graph. This pattern comprises a matching pair of parentheses, which might optionally contain 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
, called
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;
Result
The query returns label
and id
as follows:
label | id |
---|---|
Account | 7 |
Account | 16 |
Account | 20 |
Person | 1 |
Person | 2 |
Person | 3 |
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 id
, name
properties of the matched nodes.
GRAPH FinGraph
MATCH (p:Person)
RETURN LABELS(p) AS label, p.id, p.name;
Result
label | id | name |
---|---|---|
Person | 1 | Alex |
Person | 2 | Dana |
Person | 3 | Lee |
Find all nodes matching a label expression
You can create a label expression with one or more logical operators.
The following query matches all nodes in the graph that have either the
Person
or Account
label. The set of properties exposed by
the graph pattern variable n
is the superset of the properties exposed by the
nodes that have either 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 results, all nodes have the
id
property. - Nodes matching
Account
label have thecreate_time
property, but don't have thebirthday
property. ANULL
is returned for thebirthday
property for such nodes. - Nodes matching
Person
label have thebirthday
property, but don't have thecreate_time
property. ANULL
is returned for thecreate_time
property for such nodes.
Result
label | id | birthday | create_time |
---|---|---|---|
Account | 7 | NULL | 2020-01-10T14:22:20.222Z |
Account | 16 | NULL | 2020-01-28T01:55:09.206Z |
Account | 20 | NULL | 2020-02-18T13:44:20.655Z |
Person | 1 | 1991-12-21T08:00:00Z | NULL |
Person | 2 | 1980-10-31T08:00:00Z | NULL |
Person | 3 | 1986-12-07T08:00:00Z | NULL |
For more information on label expression rules, see Label expression.
Find all nodes matching the label expression and property filter
The following 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;
Result
label | id | name | birthday |
---|---|---|---|
Person | 1 | Alex | 1991-12-21T08:00:00Z |
You can use the WHERE
clause to form more complex filtering conditions on
labels and properties.
The following query 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;
Result
label | name | birthday |
---|---|---|
Person | Dana | 1980-10-31T08:00:00Z |
Person | Lee | 1986-12-07T08:00:00Z |
Edge patterns
An edge pattern matches edges or relationships between nodes. Edge patterns are
enclosed with square brackets []
with symbols -
, ->
, or <-
to indicate
directions.
Similar to node patterns, graph pattern variables are used to bind to matching edge elements.
Find all edges with matching labels
The following query returns all edges in the graph that have the Owns
label.
The graph pattern variable e
is bound to the matching edges.
GRAPH FinGraph
MATCH -[e:Owns]->
RETURN e.id AS owner_id, e.account_id;
Result
owner_id | account_id |
---|---|
1 | 7 |
3 | 16 |
2 | 20 |
Find all edges matching the label expression and property filter
Similar to a node pattern, an edge pattern can use
label expressions, property specification, and WHERE
clauses, as shown in the
following query. The query finds all edges labeled with Owns
and has the
property create_time
in a specified period.
GRAPH FinGraph
MATCH -[e:Owns WHERE e.create_time > '2020-01-14'
AND e.create_time < '2020-05-14']->
RETURN e.id AS owner_id, e.create_time, e.account_id;
Result
owner_id | create_time | account_id |
---|---|---|
2 | 2020-01-28T01:55:09.206Z | 20 |
3 | 2020-02-18T13:44:20.655Z | 16 |
Find all edges using any direction edge pattern
Although all edges in Spanner Graph are directed, you can use the
any direction
edge pattern -[]-
in a query to match edges in either direction.
The following query finds all transfers where a blocked account is involved.
GRAPH FinGraph
MATCH (account:Account)-[transfer:Transfers]-(:Account)
WHERE account.is_blocked
RETURN transfer.order_number, transfer.amount;
Result
order_number | amount |
---|---|
304330008004315 | 300 |
304120005529714 | 100 |
103650009791820 | 300 |
302290001255747 | 200 |
Path patterns
A path pattern is built from alternating node and edge patterns.
Find all paths from a node with specified label and property filters, 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;
Result
sender_id | from_id | to_id |
---|---|---|
2 | 20 | 7 |
2 | 20 | 16 |
Quantified path patterns
A quantified pattern allows a pattern to be repeated within a specified range.
Match a quantified edge pattern
The following query finds all the destination accounts one to three
transfers away from a source Account
with id
equal to 7
, other than
itself.
The edge pattern postfixed with the quantifier {1, 3}
.
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;
Result
src_account_id | path_length | dst_account_id |
---|---|---|
7 | 1 | 16 |
7 | 1 | 16 |
7 | 1 | 16 |
7 | 3 | 16 |
7 | 3 | 16 |
7 | 2 | 20 |
7 | 2 | 20 |
The previous example uses the ARRAY_LENGTH
function to access the
group variable
e
. For
more information, see access group variable.
Some rows in the example results are repeated because there might be multiple
paths between the same pair of src
and dst
accounts that match the pattern.
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 the WHERE
clause is used in
the parenthesis to specify conditions for the repeated pattern.
GRAPH FinGraph
MATCH
(src:Account)
((:Account)-[:Transfers]->(interm:Account) WHERE interm.is_blocked){1,2}
-[:Transfers]->(dst:Account)
RETURN src.id AS src_account_id, dst.id AS dst_account_id;
Result
src_account_id | dst_account_id |
---|---|
7 | 20 |
7 | 20 |
20 | 20 |
Group variables
A graph pattern variable declared in a quantified pattern is considered a group variable when accessed outside the quantified pattern, and it binds to an array of matched graph elements.
You can access a group variable as an array where graph elements are preserved in the order of 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 the following:
- A graph pattern variable bound to a single edge in the
WHERE
clausee.amount > 100
(within the quantified pattern). - A group variable bound to an array of edge elements in
ARRAY_LENGTH(e)
in theRETURN
statement (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;
Result
src_account_id | path_length | total_amount | dst_account_id |
---|---|---|---|
7 | 1 | 300 | 16 |
7 | 2 | 600 | 20 |
Any and Any Shortest paths
To limit the matched paths in each group of paths sharing the same 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 which are one or two
Transfers
away from a given Account
node.
The ANY
path search prefix ensures that only one path between a unique
pair of src
and dst
Account
nodes is returned. In the following example,
although you can reach the Account
node with {id: 16}
in two different
paths from the source Account
node, the results include only one path.
GRAPH FinGraph
MATCH ANY (src:Account {id: 7})-[e:Transfers]->{1,2}(dst:Account)
LET ids_in_path = ARRAY(SELECT e.to_id FROM UNNEST(e) AS e)
RETURN src.id AS src_account_id, dst.id AS dst_account_id, ids_in_path;
Result
src_account_id | dst_account_id | ids_in_path |
---|---|---|
7 | 16 | 16 |
7 | 20 | 16,20 |
Graph patterns
A graph pattern consists of one or more path patterns, separated by 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;
Result
src_account_id | dst_account_id | interm_account_id | owner_id |
---|---|---|---|
20 | 16 | 7 | 1 |
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.
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)
WHERE dst_account.is_blocked
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 how the intermediate results are passed along the statements. Only some properties of the intermediate results are shown, for brevity.
Statement | Intermediate Result (abreviated) | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
MATCH (src_account:Account) -[transfer:Transfers]-> (dst_account:Account) WHERE dst_account.is_blocked |
|
||||||||||||
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 |
|
Result
account_id | owner_name |
---|---|
7 | Alex |
Return statement
Return statement defines what to return from the matched patterns. It can access
graph pattern variables, contain expressions and other clauses like ORDER_BY,
GROUP_BY. See the RETURN
statement.
Note that Spanner Graph doesn't support returning graph elements as query
results. To return the entire graph element, use the TO_JSON
function.
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;
Result
n |
---|
{"identifier":"mUZpbkdyYXBoLkFjY291bnQAeJEO","kind":"node","labels":["Account"],"properties":{"create_time":"2020-01-10T14:22:20.222Z","id":7,"is_blocked":false,"nick_name":"Vacation Fund"}} |
Composing larger queries with NEXT keyword
You can chain multiple graph linear query statements together using the NEXT
keyword. Input to the first linear query statement is empty. The output of each
linear query statement becomes input to the next linear query statement.
The following example finds the owner of the account with the most incoming
transfers by chaining multiple graph linear statements together. Note that you
can use the same variable, account
in this example, 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;
Result
account_id | owner_name | num_incoming_transfers |
---|---|---|
16 | Lee | 3 |
Functions and expressions
You can use all the functions, operators, and conditionals in GoogleSQL, including aggregate functions and other scalar functions in Spanner Graph query.
Spanner Graph also supports built-in functions and operators for graph elements.
Built-in functions and operators
The following functions and operators are commonly used in GQL:
PROPERTY_EXISTS(n, birthday)
: Returns whethern
exposesbirthday
property.LABELS(n)
: Returns the labels ofn
as defined in the graph schema.PROPERTY_NAMES(n)
: Returns the property names ofn
.TO_JSON(n)
: Returnsn
in JSON format. For more information, see theTO_JSON
function.
The following query illustrates 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;
Result
is_blocked_property_exists | name_property_exists | labels | accounts | location | person |
---|---|---|---|---|---|
false | true | Person | ["Vacation Fund"] | Adelaide, Australia | {"identifier":"mUZpbkdyYXBoLlBlcnNvbgB4kQI=","kind":"node","labels":["Person"],"properties":{"birthday":"1991-12-21T08:00:00Z","city":"Adelaide","country":"Australia","id":1,"name":"Alex"}} |
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 the
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
amount 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 SUM(transfer.amount) AS total_transfer
} AS total_transfer;
Result
name | account_id | total_transfer |
---|---|---|
Alex | 7 | 400 |
Dana | 20 | 700 |
Lee | 16 | 300 |
For a list of supported subquery expressions, see Spanner Graph subqueries.
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.
GRAPH_TABLE
The GRAPH_TABLE
operator takes a linear graph query and returns its result in
a tabular form that can be seamlessly 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);
Then, identify persons of interest through graph pattern matching in GRAPH_TABLE
and join the graph query results with the CreditReports
table to access credit
score.
SELECT
gt.person.id,
credit.score AS latest_credit_score
FROM GRAPH_TABLE(
FinGraph
MATCH (person:Person)-[:Owns]->(:Account)-[:Transfers]->(account:Account)
WHERE account.is_blocked
RETURN DISTINCT person
) AS gt
JOIN CreditReports AS credit
ON gt.person.id = credit.person_id
ORDER BY credit.create_time;
Result:
person_id | latest_credit_score |
---|---|
1 | 700 |
2 | 800 |
What's next
Learn best practices for tuning queries.