Spanner Graph reference for openCypher users

This document compares openCypher and Spanner Graph in the following ways:

• Terminology
• Data model
• Schema
• Query
• Mutation

This document assumes you're familiar with openCypher v9.

Before you begin

Set up and query Spanner Graph using the Google Cloud console.

Terminology

Standards compliance

Spanner Graph adopts ISO Graph Query Language (GQL) and SQL/Property Graph Queries (SQL/PGQ) standards.

Data model

Both Spanner Graph and openCypher adopt the property graph data model with some differences.

Schema

Query

Spanner Graph query capabilities are similar to those of openCypher. The differences between Spanner Graph and openCypher are described in this section.

Specify the graph

In openCypher, there is one default graph, and queries operate on the default graph. In Spanner Graph, you can define more than one graph and a query must start with the GRAPH clause to specify the graph to query. For example:

GRAPHFinGraph
MATCH(p:Person)
RETURNp.name;

For more information, see the graph query syntax.

Graph pattern matching

Spanner Graph supports graph pattern matching capabilities similar to openCypher. The differences are explained in the following sections.

Relationship uniqueness and TRAIL mode

openCypher doesn't return results with repeating edges in a single match; this is called relationship uniqueness in openCypher. In Spanner Graph, repeating edges are returned by default. When uniqueness is desired, use TRAIL mode to ensure no repeating edge exists in the single match. For detailed semantics of TRAIL and other different path modes, see Path mode.

The following example shows how the results of a query change with TRAIL mode:

• The openCypher and Spanner Graph TRAIL mode queries return empty results because the only possible path is to repeat t1 twice.
• By default, the Spanner Graph query returns a valid path.

Example graph

MATCH
(src:Account)-[t1:Transfers]->
(dst:Account)-[t2:Transfers]->
(src)-[t1]->(dst)
WHEREsrc.id=16
RETURNsrc.idASsrc_id,dst.idASdst_id;

GRAPHFinGraph
MATCHTRAIL
(src:Account)-[t1:Transfers]->
(dst:Account)-[t2:Transfers]->
(src)-[t1]->(dst)
WHEREsrc.id=16
RETURNsrc.idASsrc_id,dst.idASdst_id;

GRAPHFinGraph
MATCH
(src:Account)-[t1:Transfers]->
(dst:Account)-[t2:Transfers]->
(src)-[t1]->(dst)
WHEREsrc.id=16
RETURNsrc.idASsrc_id,dst.idASdst_id;

Return graph elements as query results

MATCH(account:Account)
WHEREaccount.id=16
RETURNaccount;

GRAPHFinGraph
MATCH(account:Account)
WHEREaccount.id=16
RETURNTO_JSON(account)ASaccount;

In Spanner Graph, query results don't return graph elements. Use the TO_JSON function to return graph elements as JSON.

Variable-length pattern matching and pattern quantification

Variable-length pattern matching in openCypher is called path quantification in Spanner Graph. Path quantification uses a different syntax, as shown in the following example. For more information, see Quantified path pattern.

MATCH(src:Account)-[:Transfers*1..2]->(dst:Account)
WHEREsrc.id=16
RETURNdst.id
ORDERBYdst.id;

GRAPHFinGraph
MATCH(src:Account)-[:Transfers]->{1,2}(dst:Account)
WHEREsrc.id=16
RETURNdst.id
ORDERBYdst.id;

Variable-length pattern: list of elements

Spanner Graph lets you directly access the variables used in path quantifications. In the following example, e in Spanner Graph is the same as edges(p) in openCypher.

MATCHp=(src:Account)-[:Transfers*1..3]->(dst:Account)
WHEREsrc.id=16
RETURNedges(p);

GRAPHFinGraph
MATCH(src:Account)-[e:Transfers]->{1,3}(dst:Account)
WHEREsrc.id=16
RETURNTO_JSON(e)ASe;

Shortest path

openCypher has two built-in functions to find the shortest path between nodes: shortestPath and allShortestPath.

• shortestPath finds a single shortest path between nodes.
• allShortestPath finds all the shortest paths between nodes. There can be multiple paths of the same length.

Spanner Graph uses a different syntax to find a single shortest path between nodes: ANY SHORTEST for shortestPath. The allShortestPath function isn't supported in Spanner Graph.

MATCH
(src:Account{id:7}),
(dst:Account{id:20}),
p=shortestPath((src)-[*1..10]->(dst))
RETURNlength(p)ASpath_length;

GRAPHFinGraph
MATCHANYSHORTEST
(src:Account{id:7})-[e:Transfers]->{1,3}
(dst:Account{id:20})
RETURNARRAY_LENGTH(e)ASpath_length;

Statements and clauses

The following table lists the openCypher clauses, and indicates whether or not they're supported in Spanner Graph.

GRAPHFinGraph
MATCH(p:Person)
RETURNEXTRACT(YEARFROMp.birthday)ASbirthYear;

GRAPHFinGraph
MATCH(p:Person)
RETURNEXTRACT(YEARFROMp.birthday);-- No aliasing

GRAPHFinGraph
MATCH(n:Account)
RETURNn.id
SKIP@offsetParameter
LIMIT3;

GRAPHFinGraph
MATCH(n:Account)
RETURNn.id
LIMITVALUE{
MATCH(m:Person)
RETURNCOUNT(*)AScount
}AScount;-- Not a constant expression

GRAPHFinGraph
LETarr=[1,2,3]
FORnumINarr
RETURNnum;

Data types

Spanner Graph supports all GoogleSQL data types. For more information, see Data types in GoogleSQL.

The following sections compare openCypher data types with Spanner Graph data types.

Structural type

Property type

Composite type

Spanner Graph doesn't support heterogeneous lists of different types or maps of a dynamic key list and heterogeneous element value types. Use JSON for these use cases.

Type Coercion

For more information about type conversion rules, see Conversion rules in GoogleSQL.

Functions and expressions

Besides graph functions and expressions, Spanner Graph also supports all GoogleSQL built-in functions and expressions.

This section lists openCypher functions and expressions and their equivalents in Spanner Graph.

Structural type functions and expressions

MATCH(n)
RETURNn[n.name]

VALUE{
MATCHpattern
RETURNCOUNT(*)AScount;
}

Property type functions and expressions

a<b<c

a<bANDb<C

boolean_1 <> boolean_2

Other expressions

Query parameter

The following queries show the difference between using parameters in openCypher and in Spanner Graph.

MATCH(n:Person)
WHEREn.id=$id
RETURNn.name;

GRAPHFinGraph
MATCH(n:Person)
WHEREn.id=@id
RETURNn.name;

Mutation

Spanner Graph uses GoogleSQL DML to mutate the node and edge input tables. For more information, see Insert, update, or delete Spanner Graph data.

Create node and edge

CREATE(:Person{id:100,name:'John'});
CREATE(:Account{id:1000,is_blocked:FALSE});


MATCH(p:Person{id:100}),
(a:Account{id:1000})
CREATE(p)-[:Owns{create_time:timestamp()}]->(a);

INSERTINTO
Person(id,name)
VALUES(100,"John");


INSERTINTO
Account(id,is_blocked)
VALUES(1000,FALSE);


INSERTINTOPersonOwnAccount(id,account_id,create_time)
VALUES(100,1000,CURRENT_TIMESTAMP());

MATCH(a:Account{id:1}),(oa:Account)
WHEREoaa
CREATE(a)-[:Transfers{amount:100,create_time:timestamp()}]->(oa);

INSERTINTOAccountTransferAccount(id,to_id,create_time,amount)
SELECTa.id,oa.id,CURRENT_TIMESTAMP(),100
FROMGRAPH_TABLE(
FinGraph
MATCH
(a:Account{id:1000}),
(oa:Account)
WHEREoaa
);

In Spanner Graph, the labels are statically assigned according to the CREATE PROPERTY GRAPH DDL statement.

Update node and edge

MATCH(p:Person{id:100})
SETp.country='United States';

UPDATEPersonASp
SETp.country='United States'
WHEREp.id=100;

To update Spanner Graph labels, see Create, update, or drop a Spanner Graph schema.

Merge node and edge

MERGE(p:Person{id:100,country:'United States'});

INSERTORUPDATEINTOPerson
(id,country)
VALUES(100,'United States');

Delete node and edge

Deleting edges is the same as deleting the input table.

MATCH(p:Person{id:100}),(a:Account{id:1000})
DELETE(p)-[:Owns]->(a);

DELETEPersonOwnAccount
WHEREid=100ANDaccount_id=1000;

Deleting nodes requires handling potential dangling edges. When DELETE CASCADE is specified, DELETE removes the associated edges of nodes like DETACH DELETE in openCypher. For more information, see Spanner schema overview.

DETACHDELETE(:Account{id:1000});

DELETEAccount
WHEREid=1000;

Return mutation results

MATCH(p:Person{id:100})
SETp.country='United States'
RETURNp.id,p.name;

UPDATEPersonASp
SETp.country='United States'
WHEREp.id=100
THENRETURNid,name;

DELETE(p:Person{id:100})
RETURNp.country;

DELETEFROMPerson
WHEREid=100
THENRETURNcountry;

What's next

• Spanner Graph overview.