PGX 19.1.0
Documentation
Note: This feature is only available in the following software packages: PGX on the Oracle Tech Network, Oracle Big Data Spatial and Graph

Running Graph Pattern Matching Queries (PGQL)

System Requirements

Applications that run on top of PGX Embedded Mode as opposed to Server-Client mode, should make sure that the maximum JVM heap space is set to at least 1 gigabyte (-Xmx1g), or a StackOverflowError may occur.

Submitting a Query and Getting a Result Set

PgxGraph provides methods to submit a graph pattern matching query written in PGQL:

PgxFuture<PgqlResultSet> queryPgqlAsync(String pgqlString)

and its blocking equivalent:

PgqlResultSet queryPgql(String pgqlString)

PgxGraph also provides the following beta methods for submitting pattern matching queries:

PgxFuture<PgqlResultSet> executePgqlAsync(String query)
PgxFuture<PgxGraph> cloneAndExecutePgqlAsync(String query)
PgxFuture<PgxGraph> cloneAndExecutePgqlAsync(String query, String graphName)

and their blocking equivalents:

PgqlResultSet executePgql(String query)
PgxGraph cloneAndExecutePgql(String query)
PgxGraph cloneAndExecutePgql(String query, String graphName)

Note that the above methods are beta functions, their syntax and semantics might change in future PGX releases.

Both queryPgql() and executePgql() submit the graph pattern matching queries to the graph on which it is invoked. Note that MODIFY queries are only accepted by executePgql(), whereas SELECT queries can be run using both methods. The methods return a PgqlResultSet that contains the result of the SELECT query, or null in case of a MODIFY query.

The cloneAndExecutePgql() method first clones the graph, and after executes the query on the cloned graph and returns it, while the original graph remains intact. If the graphName argument is specified (and not null), it will be the name of the returned graph (if the name is taken, the execution will fail). If the name is not specified, it will be automatically generated. The function accepts both SELECT and MODIFY queries, however in case of a SELECT, the returned graph will be the exact copy of the original one.

Note that cloneAndExecutePgql() is only supported on graphs where the clone() method is supported. For this reason, heterogeneous graphs cannot be queried using this method.

Examples

In the following block, there are some examples for submitting SELECT pattern matching queries to a graph:

rs = g.queryPgql("SELECT v.age MATCH (v) WHERE v.salary > 1000")
rs = g.executePgql("SELECT v.age MATCH (v) WHERE v.salary > 1000")

The two above examples are completely equivalent, they give the same result.

MODIFY queries can be applied in-place for the graph using the executePgql() method, for example each persons age in the graph can be increased by one using the following query:

g.executePgql("MODIFY/*beta*/ UPDATE v SET PROPERTY v.age = v.age + 1 MATCH (v)")

The same modifications can be run on a new graph instance using the cloneAndExecutePgql() method:

g2 = g.cloneAndExecutePgql("MODIFY/*beta*/ UPDATE v SET PROPERTY v.age = v.age + 1 MATCH (v)", "new_graph")

In this case the graph g will remain untouched by the query, and the g2 graph will be updated.

Note that the following query would also do the update

g.cloneAndExecutePgql("MODIFY/*beta*/ new_graph UPDATE v SET PROPERTY v.age = v.age + 1 MATCH (v)", "new_graph")

However, the following query would fail, as the target of the update is not g but the new graph:

g.cloneAndExecutePgql("MODIFY/*beta*/ name_of_g UPDATE v SET PROPERTY v.age = v.age + 1 MATCH (v)", "new_graph")

Getting Result Element Information from a Result Set

A graph pattern matching result consists of a list of result elements. We allow the following types for an result element.

  • Integer
  • Long
  • Float
  • Double
  • Boolean
  • Date
  • Time
  • Timestamp
  • Time with timezone
  • Timestamp with timezone
  • Vertex
  • Edge
  • Vertex labels
  • Point2D

Since a result element can have any of the above types, we provide a way to get information on each result element. PgqlResultSet has the following method to get a list of result element information.

List<PgqlResultElement> getPgqlResultElements()

ith PgqlResultElement instance in the list contains type and variable name information on the ith result element in a graph pattern matching result. They are accessible via the following PgqlResultElement methods:

PgqlResultElement.Type getElementType()
IdType getVertexEdgeIdType()
String getVarName()

Iterating over Results

Once you get a PgqlResultSet object, you can iterate over the results, because PgqlResultSet is an Iterable<PgxResult> instance.

Then, you can iterate over the graph pattern matching results using the for-each style loop.

Each PgxResult instance contains a specific pattern matching result. You can get a java object for each result element by its index or variable name. The index of the first result element is 1.

Object getObject(int elementIdx)
Object getObject(int variableName)

Alternatively, if you are aware of a type of a result element from your knowledge on a query or from a PgqlResultElement instance, you can use the following type-specific getter methods:

Integer getInteger(int elementIdx)
Integer getInteger(String variableName)
Long getLong(int elementIdx)
Long getLong(String variableName)
Float getFloat(int elementIdx)
Float getFloat(String variableName)
Double getDouble(int elementIdx)
Double getDouble(String variableName)
Double getBoolean(int elementIdx)
Double getBoolean(String variableName)
LocalDate getDate(int elementIdx)
LocalDate getDate(String variableName)
LocalTime getTime(int elementIdx)
LocalTime getTime(String variableName)
LocalDateTime getTimestamp(int elementIdx)
LocalDateTime getTimestamp(String variableName)
OffsetTime getTimeWithTimezone(int elementIdx)
OffsetTime getTimeWithTimezone(String variableName)
OffsetDateTime getTimestampWithTimezone(int elementIdx)
OffsetDateTime getTimestampWithTimezone(String variableName)
Date getLegacyDate(int elementIdx)
Date getLegacyDate(String variableName)
PgxVertex<ID> getVertex(int elementIdx)
PgxVertex<ID> getVertex(String variableName)
PgxEdge getEdge(int elementIdx)
PgxEdge getEdge(String variableName)
Set<String> getVertexLabels(int elementIdx)
Set<String> getVertexLabels(String variableName)
Point2D getPoint2D(int elementIdx)
Point2D getPoint2D(String variableName)

PGX supports the new Java 8 temporal data types (java.time.*) but also supports the legacy java.util.Date through getLegacyDate(..).

We also provide a print method for PqglResultSet to print out the result set without iterating over each result.

PgqlResultSet print(PrintStream stream, int from, long numResults)
PgqlResultSet print(int from, long numResults)
PgqlResultSet print(long numResults)
PgqlResultSet print()

The print methods prints numResults results to stream after skipping from - 1 results. Without stream, the method prints the results to System.out. Without from, the method does not skip any results. Without numResults, the method print all results.

The methods return the result set itself. This allows for method chaining, for example:

g.queryPgql("...").print().close();

Creating vertex/edge sets out of a PGQL result set

Users can create a vertex/edge set out of a given PGQL result set. For example, given the result set generated by the following line:

PgqlResultSet resultSet = g.queryPgql("SELECT x MATCH (x) WHERE x.age > 24");

one can create a vertex set containing all the vertices with age greater than 24, by creating a vertex filter on the result set:

VertexSet vertexSet = g.getVertices(new ResultSetVertexFilter(resultSet, "x"));

Prepared Statements

Prepared statements provide a robust way of fighting injection and provides additional opportunity for query optimization. The prepared statment interface of PGX follows JDBC's PreparedStatement interface very closely (same methods and method names). Furthermore, the PGQL syntax for bind variables uses the question mark symbol (?) in places where literals are allowed, just like in SQL.

An example of a query with three bind variables is as follows:

SELECT * MATCH (n:Person) WHERE n.name = ? LIMIT ? OFFSET ?

Bind variables are allowed at any location where a literal is allowed, in the PATH, SELECT, MATCH, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT and OFFSET clauses (i.e. in all the clauses) as well as in PATH pattern definitions.

Note, that currently preparePgql() only accepts SELECT queries, MODIFY queries cannot be prepared.

PgxGraph provides the following methods to prepare a PGQL query:

PgxFuture<PgxPreparedStatement> preparePgqlAsync(String pgqlString)

and its blocking equivalent:

PgxPreparedStatement preparePgql(String pgqlString)

Similarly, PgxPreparedStatement provides blocking and non-blocking methods for executing a prepared PGQL query:

PgxFuture<PgqlResultSet> executeQueryAsync()
PgqlResultSet executeQuery()

The following example creates a prepared statement for a query with three bind variables. Bindings for the variables are first set through the PreparedStatement API and then the query gets executed. In the example, the statement is used a second time, to execute another query. The second and thrid bind values (previously set) are reused, but the first bind variable is changed to a new string value. Then, the prepared statement gets executed again.

PgxPreparedStatement stmnt = pgxGraph.preparePgql("SELECT m.name " +  
     "MATCH (n:Person) -[:?]-> (m:Person) WHERE n.name = ? AND n.age = ?");
stmnt.setString(1, "likes");
stmnt.setString(2, "John");
stmnt.setInt(3, 40);
ResultSet resultSet1 = stmnt.executeQuery();

stmnt.setString(1, "dislikes"); 
ResultSet resultSet2 = stmnt.executeQuery();

stmnt.close();

When closing a prepared statement, all the result sets associated with it are automatically closed in case they were not closed yet.

The following example shows a PGQL query with an IN predicate, which tests an expression for membership in an array of values. In this case, a bind variable can be placed in the position of the array literal. The method setArray of PreparedStatement API is used in this example to set the bind variable to the given List of String values.

PgxPreparedStatement stmnt = pgxGraph.preparePgql("SELECT n.name MATCH (n:Person) WHERE n.name IN ?");
List arr = new ArrayList<String>();
arr.add("Emily");
arr.add("Carl");
stmnt.setArray(1, arr)
stmnt.executeQuery().print().close();

Notice that only some of the Java types are allowed for the list of values. Also, the type of the expression that is being tested for membership (the type of n.name in the example) should be compatible with the type of the values in the list (type String in the example). The following table specifies which Java types are allowed and its compatible expression types:

+------------------------------------------------------+
| Java type      | Expression type                     |
+------------------------------------------------------+
| Integer        | Integer, Long, Float, Double        |
| Long           | Integer, Long, Float, Double        |
| Float          | Integer, Long, Float, Double        |
| Double         | Integer, Long, Float, Double        |
| Boolean        | Boolean                             |
| String         | String                              |
| LocalDate      | Date                                |
| LocalTime      | Time, Time with time zone           |
| OffsetTime     | Time, Time with time zone           |
| LocalDateTime  | Timestamp, Timestamp with time zone |
| OffsetDateTime | Timestamp, Timestamp with time zone |
+------------------------------------------------------+

Explain the query execution plan

Every query uses an execution plan to guide the way a query is processed. The execution plan of a query can be obtained using the function explainPgql (simply replace the function queryPgql with explainPgql). The result of explainPgql is the root of the PGQL execution plan tree. The whole tree can be printed by calling print() on this root node. Every node contains information about a single step in the execution plan of the query, e.g., the operator, cardinality estimate and cost estimate.

FROM clause

A FROM clause can be used in a PGQL query. The FROM clause specifies the graph on which the query will be executed. A query with a FROM clause can be executed on a PgxSession instance.

session.queryPgql("SELECT x.name FROM graph_name MATCH (x)");
session.preparePgql("SELECT x.name FROM graph_name MATCH (x) WHERE x.age = ?")