GoogleSQL for Spanner supports the following GQL functions:
Function list
| Name | Summary |
|---|---|
DESTINATION_NODE_ID | Gets a unique identifier of a graph edge's destination node. |
EDGES | Gets the edges in a graph path. The resulting array retains the original order in the graph path. |
ELEMENT_ID | Gets a graph element's unique identifier. |
IS_ACYCLIC | Checks if a graph path has a repeating node. |
IS_TRAIL | Checks if a graph path has a repeating edge. |
LABELS | Gets the labels associated with a graph element. |
NODES | Gets the nodes in a graph path. The resulting array retains the original order in the graph path. |
PATH | Creates a graph path from a list of graph elements. |
PATH_FIRST | Gets the first node in a graph path. |
PATH_LAST | Gets the last node in a graph path. |
PATH_LENGTH | Gets the number of edges in a graph path. |
PROPERTY_NAMES | Gets the property names associated with a graph element. |
SOURCE_NODE_ID | Gets a unique identifier of a graph edge's source node. |
DESTINATION_NODE_ID
DESTINATION_NODE_ID(edge_element)Description
Gets a unique identifier of a graph edge's destination node. The unique identifier is only valid for the scope of the query where it's obtained.
Arguments
edge_element: AGRAPH_ELEMENTvalue that represents an edge.
Details
Returns NULL if edge_element is NULL.
Return type
STRING
Examples
GRAPHFinGraphMATCH(:Person)-[o:Owns]->(a:Account)RETURNa.idASaccount_id,DESTINATION_NODE_ID(o)ASdestination_node_id/*------------------------------------------+ |account_id | destination_node_id | +-----------|------------------------------+ | 7 | mUZpbkdyYXBoLkFjY291bnQAeJEO | | 16 | mUZpbkdyYXBoLkFjY291bnQAeJEg | | 20 | mUZpbkdyYXBoLkFjY291bnQAeJEo | +------------------------------------------*/Note that the actual identifiers obtained may be different from what's shown above.
EDGES
EDGES(graph_path)Description
Gets the edges in a graph path. The resulting array retains the original order in the graph path.
Definitions
graph_path: AGRAPH_PATHvalue that represents a graph path.
Details
If graph_path is NULL, returns NULL.
Return type
ARRAY<GRAPH_ELEMENT>
Examples
GRAPHFinGraphMATCHp=(src:Account)-[t1:Transfers]->(mid:Account)-[t2:Transfers]->(dst:Account)LETes=EDGES(p)RETURNARRAY_CONCAT(ARRAY_TRANSFORM(es,e->e.Id),[dst.Id])asids_in_path/*-------------+ | ids_in_path | +-------------+ | [16,20,7] | +-------------+ | [20,7,16] | +-------------+ | [20,7,16] | +-------------+ | [16,20,16] | +-------------+ | [7,16,20] | +-------------+ | [7,16,20] | +-------------+ | [20,16,20] | +-------------*/ELEMENT_ID
ELEMENT_ID(element)Description
Gets a graph element's unique identifier. The unique identifier is only valid for the scope of the query where it's obtained.
Arguments
element: AGRAPH_ELEMENTvalue.
Details
Returns NULL if element is NULL.
Return type
STRING
Examples
GRAPHFinGraphMATCH(p:Person)-[o:Owns]->(:Account)RETURNp.nameASname,ELEMENT_ID(p)ASnode_element_id,ELEMENT_ID(o)ASedge_element_id/*--------------------------------------------------------------------------------------------------------------------------------------------+ | name | node_element_id | edge_element_id . | +------|------------------------------|------------------------------------------------------------------------------------------------------+ | Alex | mUZpbkdyYXBoLlBlcnNvbgB4kQI= | mUZpbkdyYXBoLlBlcnNvbk93bkFjY291bnQAeJECkQ6ZRmluR3JhcGguUGVyc29uAHiRAplGaW5HcmFwaC5BY2NvdW50AHiRDg== | | Dana | mUZpbkdyYXBoLlBlcnNvbgB4kQQ= | mUZpbkdyYXBoLlBlcnNvbk93bkFjY291bnQAeJEGkSCZRmluR3JhcGguUGVyc29uAHiRBplGaW5HcmFwaC5BY2NvdW50AHiRIA== | | Lee | mUZpbkdyYXBoLlBlcnNvbgB4kQY= | mUZpbkdyYXBoLlBlcnNvbk93bkFjY291bnQAeJEEkSiZRmluR3JhcGguUGVyc29uAHiRBJlGaW5HcmFwaC5BY2NvdW50AHiRKA== | +--------------------------------------------------------------------------------------------------------------------------------------------*/Note that the actual identifiers obtained may be different from what's shown above.
IS_ACYCLIC
IS_ACYCLIC(graph_path)Description
Checks if a graph path has a repeating node. Returns TRUE if a repetition is found, otherwise returns FALSE.
Definitions
graph_path: AGRAPH_PATHvalue that represents a graph path.
Details
Two nodes are considered equal if they compare as equal.
Returns NULL if graph_path is NULL.
Return type
BOOL
Examples
GRAPHFinGraphMATCHp=(src:Account)-[t1:Transfers]->(mid:Account)-[t2:Transfers]->(dst:Account)RETURNsrc.idASsource_account_id,IS_ACYCLIC(p)ASis_acyclic_path/*-------------------------------------* | source_account_id | is_acyclic_path | +-------------------------------------+ | 16 | TRUE | | 20 | TRUE | | 20 | TRUE | | 16 | FALSE | | 7 | TRUE | | 7 | TRUE | | 20 | FALSE | *-------------------------------------*/IS_TRAIL
IS_TRAIL(graph_path)Description
Checks if a graph path has a repeating edge. Returns TRUE if a repetition is found, otherwise returns FALSE.
Definitions
graph_path: AGRAPH_PATHvalue that represents a graph path.
Details
Returns NULL if graph_path is NULL.
Return type
BOOL
Examples
GRAPHFinGraphMATCHp=(a1:Account)-[t1:Transfers]->(a2:Account)-[t2:Transfers]-> (a3:Account)-[t3:Transfers]->(a4:Account)WHEREa1.id < a4.idRETURNIS_TRAIL(p)ASis_trail_path,t1.idast1_id,t2.idast2_id,t3.idast3_id/*---------------+-------+-------+-------+ | is_trail_path | t1_id | t2_id | t3_id | +---------------+-------+-------+-------+ | FALSE | 16 | 20 | 16 | | TRUE | 7 | 16 | 20 | | TRUE | 7 | 16 | 20 | +---------------+-------+-------+-------*/LABELS
LABELS(element)Description
Gets the labels associated with a graph element and preserves the original case of each label.
Arguments
element: AGRAPH_ELEMENTvalue that represents the graph element to extract labels from.
Details
Returns NULL if element is NULL.
Return type
ARRAY<STRING>
Examples
GRAPHFinGraphMATCH(n:Person|Account)RETURNLABELS(n)ASlabel,n.id/*----------------+ | label | id | +----------------+ | [Account] | 7 | | [Account] | 16 | | [Account] | 20 | | [Person] | 1 | | [Person] | 2 | | [Person] | 3 | +----------------*/NODES
NODES(graph_path)Description
Gets the nodes in a graph path. The resulting array retains the original order in the graph path.
Definitions
graph_path: AGRAPH_PATHvalue that represents a graph path.
Details
Returns NULL if graph_path is NULL.
Return type
ARRAY<GRAPH_ELEMENT>
Examples
GRAPHFinGraphMATCHp=(src:Account)-[t1:Transfers]->(mid:Account)-[t2:Transfers]->(dst:Account)LETns=NODES(p)RETURNJSON_QUERY(TO_JSON(ns)[0],'$.labels')ASlabels,JSON_QUERY(TO_JSON(ns)[0],'$.properties.nick_name')ASnick_name;/*--------------------------------* | labels | nick_name | +--------------------------------+ | ["Account"] | "Vacation Fund" | | ["Account"] | "Rainy Day Fund" | | ["Account"] | "Rainy Day Fund" | | ["Account"] | "Rainy Day Fund" | | ["Account"] | "Vacation Fund" | | ["Account"] | "Vacation Fund" | | ["Account"] | "Vacation Fund" | | ["Account"] | "Rainy Day Fund" | *--------------------------------*/PATH
PATH(graph_element[,...])Description
Creates a graph path from a list of graph elements.
Definitions
graph_element: AGRAPH_ELEMENTvalue that represents a graph element, such as a node or edge, to add to a graph path.
Details
This function produces an error if:
- A graph element is
NULL. - Nodes aren't interleaved with edges.
- An edge doesn't connect to neighboring nodes.
Return type
GRAPH_PATH
Examples
GRAPHFinGraphMATCH(src:Account)-[t1:Transfers]->(mid:Account)-[t2:Transfers]->(dst:Account)LETp=PATH(src,t1,mid,t2,dst)RETURNJSON_QUERY(TO_JSON(p)[0],'$.labels')ASelement_a,JSON_QUERY(TO_JSON(p)[1],'$.labels')ASelement_b,JSON_QUERY(TO_JSON(p)[2],'$.labels')ASelement_c/*-------------------------------------------* | element_a | element_b | element_c | +-------------------------------------------+ | ["Account"] | ["Transfers"] | ["Account"] | | ... | ... | ... | *-------------------------------------------*/-- Error: in 'p', a graph element is NULL.GRAPHFinGraphMATCH(src:Account)-[t1:Transfers]->(mid:Account)-[t2:Transfers]->(dst:Account)LETp=PATH(src,NULL,mid,t2,dst)RETURNTO_JSON(p)ASresults-- Error: in 'p', 'src' and 'mid' are nodes that should be interleaved with an-- edge.GRAPHFinGraphMATCH(src:Account)-[t1:Transfers]->(mid:Account)-[t2:Transfers]->(dst:Account)LETp=PATH(src,mid,t2,dst)RETURNTO_JSON(p)ASresults-- Error: in 'p', 't2' is an edge that doesn't connect to a neighboring node on-- the right.GRAPHFinGraphMATCH(src:Account)-[t1:Transfers]->(mid:Account)-[t2:Transfers]->(dst:Account)LETp=PATH(src,t2,mid)RETURNTO_JSON(p)ASresultsPATH_FIRST
PATH_FIRST(graph_path)Description
Gets the first node in a graph path.
Definitions
graph_path: AGRAPH_PATHvalue that represents the graph path to extract the first node from.
Details
Returns NULL if graph_path is NULL.
Return type
GRAPH_ELEMENT
Examples
GRAPHFinGraphMATCHp=(src:Account)-[t1:Transfers]->(mid:Account)-[t2:Transfers]->(dst:Account)LETf=PATH_FIRST(p)RETURNLABELS(f)ASlabels,f.nick_nameASnick_name;/*--------------------------* | labels | nick_name | +--------------------------+ | Account | Vacation Fund | | Account | Rainy Day Fund | | Account | Rainy Day Fund | | Account | Vacation Fund | | Account | Vacation Fund | | Account | Vacation Fund | | Account | Rainy Day Fund | *--------------------------*/PATH_LAST
PATH_LAST(graph_path)Description
Gets the last node in a graph path.
Definitions
graph_path: AGRAPH_PATHvalue that represents the graph path to extract the last node from.
Details
Returns NULL if graph_path is NULL.
Return type
GRAPH_ELEMENT
Examples
GRAPHFinGraphMATCHp=(src:Account)-[t1:Transfers]->(mid:Account)-[t2:Transfers]->(dst:Account)LETf=PATH_LAST(p)RETURNLABELS(f)ASlabels,f.nick_nameASnick_name;/*--------------------------* | labels | nick_name | +--------------------------+ | Account | Vacation Fund | | Account | Vacation Fund | | Account | Vacation Fund | | Account | Vacation Fund | | Account | Rainy Day Fund | | Account | Rainy Day Fund | | Account | Rainy Day Fund | *--------------------------*/PATH_LENGTH
PATH_LENGTH(graph_path)Description
Gets the number of edges in a graph path.
Definitions
graph_path: AGRAPH_PATHvalue that represents the graph path with the edges to count.
Details
Returns NULL if graph_path is NULL.
Return type
INT64
Examples
GRAPHFinGraphMATCHp=(src:Account)-[t1:Transfers]->(mid:Account)-[t2:Transfers]->(dst:Account)RETURNPATH_LENGTH(p)ASresults/*---------* | results | +---------+ | 2 | | 2 | | 2 | | 2 | | 2 | | 2 | | 2 | *---------*/PROPERTY_NAMES
PROPERTY_NAMES(element)Description
Gets the name of each property associated with a graph element and preserves the original case of each name.
Arguments
element: AGRAPH_ELEMENTvalue.
Details
Returns NULL if element is NULL.
Return type
ARRAY<STRING>
Examples
GRAPHFinGraphMATCH(n:Person|Account)RETURNPROPERTY_NAMES(n)ASproperty_names,n.id/*-----------------------------------------------+ | label | id | +-----------------------------------------------+ | [create_time, id, is_blocked, nick_name] | 7 | | [create_time, id, is_blocked, nick_name] | 16 | | [create_time, id, is_blocked, nick_name] | 20 | | [birthday, city, country, id, name] | 1 | | [birthday, city, country, id, name] | 2 | | [birthday, city, country, id, name] | 3 | +-----------------------------------------------*/SOURCE_NODE_ID
SOURCE_NODE_ID(edge_element)Description
Gets a unique identifier of a graph edge's source node. The unique identifier is only valid for the scope of the query where it's obtained.
Arguments
edge_element: AGRAPH_ELEMENTvalue that represents an edge.
Details
Returns NULL if edge_element is NULL.
Return type
STRING
Examples
GRAPHFinGraphMATCH(p:Person)-[o:Owns]->(:Account)RETURNp.nameASname,SOURCE_NODE_ID(o)ASsource_node_id/*-------------------------------------+ | name | source_node_id | +------|------------------------------+ | Alex | mUZpbkdyYXBoLlBlcnNvbgB4kQI= | | Dana | mUZpbkdyYXBoLlBlcnNvbgB4kQQ= | | Lee | mUZpbkdyYXBoLlBlcnNvbgB4kQY= | +-------------------------------------*/Note that the actual identifiers obtained may be different from what's shown above.
