To create a graph, use the create_graph function, located in the ag_catalog namespace.

To delete a graph, use the drop_graph function, located in the ag_catalog namespace.

When creating graphs with apache_age, a Postgres Pro namespace will be generated for every individual graph. The name and namespace of the created graphs can be seen within the ag_graph table from the ag_catalog namespace:

SELECT create_graph('new_graph');

NOTICE:  graph 'new_graph' has been created
 create_graph
--------------

(1 row)

SELECT * FROM ag_catalog.ag_graph;

   name    | namespace
-----------+-----------
 new_graph | new_graph
(1 row)

After creating the graph, two tables are going to be created under the graph namespace to store vertices and edges: _ag_label_vertex and _ag_label_edge. These will be the parent tables of any new vertex or edge label. The query below shows how to retrieve the edge and vertex labels for all the graphs in the database.

-- Before creating a new vertex label.
SELECT * FROM ag_catalog.ag_label;

       name       | graph | id | kind |          relation          |        seq_name
------------------+-------+----+------+----------------------------+-------------------------
 _ag_label_vertex | 68484 |  1 | v    | new_graph._ag_label_vertex | _ag_label_vertex_id_seq
 _ag_label_edge   | 68484 |  2 | e    | new_graph._ag_label_edge   | _ag_label_edge_id_seq
(2 rows)

-- Creating a new vertex label.
SELECT create_vlabel('new_graph', 'Person');
NOTICE:  VLabel 'Person' has been created
 create_vlabel
---------------

(1 row)

-- After creating a new vertex label.
SELECT * FROM ag_catalog.ag_label;
       name       | graph | id | kind |          relation          |        seq_name
------------------+-------+----+------+----------------------------+-------------------------
 _ag_label_vertex | 68484 |  1 | v    | new_graph._ag_label_vertex | _ag_label_vertex_id_seq
 _ag_label_edge   | 68484 |  2 | e    | new_graph._ag_label_edge   | _ag_label_edge_id_seq
 Person           | 68484 |  3 | v    | new_graph.'Person'         | Person_id_seq
(3 rows)

Whenever a vertex label is created with the create_vlabel() function, a new table is generated within the new_graph namespace: new_graph.'label'. The same works for the create_elabel() function for the creation of edge labels. Creating vertices and edges with Cypher will automatically make these tables.

-- Creating two vertices and one edge.
SELECT * FROM cypher('new_graph', $$
CREATE (:Person {name: 'Daedalus'})-[:FATHER_OF]->(:Person {name: 'Icarus'})
$$) AS (a agtype);
 a
---
(0 rows)

-- Showing the newly created tables.
SELECT * FROM ag_catalog.ag_label;
       name       | graph | id | kind |          relation          |        seq_name
------------------+-------+----+------+----------------------------+-------------------------
 _ag_label_vertex | 68484 |  1 | v    | new_graph._ag_label_vertex | _ag_label_vertex_id_seq
 _ag_label_edge   | 68484 |  2 | e    | new_graph._ag_label_edge   | _ag_label_edge_id_seq
 Person           | 68484 |  3 | v    | new_graph.'Person'         | Person_id_seq
 FATHER_OF        | 68484 |  4 | e    | new_graph.'FATHER_OF'      | FATHER_OF_id_seq
(4 rows)

Cypher may not be used as part of an expression, use a subquery instead. See Advanced Cypher Queries for information about how to use Cypher queries with expressions.

Calling Cypher in the SELECT clause as an independent column is not allowed. However, Cypher may be used when it belongs as a conditional.

SELECT
    cypher('graph_name', $$
         MATCH (v:Person)
         RETURN v.name
     $$);

ERROR:  cypher(...) in expressions is not supported
LINE 3:     cypher('graph_name', $$
            ^
HINT:  Use subquery instead if possible.

An entity has a unique, comparable identity which defines whether or not two entities are equal.

An entity is assigned a set of properties, each of which are uniquely identified in the set by the irrespective property keys.

{id:1; label: 'label_name'; properties: {prop1: value1, prop2: value2}}::vertex

Type casting a map to a vertex:

SELECT *
FROM cypher('graph_name', $$
    WITH {id: 0, label: 'label_name', properties: {i: 0}}::vertex as v
    RETURN v
$$) AS (v agtype);

                                v
------------------------------------------------------------------
 {"id": 0, "label": "label_name", "properties": {"i": 0}}::vertex
(1 row)

An edge is an entity that encodes a directed connection between exactly two nodes, the source node and the target node. An outgoing edge is a directed relationship from the point of view of its source node. An incoming edge is a directed relationship from the point of view of its target node. An edge is assigned exactly one edge type.

{id: 3; startid: 1; endid: 2; label: 'edge_label' properties{prop1: value1, prop2: value2}}::edge

Type casting a map to an edge:

SELECT *
FROM cypher('graph_name', $$
    WITH {id: 2, start_id: 0, end_id: 1, label: 'label_name', properties: {i: 0}}::edge as e
    RETURN e
$$) AS (e agtype);

                                             e
--------------------------------------------------------------------------------------------
 {"id": 2, "label": "label_name", "end_id": 1, "start_id": 0, "properties": {"i": 0}}::edge
(1 row)

A path is a series of alternating vertices and edges. A path must start with a vertex, and have at least one edge.

Type casting a list to a path:

SELECT *
FROM cypher('graph_name', $$
    WITH [{id: 0, label: 'label_name_1', properties: {i: 0}}::vertex,
            {id: 2, start_id: 0, end_id: 1, label: 'edge_label', properties: {i: 0}}::edge,
           {id: 1, label: 'label_name_2', properties: {}}::vertex
           ]::path as p
    RETURN p
$$) AS (p agtype);

                                                                                                                  p
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"id": 0, "label": "label_name_1", "properties": {"i": 0}}::vertex, {"id": 2, "label": "edge_label", "end_id": 1, "start_id": 0, "properties": {"i": 0}}::edge, {"id": 1, "label": "label_name_2", "properties": {}}::vertex]::path
(1 row)

The openCypher specification features four distinct concepts related to equality and ordering:

Comparison operators need to function as one would expect comparison operators to function — equality and comparability. But, at the same time, they need to allow the sorting of column data — equivalence and orderability.

Unfortunately, it may not be possible to implement separate comparison operators for equality and comparison operations, and, equivalence and orderability operations, in Postgres Pro, for the same query. So we prioritize equivalence and orderability over equality and comparability to allow for ordering of output data.

The ordering of different agtype types, when using <, <=, >, >= from the smallest value to the largest value is:

SELECT * FROM cypher('graph_name', $$
CREATE (:Person {name: 'John'}),
       (:Person {name: 'Jeff'}),
       (:Person {name: 'Joan'}),
       (:Person {name: 'Bill'})
$$) AS (result agtype);

Operator precedence in apache_age is shown below:

To calculate aggregated data, Cypher offers aggregation, analogous to SQL GROUP BY.

Aggregating functions take a set of values and calculate an aggregated value over them. Examples are avg() that calculates the average of multiple numeric values, or min() that finds the smallest numeric or string value in a set of values. When we say below that an aggregating function operates on a set of values, we mean these to be the result of the application of the inner expression (such as n.age) to all the records within the same aggregation group.

Aggregation can be computed over all the matching subgraphs, or it can be further divided by introducing grouping keys. These are non-aggregate expressions, that are used to group the values going into the aggregate functions.

Assume we have the following RETURN statement:

SELECT * FROM cypher('graph_name', $$
    MATCH (v:Person)
    RETURN v.name, count(*)
$$) as (grouping_key agtype, count agtype);

 grouping_key | count
--------------+-------
 "A"          | 1
 "D"          | 2
 "B"          | 1
 "C"          | 1
(4 rows)

We have two return expressions: grouping_key, and count(*). The first, grouping_key, is not an aggregate function, and so it will be the grouping key. The latter, count(*) is an aggregate expression. The matching subgraphs will be divided into different buckets, depending on the grouping key. The aggregate function will then be run on these buckets, calculating an aggregate value per bucket.

To use aggregations to sort the result set, the aggregation must be included in the RETURN to be used in the ORDER BY.

SELECT *
FROM cypher('graph_name', $$
    MATCH (me:Person)-[]->(friend:Person)
    RETURN count(friend), me
    ORDER BY count(friend)
$$) as (friends agtype, me agtype);

In a distinct aggregation (i.e. of the form aggr(DISTINCT expr)), the list of aggregated values is the list of candidate values with all null values removed from it. Furthermore, in a distinct aggregation, only one of all equivalent candidate values is included in the list of aggregated values, i.e. duplicates under equivalence are removed.

The DISTINCT operator works in conjunction with aggregation. It is used to make all values unique before running them through an aggregate function.

SELECT *
FROM cypher('graph_name', $$
    MATCH (v:Person)
    RETURN count(DISTINCT v.eyes), count(v.eyes)
$$) as (distinct_eyes agtype, eyes agtype);

 distinct_eyes | eyes
---------------+------
 2             | 3
(1 row)

This feature of not requiring the user to specify their grouping keys for a query allows for ambiguity on what Cypher should qualify as their grouping keys.

SELECT * FROM cypher('graph_name', $$
CREATE (:L {a: 1, b: 2, c: 3}),
       (:L {a: 2, b: 3, c: 1}),
       (:L {a: 3, b: 1, c: 2})
$$) as (a agtype);

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN x.a + count(*) + x.b + count(*) + x.c
$$) as (a agtype);

ERROR:  'x' must be either part of an explicitly listed key or used inside an aggregate function
LINE 3: RETURN x.a + count(*) + x.b + count(*) + x.c

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN (x.a + x.b + x.c) + count(*) + count(*), x.a + x.b + x.c
$$) as (count agtype, key agtype);

count  | key
-------+-----
 12    | 6
(1 row)

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN x.a + count(*) + x.b + count(*) + x.c, x.a, x.b, x.c
$$) as (count agtype, a agtype, b agtype, c agtype);

 count | a | b | c
-------+---+---+---
 10    | 3 | 1 | 2
 10    | 2 | 3 | 1
 10    | 1 | 2 | 3
(3 rows)

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN count(*) + count(*) + x.a + x.b + x.c, x
$$) as (count agtype, key agtype);

 count |                                          key
-------+----------------------------------------------------------------------------------------
 8     | {"id": 1407374883553283, "label": "L", "properties": {"a": 3, "b": 1, "c": 2}}::vertex
 8     | {"id": 1407374883553281, "label": "L", "properties": {"a": 1, "b": 2, "c": 3}}::vertex
 8     | {"id": 1407374883553282, "label": "L", "properties": {"a": 2, "b": 3, "c": 1}}::vertex
(3 rows)

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    WITH count(*) + count(*) + x.a + x.b + x.c as column, x
    RETURN column
$$) as (a agtype);

 a
---
 8
 8
 8
(3 rows)

Following are the details about the functions to create vertices and edges from the file.

Function load_labels_from_file is used to load vertices from CSV files.

load_labels_from_file('graph_name',
                      'label_name',
                      'file_path')

By adding the fourth parameter user can exclude the id field. Use this when there is no id field in the file.

load_labels_from_file('graph_name',
                      'label_name',
                      'file_path',
                      false)

Function load_edges_from_file can be used to load edges from the CSV file. See the file structure below.

load_edges_from_file('graph_name',
                    'label_name',
                    'file_path');

Following is the explanation about the structure for CSV files for vertices and edges.

A CSV file for nodes is formatted as follows:

Similarly, a CSV file for edges is formatted as follows:

When the connection between two vertices is of variable length, the list of edges that form the connection can be returned using the following connection.

Rather than describing a long path using a sequence of many vertex and edge descriptions in a pattern, many edges (and the intermediate vertices) can be described by specifying a length in the edge description of a pattern.

(u)-[*2]->(v)

Which describes a right directed path of three vertices and two edges can be rewritten to:

(u)-[]->()-[]->(v)

A range length can also be given:

(u)-[*3..5]->(v)

Which is equivalent to:

(u)-[]->()-[]->()-[]->(v) and
(u)-[]->()-[]->()-[]->()-[]->(v) and
(u)-[]->()-[]->()-[]->()-[]->()-[]->(v)

The previous example provided gave the path both a lower and upper bound for the number of edges (and vertices) between u and v. Either one or both of these binding values can be excluded.

(u)-[*3..]->(v)

Returns all paths between u and v that have three or more edges included.

(u)-[*..5]->(v)

Returns all paths between u and v that have 5 or fewer edges included.

(u)-[*]->(v)

Returns all paths between u and v.

SELECT * FROM cypher('graph_name', $$
    MATCH p = (actor {name: 'Willam Dafoe'})-[:ACTED_IN*2]-(co_actor)
    RETURN relationships(p)
$$) as (r agtype);

                                                                               r
------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{id: 0; label:"ACTED_IN"; properties: {role: "Green Goblin"}}::edge, {id: 1; label: "ACTED_IN; properties: {role: "Spiderman", actor: "Toby Maguire}}::edge]
[{id: 0; label:"ACTED_IN"; properties: {role: "Green Goblin"}}::edge, {id: 2; label: "ACTED_IN; properties: {role: "Spiderman", actor: "Andrew Garfield"}}::edge]
(2 rows)

Aggregated results have to pass through a WITH clause to be able to filter on.

SELECT *
FROM cypher('graph_name', $$
    MATCH (david {name: 'David'})-[]-(otherPerson)-[]->()
    WITH otherPerson, count(*) AS foaf
    WHERE foaf > 1
    RETURN otherPerson.name
$$) as (name agtype);

  name
--------
"Anders"
(1 row)

The name of the person connected to “David” with the at least more than one outgoing relationship will be returned by the query.

You can sort your results before passing them to collect, thus sorting the resulting list.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)WITH n
    ORDER BY n.name DESC LIMIT 3
    RETURN collect(n.name)
$$) as (names agtype);

         names
-------------------------
["Emil","David","Ceasar"]
(1 row)

A list of the names of people in reverse order, limited to 3, is returned.

You can match paths, limit to a certain number, and then match again using those paths as a base, as well as any number of similar limited searches.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'Anders'})-[]-(m)WITH m
    ORDER BY m.name DESC LIMIT 1
    MATCH (m)-[]-(o)
    RETURN o.name
$$) as (name agtype);

  name
-------
"Anders"
"Bossman"
(2 rows)

Starting at “Anders”, find all matching nodes, order by name descending and get the top result, then find all the nodes connected to that top result, and return their names.

To return a node, list it in the RETURN statement.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'B'})
    RETURN n
$$) as (n agtype);

                       n
---------------------------------------------------
{id: 0; label: '' properties: {name: 'B'}}::vertex
(1 row)

The example will return the node.

To return n edges, just include it in the RETURN list.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)-[r:KNOWS]->()
    WHERE n.name = 'A'
    RETURN r
$$) as (r agtype);

                              r
-------------------------------------------------------------------
 {id: 2; startid: 0; endid: 1; label: 'KNOWS' properties: {}}::edge
(1 row)

The relationship is returned by the example.

To return a property, use the dot separator, like this:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'A'})
    RETURN n.name
$$) as (name agtype);

 name
------
 'A'
(1 row)

The value of the property name gets returned.

When you want to return all vertices, edges and paths found in a query, you can use the * symbol.

SELECT *
FROM cypher('graph_name', $$
    MATCH (a {name: 'A'})-[r]->(b)
    RETURN *
$$) as (a agtype, b agtype, r agtype);

                                             a                                                          |                                                                 b	                                                          |                                 r
--------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------
 {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "A", "happy": "Yes!"}}::vertex	| {"id": 1125899906842625, "label": "BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge	| {"id": 281474976710660, "label": "", "properties": {"name": "B"}}::vertex
 {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "A", "happy": "Yes!"}}::vertex	| {"id": 1407374883553281, "label": "KNOWS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge	| {"id": 281474976710660, "label": "", "properties": {"name": "B"}}::vertex
(2 rows)

This returns the two vertices, and the edge used in the query.

To introduce a placeholder that is made up of characters that are not contained in the English alphabet, you can use the ` to enclose the variable, like this:

SELECT *
FROM cypher('graph_name', $$
    MATCH (`This isn\'t a common variable`)
    WHERE `This isn\'t a common variable`.name = 'A'
    RETURN `This isn\'t a common variable`.happy
$$) as (happy agtype);

 happy
-------
 "Yes!"
(1 row)

The node with name “A” is returned.

If the name of the field should be different from the expression used, you can rename it by changing the name in the column list definition.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'A'})
    RETURN n.name
$$) as (objects_name agtype);

 objects_name
-------------
 'A'
(1 row)

Returns the property of a node, but renames the field.

If a property might or might not be there, you can still select it as usual. It will be treated as null if it is missing.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    RETURN n.age
$$) as (age agtype);

 age
-----
 55
 NULL
(2 rows)

This example returns age when the node has that property, or null if the property is not there.

Any expression can be used as a return item—literals, predicates, properties, functions, and everything else.

SELECT *
FROM cypher('graph_name', $$
    MATCH (a)
    RETURN a.age > 30, 'I\'m a literal', id(a)
$$) as (older_than_30 agtype, literal agtype, id agtype);

 older_than_30 | literal         | id
---------------+-----------------+----
 true          | 'I'm a literal' | 1
(1 row)

Returns a predicate, a literal and function call with a pattern expression parameter.

DISTINCT retrieves only unique records depending on the fields that have been selected to output.

SELECT *
FROM cypher('graph_name', $$
MATCH (a {name: 'A'})-[]->(b)
RETURN DISTINCT b
$$) as (b agtype);

                          b
----------------------------------------------------
 {id: 1; label: '' properties: {name: 'B'}}::vertex
(1 row)

The node named “B” is returned by the query, but only once.

ORDER BY is used to sort the output.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    WITH n.name as name, n.age as age
    ORDER BY n.name
    RETURN name, age
$$) as (name agtype, age agtype);

  name  | age
--------+-----
  "A"	  | 34
  "B"	  | 34
  "C"	  | 32
(3 rows)

The nodes are returned, sorted by their name.

You can order by multiple properties by stating each variable in the ORDER BY clause. Cypher will sort the result by the first variable listed, and for equal values, go to the next property in the ORDER BY clause, and so on.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    WITH n.name as name, n.age as age
    ORDER BY n.age, n.name
    RETURN name, age
$$) as (name agtype, age agtype);

  name  | age
--------+-----
  "C"	  | 32
  "A"	  | 34
  "B"	  | 34
(3 rows)

This returns the nodes, sorted first by their age, and then by their name.

By adding DESC[ENDING] after the variable to sort on, the sort will be done in reverse order.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    WITH n.name AS name, n.age AS age
    ORDER BY n.name DESC
    RETURN name, age
$$) as (name agtype, age agtype);

  name  | age
--------+-----
  "C"	  | 32
  "B"	  | 34
  "A"	  | 34
(3 rows)

The example returns the nodes, sorted by their name in reverse order.

When sorting the result set, null will always come at the end of the result set for ascending sorting, and first when doing descending sort.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    WITH n.name AS name, n.age AS age, n.height AS height
    ORDER BY n.height
    RETURN name, age, height
$$) as (name agtype, age agtype, height agtype);

  name  | age | height
--------+-----+--------
  "A"	  | 34  | 170
  "C"	  | 32  | 185
  "B"	  | 34  | NULL
(3 rows)

The nodes are returned sorted by the length property, with a node without that property last.

To return a subset of the result, starting from the top, use this syntax:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    RETURN n.name
    ORDER BY n.name
    SKIP 3
$$) as (names agtype);

 names
-------
 "D"
 "E"
(2 rows)

The node is returned, and no property age exists on it.

To return a subset of the result, starting in the middle, use this syntax:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    RETURN n.name
    ORDER BY n.name
    SKIP 1
    LIMIT 2
$$) as (names agtype);

 names
-------
 "B"
 "C"
(2 rows)

Two vertices from the middle are returned.

Using an expression with SKIP to return a subset of the rows.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    RETURN n.name
    ORDER BY n.name
    SKIP (3 * rand())+ 1
$$) as (a agtype);

 names
-------
 "C"
 "D"
 "E"
(3 rows)

The first two vertices are skipped, and only the last three are returned in the result.

To return a subset of the result, starting from the top, use this syntax:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)RETURN n.name
    ORDER BY n.name
    LIMIT 3
$$) as (names agtype);

names
"A"
"B"
"C"
3 rows

The node is returned, and no property age exists on it.

LIMIT accepts any expression that evaluates to a positive integer as long as it is not referring to any external variables:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    RETURN n.name
    ORDER BY n.name
    LIMIT toInteger(3 * rand()) + 1
$$) as (names agtype);

 names
-------
 "A"
 "B"
(2 rows)

Returns one to three top items.

A CREATE clause that is not followed by another clause is called a terminal clause. When the Cypher query ends with a terminal clause, no results will be returned from the Cypher function call. However, the Cypher function call still requires a column list definition. When the Cypher query ends with a terminal node, define a single value in the column list definition: no data will be returned in this variable.

SELECT *
FROM cypher('graph_name', $$
    CREATE /* Create clause here, no following clause */
$$) as (a agtype);

 a
---
(0 rows)

Creating a single vertex is done by issuing the following query.

SELECT *
FROM cypher('graph_name', $$
    CREATE (n)
$$) as (v agtype);

 v
---
(0 rows)

Nothing is returned from this query.

Creating multiple vertices is done by separating them with a comma.

SELECT *
FROM cypher('graph_name', $$
    CREATE (n), (m)
$$) as (v agtype);

   a
-------
(0 rows)

To add a label when creating a vertex, use the syntax below.

SELECT *
FROM cypher('graph_name', $$
    CREATE (:Person)
$$) as (v agtype);

   v
-------
(0 rows)

Nothing is returned from this query.

When creating a new vertex with labels, you can add properties at the same time.

SELECT *
FROM cypher('graph_name', $$
    CREATE (:Person {name: 'Andres', title: 'Developer'})
$$) as (n agtype);

   n
-------
(0 rows)

Nothing is returned from this query.

Creating a single node is done by issuing the following query.

SELECT *
FROM cypher('graph_name', $$
    CREATE (a {name: 'Andres'})
    RETURN a
$$) as (a agtype);

                                       a
--------------------------------------------------------------------------------
 {"id": 281474976710660, "label": "", "properties": {"name": "Andres"}}::vertex
(1 row)

The newly-created node is returned.

To create an edge between two vertices, we first get the two vertices. Once the nodes are loaded, we simply create an edge between them.

SELECT *
FROM cypher('graph_name', $$
    MATCH (a:Person), (b:Person)
    WHERE a.name = 'Node A' AND b.name = 'Node B'
    CREATE (a)-[e:RELTYPE]->(b)
    RETURN e
$$) as (e agtype);

                              e
-----------------------------------------------------------------------
{id: 3; startid: 0, endid: 1; label: 'RELTYPE'; properties: {}}::edge
(1 row)

The created edge is returned by the query.

Setting properties on edges is done in a similar manner to how it is done when creating vertices. Note that the values can be any expression.

SELECT *
FROM cypher('graph_name', $$
    MATCH (a:Person), (b:Person)
    WHERE a.name = 'Node A' AND b.name = 'Node B'
    CREATE (a)-[e:RELTYPE {name:a.name + '<->' + b.name}]->(b)
    RETURN e
$$) as (e agtype);

                                          e
---------------------------------------------------------------------------------------------------
 {id: 3; startid: 0, endid: 1; label: 'RELTYPE'; properties: {name: 'Node A<->Node B'}}::edge
(1 row)

The newly-created edge is returned by the example query.

When you use CREATE and a pattern, all parts of the pattern that are not already in scope at this time will be created.

SELECT *
FROM cypher('graph_name', $$
    CREATE p = (andres {name:'Andres'})-[:WORKS_AT]->(neo)<-[:WORKS_AT]-(michael {name:'Michael'})
    RETURN p
$$) as (p agtype);

                     p
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"id": 281474976710661, "label": "", "properties": {"name": "Andres"}}::vertex, {"id": 1407374883553282, "label": "WORKS_AT", "end_id": 281474976710662, "start_id": 281474976710661, "properties": {}}::edge, {"id": 281474976710662, "label": "", "properties": {}}::vertex, {"id": 1407374883553281, "label": "WORKS_AT", "end_id": 281474976710662, "start_id": 281474976710663, "properties": {}}::edge, {"id": 281474976710663, "label": "", "properties": {"name": "Michael"}}::vertex]::path
(1 row)

This query creates three nodes and two relationships in one go, assigns it to a path variable, and returns it.

To delete a vertex, use the DELETE clause.

SELECT *
FROM cypher('graph_name', $$
    MATCH (v:Useless)
    DELETE v
$$) as (v agtype);

   v
-------
(0 rows)

This will delete the vertices (with label Useless) that have no edges. Nothing is returned from this query.

Running a MATCH clause will collect all nodes, use the DETACH option to delete vertice edges first, and then delete the vertex itself.

SELECT *
FROM cypher('graph_name', $$
    MATCH (v:Useless)
    DETACH DELETE v
$$) as (v agtype);

   v
-------
(0 rows)

Nothing is returned from this query.

To delete an edge, use the MATCH clause to find your edges, then add the variable to the DELETE.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'Andres'})-[r:KNOWS]->()
    DELETE r
$$) as (v agtype);

   v
-------
(0 rows)

Nothing is returned from this query.

In apache_age, you can return vertices that have been deleted.

SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'A'})
    DELETE n
    RETURN n
$$) as (a agtype);

                                  a
---------------------------------------------------------------------------
 {"id": 281474976710659, "label": "", "properties": {"name": "A"}}::vertex
(1 rows)

A SET clause that is not followed by another clause is called a terminal clause. When the Cypher query ends with a terminal clause, no results will be returned from the Cypher function call. However, the Cypher function call still requires a column list definition. When the Cypher query ends with a terminal node, define a single value in the column list definition: no data will be returned in this variable.

To set a property on a node or relationship, use SET.

SELECT *
FROM cypher('graph_name', $$
   MATCH (v {name: 'Andres'})
   SET v.surname = 'Taylor'
$$) as (v agtype);

   v
-------
(0 rows)

The newly changed node is returned by the query.

Creating a single vertex is done by issuing the following query.

SELECT *
FROM cypher('graph_name', $$
    MATCH (v {name: 'Andres'})
    SET v.surname = 'Taylor'
    RETURN v
$$) as (v agtype);

                                                  v
-----------------------------------------------------------------------------------------------------
 {id: 3; label: 'Person'; properties: {surname:"Taylor", name:"Andres", age:36, hungry:true}}::vertex
(1 row)

The newly changed vertex is returned by the query.

Normally you remove a property by using REMOVE, but it's sometimes handy to do it using the SET command. One example is if the property comes from a parameter.

SELECT *
FROM cypher('graph_name', $$
    MATCH (v {name: 'Andres'})
    SET v.name = NULL
    RETURN v
$$) as (v agtype);

                                         v
---------------------------------------------------------------------------------------
 {id: 3; label: 'Person'; properties: {surname:"Taylor", age:36, hungry:true}}::vertex
(1 row)

The node is returned by the query, and the name property is now missing.

If you want to set multiple properties in one go, simply separate them with a comma.

SELECT *
FROM cypher('graph_name', $$
MATCH (v {name: 'Andres'})
SET v.position = 'Developer', v.surname = 'Taylor'
RETURN v
$$) as (v agtype);

                                                    v
------------------------------------------------------------------------------------------------------------------------------
{"id": 281474976710661, "label": "", "properties": {"name": "Andres", "surname": "Taylor", "position": "Developer"}}: :vertex
(1 row)

There are no restrictions to using Cypher with CTEs (Common Table Expression).

WITH graph_query as (
    SELECT *
        FROM cypher('graph_name', $$
        MATCH (n)
        RETURN n.name, n.age
    $$) as (name agtype, age agtype)
)
SELECT * FROM graph_query;

 name      | age
-----------+-----
 "Andres"  | 36
 "Tobias"  | 25
 "Peter"   | 35
(3 rows)

A Cypher query can be part of a JOIN clause.

SELECT id,
    graph_query.name = t.name as names_match,
    graph_query.age = t.age as ages_match
FROM schema_name.sql_person AS t
JOIN cypher('graph_name', $$
        MATCH (n:Person)
        RETURN n.name, n.age, id(n)
$$) as graph_query(name agtype, age agtype, id agtype)
ON t.person_id = graph_query.id;

 id | names_match | ages_match
 ---+-------------+------------
 1  | True        | True
 2  | False       | True
 3  | True        | False
(3 rows)

Cypher cannot be used in an expression, the query must exists in the FROM clause of a query. However, if the Cypher query is placed in a subquery, it will behave as any SQL style query.

SELECT t.name FROM schema_name.sql_person AS t
where t.name = (
    SELECT a
    FROM cypher('graph_name', $$
          MATCH (v)
        RETURN v.name
    $$) as (name varchar(50))
    ORDER BY name
    LIMIT 1);

   name   | age
----------+-----
 "Andres" | 36
(1 rows)

SELECT t.name, t.age FROM schema_name.sql_person as t
where t.name in (
    SELECT *
    FROM cypher('graph_name', $$
        MATCH (v:Person)
        RETURN v.name
    $$) as (a agtype));

  name     | age
-----------+-----
 "Andres"  | 36
 "Tobias"  | 25
 "Peter"   | 35
(3 rows)

SELECT t.name, t.age
FROM schema_name.sql_person as t
WHERE EXISTS (
    SELECT *
    FROM cypher('graph_name', $$
      MATCH (v:Person)
        RETURN v.name, v.age
    $$) as (name agtype, age agtype)
    WHERE name = t.name AND age = t.age
);

   name    | age
-----------+-----
 "Andres"  | 36
 "Tobias"  | 25
 "Peter"   | 35
(3 rows)

SELECT graph_1.name, graph_1.age, graph_2.license_number
FROM cypher('graph_1', $$
    MATCH (v:Person)
    RETURN v.name, v.age
$$) as graph_1(col_1 agtype, col_2 agtype, col_3 agtype)
JOIN cypher('graph_2', $$
    MATCH (v:Doctor)
    RETURN v.name, v.license_number
$$) as graph_2(name agtype, license_number agtype)
ON graph_1.name = graph_2.name

   name    | age | license_number
-----------+-----+----------------
 "Andres"  | 36  | 1234567890
(1 rows)

Cypher can run a read query within a prepared statement. When using parameters with stored procedures, an SQL parameter must be placed in the Cypher function call. See The apache_age Query Format for details.

A Cypher parameter is in the format of a "$" followed by an identifier. Unlike Postgres Pro parameters, Cypher parameters start with a letter, followed by an alphanumeric string of arbitrary length. Example: $parameter_name

Preparing prepared statements in Cypher is an extension of Postgres Pro stored procedure system. Use the PREPARE clause to create a query with the Cypher function call in it. Do not place Postgres Pro style parameters in the Cypher query call, instead place Cypher parameters in the query and place a Postgres Pro parameter as the third argument in the Cypher function call.

PREPARE cypher_stored_procedure(agtype) AS
SELECT *
FROM cypher('expr', $$
    MATCH (v:Person)
    WHERE v.name = $name //Cypher parameter
    RETURN v
$$, $1) //An SQL Parameter must be placed in the Cypher function call
AS (v agtype);

When executing the prepared statement, place an agtype map with the parameter values where the Postgres Pro parameter in the Cypher function call is. The value must be an agtype map or an error will be thrown. Exclude the "$" for parameter names.

EXECUTE cypher_prepared_statement('{'name': 'Tobias'}');

Cypher commands can be run in PL/pgSQL functions without restriction.

SELECT *
FROM cypher('imdb', $$
    CREATE (toby:actor {name: 'Toby Maguire'}),
        (tom:actor {name: 'Tom Holland'}),
        (willam:actor {name: 'Willam Dafoe'}),
        (robert:actor {name: 'Robert Downey Jr'}),
        (spiderman:movie {title: 'Spiderman'}),
        (no_way_home:movie {title: 'Spiderman: No Way Home'}),
        (homecoming:movie {title: 'Spiderman: Homecoming'}),
        (ironman:movie {title: 'Ironman'}),
        (tropic_thunder:movie {title: 'Tropic Thunder'}),
        (toby)-[:acted_in {role: 'Peter Parker', alter_ego: 'Spiderman'}]->(spiderman),
        (willam)-[:acted_in {role: 'Norman Osborn', alter_ego: 'Green Goblin'}]->(spiderman),
        (toby)-[:acted_in {role: 'Toby Maguire'}]->(tropic_thunder),
        (robert)-[:acted_in {role: 'Kirk Lazarus'}]->(tropic_thunder),
        (robert)-[:acted_in {role: 'Tony Stark', alter_ego: 'Ironman'}]->(homecoming),
        (tom)-[:acted_in {role: 'Peter Parker', alter_ego: 'Spiderman'}]->(homecoming),
        (tom)-[:acted_in {role: 'Peter Parker', alter_ego: 'Spiderman'}]->(no_way_home),
        (toby)-[:acted_in {role: 'Peter Parker', alter_ego: 'Spiderman'}]->(no_way_home),
        (willam)-[:acted_in {role: 'Norman Osborn', alter_ego: 'Green Goblin'}]->(no_way_home)
$$) AS (a agtype);

The example of function creation is shown below.

CREATE OR REPLACE FUNCTION get_all_actor_names()
RETURNS TABLE(actor agtype)
LANGUAGE plpgsql
AS $BODY$
BEGIN
    LOAD 'age';
    SET search_path TO ag_catalog;

    RETURN QUERY
    SELECT *
    FROM ag_catalog.cypher('imdb', $$
        MATCH (v:actor)
        RETURN v.name
    $$) AS (a agtype);
END
$BODY$;

Execute the query:

SELECT * FROM get_all_actor_names();

       actor
--------------------
 "Toby Maguire"
 "Tom Holland"
 "Willam Dafoe"
 "Robert Downey Jr"
(4 rows)

CREATE OR REPLACE FUNCTION get_actors_who_played_role(role agtype)
RETURNS TABLE(actor agtype, movie agtype)
LANGUAGE plpgsql
AS $function$
DECLARE sql VARCHAR;
BEGIN
        load 'age';
        SET search_path TO ag_catalog;

        sql := format('
        SELECT *
        FROM cypher(''imdb'', $$
            MATCH (actor)-[:acted_in {role: %s}]->(movie:movie)
            RETURN actor.name, movie.title
        $$) AS (actor agtype, movie agtype);
    ', role);

        RETURN QUERY EXECUTE sql;

END
$function$;

SELECT * FROM get_actors_who_played_role('"Peter Parker"');

     actor      |          movie
----------------+--------------------------
 "Toby Maguire" | "Spiderman: No Way Home"
 "Toby Maguire" | "Spiderman"
 "Tom Holland"  | "Spiderman: Homecoming"
 "Tom Holland"  | "Spiderman: No Way Home"
(4 rows)

apache_age does not support SQL being directly written in Cypher. However, with user-defined functions you can write SQL queries and call them in a Cypher command.

Create a function:

CREATE OR REPLACE FUNCTION public.get_event_year(name agtype) returns agtype AS $$
    SELECT year::agtype
    FROM history AS h
    WHERE h.event_name = name::text
    LIMIT 1;
$$ LANGUAGE sql;

SELECT * FROM cypher('graph_name', $$
    MATCH (e:event)
    WHERE e.year < public.get_event_year(e.name)
    RETURN n.name
$$) as (n agtype);

        n
-------------------
 "Apache Con 2021"
(1 row)