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:

   GRAPH FinGraph
   MATCH (p:Person)
   RETURN p.name
   ORDER BY p.name
   LIMIT 1;

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.

MATCH
  (src:Account)-[t1:Transfers]->
  (dst:Account)-[t2:Transfers]->
  (src)-[t1]->(dst)
WHERE src.id = 16
RETURN src.id AS src_id, dst.id AS dst_id;
      

GRAPH FinGraph
MATCH TRAIL
  (src:Account)-[t1:Transfers]->
  (dst:Account)-[t2:Transfers]->
  (src)-[t1]->(dst)
WHERE src.id = 16
RETURN src.id AS src_id, dst.id AS dst_id;
      

GRAPH FinGraph
MATCH
  (src:Account)-[t1:Transfers]->
  (dst:Account)-[t2:Transfers]->
  (src)-[t1]-> (dst)
WHERE src.id = 16
RETURN src.id AS src_id, dst.id AS dst_id;
      

Return graph elements as query results

MATCH (account:Account)
WHERE account.id = 16;
RETURN account;

GRAPH FinGraph
MATCH (account:Account)
WHERE account.id = 16;
RETURN TO_JSON(account) AS account;

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)
WHERE src.id = 16
RETURN dst.id
ORDER BY dst.id;
     

GRAPH FinGraph
MATCH (src:Account)-[:Transfers]->{1,2}(dst:Account)
WHERE src.id = 16
RETURN dst.id
ORDER BY dst.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.

MATCH p=(src:Account)-[:Transfers*1..3]->(dst:Account)
WHERE src.id = 16
RETURN edges(p);
      

GRAPH FinGraph
MATCH (src:Account) -[e:Transfers]->{1,3} (dst:Account)
WHERE src.id = 16
RETURN TO_JSON(e) AS e;
     

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. allShortestPath is not supported.

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

GRAPH FinGraph
MATCH ANY SHORTEST
  (src:Account {id: 7})-[e:Transfers]->{1, 3}
  (dst:Account {id: 20})
RETURN ARRAY_LENGTH(e) AS path_length;
      

Statements and clauses

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

GRAPH FinGraph
MATCH (p:Person)
RETURN EXTRACT(YEAR FROM p.birthday) AS birthYear;

GRAPH FinGraph
MATCH (p:Person)
RETURN EXTRACT(YEAR FROM p.birthday); -- No aliasing

GRAPH FinGraph
MATCH (n:Account)
RETURN n.id
SKIP @offsetParameter
LIMIT 3;

GRAPH FinGraph
MATCH (n:Account)
RETURN n.id
LIMIT VALUE {
  MATCH (m:Person)
  RETURN COUNT(*) AS count
} AS count; -- Not a constant expression

GRAPH FinGraph
LET arr = [1, 2, 3]
FOR num IN arr
RETURN num;

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.

For a complete list of functions and expressions, see GoogleSQL functions, operators, and conditionals.

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

Structural type functions and expressions

MATCH (n)
RETURN n[n.name]

VALUE {
  MATCH pattern
  RETURN COUNT(*) AS count;
}

Property type functions and expressions

a < b < c

      a < b AND b < 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)
WHERE n.id = $id
RETURN n.name;

GRAPH FinGraph
MATCH (n:Person)
WHERE n.id = @id
RETURN n.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);

INSERT INTO
Person (id, name)
VALUES (100, "John");


INSERT INTO
Account (id, is_blocked)
VALUES (1000, FALSE);


INSERT INTO PersonOwnAccount (id, account_id, create_time)
VALUES (100, 1000, CURRENT_TIMESTAMP());

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

INSERT INTO AccountTransferAccount(id, to_id, create_time, amount)
SELECT a.id, oa.id, CURRENT_TIMESTAMP(), 100
FROM GRAPH_TABLE(
  FinGraph
  MATCH
    (a:Account {id:1000}),
    (oa:Account)
  WHERE oa <> a
);

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})
SET p.country = 'United States';

UPDATE Person AS p
SET p.country = 'United States'
WHERE p.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'});

INSERT OR UPDATE INTO Person
(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);

DELETE PersonOwnAccount
WHERE id = 100 AND account_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.

DETACH DELETE (:Account {id: 1000});

DELETE Account
WHERE id = 1000;

Return mutation results

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

UPDATE Person AS p
SET p.country = 'United States'
WHERE p.id = 100
THEN RETURN id, name;

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

DELETE FROM Person
WHERE id = 100
THEN RETURN country;

What's next

• Spanner Graph overview.