PGX 3.2.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)

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.

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 = ?")