Executing PGQL Queries Against PGQL Property Graphs

This topic explains how you can execute PGQL queries directly against PGQL property graphs in your database.

The PGQL query execution flow is shown in the following figure.

Figure: PGQL on PGQL Property Graphs in Database

Description of image follows

Description of the illustration pg_views_diagram.png

The basic execution flow is:

  1. The PGQL query is submitted to PGQL on RDBMS through a Java API.

  2. The PGQL query is translated into SQL statements using the internal metadata tables for PGQL property graphs.

  3. The translated SQL is submitted to the database by JDBC.

  4. The SQL result set is wrapped as a PGQL result set and returned to the caller.

Supported PGQL Features and Limitations for PGQL Property Graphs

Learn about the supported PGQL features and limitations for PGQL property graphs.

The following table describes the complete list of supported and unsupported PGQL features for PGQL property graphs:

Table: Supported PGQL Functionalities and Limitations for PGQL Property Graphs

Feature PGQL on PGQL Property Graphs
CREATE PROPERTY GRAPH Supported
DROP PROPERTY GRAPH Supported
Fixed-length pattern matching Supported
Variable-length pattern matching goals Supported:
  • Reachability
  • Path search prefixes:
    • ANY
    • ANY SHORTEST
    • SHORTEST k
    • ALL
  • Path modes:
    • WALK
    • TRAIL
    • SIMPLE
    • ACYCLIC

Limitations:

  • Path search prefixes:
    • ALL SHORTEST
    • ANY CHEAPEST
    • CHEAPEST k
Variable-length pattern matching quantifiers Supported:
  • *
  • +
  • ?
  • { n }
  • { n, }
  • { n, m }
  • { , m }
Variable-length path unnesting Supported:
  • ONE ROW PER STEP

    Limitation: Quantifier * not supported

Not supported:

  • ONE ROW PER VERTEX
OPTIONAL MATCH Not supported
GROUP BY Supported
HAVING Supported
Aggregations Supported:
  • COUNT
  • MIN, MAX, AVG, SUM
  • LISTAGG
  • JSON_ARRAYAGG

Limitations:

  • ARRAY_AGG
DISTINCT
  • SELECT DISTINCT
  • Aggregation with DISTINCT (such as, COUNT(DISTINCT e.prop))
 Supported
SELECT v.* Supported
ORDER BY (+ASC/DESC), LIMIT, OFFSET Supported
Data Types All available Oracle RDBMS data types supported
JSON Supported:
  • JSON storage:
    • JSON strings (VARCHAR2)
    • JSON objects
  • JSON functions:

    Any JSON function call that follows the syntax, json_function_name(arg1, arg2,...). For example:

    json_value(department_data, '$.department')

Limitations:
  • Simple Dot Notation
  • Any optional clause in a JSON function call (such as RETURNING, ERROR, and so on) is not supported. For example:

    json_value(department_data, '$.employees[1].hireDate' RETURNING DATE)
Operators Supported:
  • Relational: +, -, *, /, %, - (unary minus)
  • Arithmetic: =, <>, <, >, <=, >=
  • Logical: AND, OR, NOT
  • String: || (concat)
Functions and predicates

Supported are all available functions in the Oracle RDBMS that take the form function_name(arg1, arg2, ...) with optional schema and package qualifiers.

Supported PGQL functions/predicates:

  • IS NULL, IS NOT NULL
  • JAVA_REGEXP_LIKE (based on CONTAINS)
  • LOWER, UPPER
  • SUBSTRING
  • ABS, CEIL/CEILING, FLOOR, ROUND
  • EXTRACT
  • ID, VERTEX_ID, EDGE_ID
  • LABEL, IS [NOT] LABELED
  • ALL_DIFFERENT
  • CAST
  • CASE
  • IN and NOT IN
  • IS [NOT] SOURCE [OF], IS [NOT] DESTINATION [OF] (Only supported with Oracle AI Database 26ai)
  • VERTEX_EQUAL, EDGE_EQUAL

Limitations:

  • LABELS
  • IN_DEGREE, OUT_DEGREE
User-defined functions Supported:
  • PL/SQL functions
  • Functions created via the Multilingual Engine (MLE)
Subqueries:
  • Scalar subqueries
  • EXISTS and NOT EXISTS subqueries
  • LATERAL subquery
Supported:
  • EXISTS and NOT EXISTS subqueries
  • Scalar subqueries
  • LATERAL subquery
GRAPH_TABLE operator Supported

Extension:

  • BASE GRAPHS clause in CREATE PROPERTY GRAPH for creating graphs based on metadata of other graphs
INSERT/UPDATE/DELETE Supported for Oracle Database 19c and later
INTERVAL literals and operations Not supported

Additional Information on Supported PGQL Features with Examples

The following PGQL features are supported in PGQL property graphs:

The following are a few PGQL features which are not supported:

SQL Translation for a PGQL Query

You can obtain the SQL translation for a PGQL query through the translateQuery() and getSqlTranslation() methods in PgqlStatement and PgqlPreparedStatement.

Using the raw SQL for a PGQL query you can:

Several options are available to influence PGQL query translation and execution. The following are the main ways to set query options:

The following table summarizes the available query arguments for PGQL translation and execution.

Table: PGQL Translation and Execution Options

Option Default Explicit Argument Options Flag JVM Argument
Degree of parallelism 0 parallel none none
Timeout Unlimited timeout none none
Dynamic sampling 2 dynamicSampling none none
Maximum number of results Unlimited maxResults none none
Reverse path optimization True None REVERSE_PATH=F oracle.pg.rdbms.pgql.reversePath=false
Pushing source filter optimization True None PUSH_SRC_HOPS=F oracle.pg.rdbms.pgql.pushSrcHops=false
Pushing destination filter optimization False None PUSH_DST_HOPS=T oracle.pg.rdbms.pgql.pushDstHops=true
Creation of views in shortest path translation False None SP_CREATE_VIEW=T oracle.pg.rdbms.pgql.spCreateView=true
Creation of tables in shortest path translation True None SP_CREATE_TABLE=F oracle.pg.rdbms.pgql.spCreateTable=false
Use of CLOB for path expressions True None EXP_PATH_CLOB=F oracle.pg.rdbms.pgql.expPathClob=false
Escaping in ID function True None ESCAPE_IDS=F oracle.pg.rdbms.pgql.escapeIDs=false

Performance Considerations for PGQL Queries

The following sections explain a few recommended practices for query performance.

Recursive Queries

The following indexes are recommended in order to speed up execution of recursive queries:

Note: You can also create index on (source key, destination key).

For example, consider the following CREATE PROPERTY GRAPH statement:

CREATE PROPERTY GRAPH people
  VERTEX TABLES(
    person
      KEY ( id )
      LABEL person
      PROPERTIES( name, age )
  )
  EDGE TABLES(
    knows
      key (person1, person2)
      SOURCE KEY ( person1 ) REFERENCES person (id)
      DESTINATION KEY ( person2 ) REFERENCES person (id)
      NO PROPERTIES
  )
  OPTIONS ( PG_PGQL )

And also consider the following query:

SELECT COUNT(*)
FROM MATCH ANY SHORTEST ( (n:Person) -[e:knows]->* (m:Person) )
WHERE n.id = 1234

In order to improve performance of the recursive part of the preceding query, the following indexes must exist:

Composite Vertex Keys

For composite vertex keys, query execution can be optimized with the creation of function-base indexes on the key columns:

Note: You can also create index on (source key columns, destination key columns).

For example, consider the following CREATE PROPERTY GRAPH statement:

CREATE PROPERTY GRAPH people
  VERTEX TABLES(
    person
      KEY ( id1, id2 )
      LABEL person
      PROPERTIES( name, age )
  )
  EDGE TABLES(
    knows
      key (id)
      SOURCE KEY ( id1person1, id2person1 ) REFERENCES person (id1,id2)
      DESTINATION KEY ( id1person2, id2person2 ) REFERENCES person (id1,id2)
      NO PROPERTIES
  )
  OPTIONS ( PG_PGQL )

And also consider the following query:

SELECT COUNT(*)
FROM MATCH ANY SHORTEST ( (n:Person) -[e:knows]->* (m:Person) )
WHERE n.id = 1234

In order to improve performance of the recursive part of the preceding query, the following indexes must exist:

If some of the columns in a composite vertex key is a string column, the column needs to be comma-escaped in the function-base index creation.

For example, if column ID1 in table PERSON of the preceding example is of type VARCHAR2(10), you need to escape the comma for the column as follows:

replace(ID1, ',', '\,')

So, the indexes to improve performance will result as shown:

Using Query Optimizer Hints

The following hints can be used to influence SQL translation of a PGQL query:

The preceding hints can be configured as options parameter in the following Java API methods:

For example, consider the following PGQL query:

SELECT v
1.name AS v1, v2.name AS v2, v3.name As v3
FROM MATCH (v1:Person)-[e1:friendOf]->(v2:Person),
MATCH ANY (v2:Person)-[e2:friendOf]->*(v3:Person)
WHERE v1.name= 'Bob'

When the preceding query is executed using the default option for PUSH_SRC_HOPS, the output for start_nodes_translation displays the filter expression as shown:

System.out.println(pgqlStatement.translateQuery(pgql).getSqlTranslation())
...
...
start_nodes_translation => (to_clob('SELECT ''PERSONS'' AS "src_table", e1.person_b AS "src_key"
FROM "GRAPHUSER"."PERSONS" "V1", "GRAPHUSER"."FRIENDSHIPS" "E1" WHERE (((e1.person_a = v1.person_id) AND NOT(e1.person_b IS NULL)) AND (v1.name = ''Bob''))')),
     end_nodes_translation => (to_clob('SELECT ''PERSONS'' AS "dst_table", v3.person_id AS "dst_key"
FROM "GRAPHUSER"."PERSONS" "V3"')),
...
...

If the preceding query is executed with the hint PUSH_SRC_HOPS=F, then the query is translated into SQL as shown:

System.out.println(pgqlStatement.translateQuery(pgql,"PUSH_SRC_HOPS=F").getSqlTranslation())

...
...start_nodes_translation => (to_clob('SELECT ''PERSONS'' AS "src_table", v2.person_id AS "src_key"
FROM "GRAPHUSER"."PERSONS" "V2"')),
     end_nodes_translation => (to_clob('SELECT ''PERSONS'' AS "dst_table", v3.person_id AS "dst_key"
FROM "GRAPHUSER"."PERSONS" "V3"')),
...
...

Speed Up Query Translation Using Graph Metadata Cache and Translation Cache

The following global caches help to speed up PGQL query translation:

You can configure the caches using the following Java APIs:

These preceding methods are part of the PgqlConnection class. Separate caches are maintained for each database user such that cached objects are shared between different PgqlConnection objects if they have the same connection URL and user underneath.

By default, both the metadata and translation caches are refreshed every 1000ms (default value) if they are enabled. This makes it easy to sync the metadata cache in case you are modifying one graph through multiple JVMs. Also, you can increase the time (in milliseconds) taken for refreshing the cache by calling the setGraphMetadataRefreshInterval(long interval) function.

Using the Java and Python APIs to Run PGQL Queries

You can run PGQL queries on PGQL property graphs using the Java API in the oracle.pg.rdbms.pgql package. Also, you can use the Python OPG4Py package for executing PGQL queries against the graph data in the database. This package contains a sub-package Pgql with one or more modules that wraps around the Java API in the oracle.pg.rdbms.pgql package.

Creating a PGQL Property Graph

You can create a PGQL property graph using the CREATE PROPERTY GRAPH statement.

Example: Creating a PGQL Property Graph

The following example describes the creation of a PGQL property graph.

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
opg4j> conn.setAutoCommit(false)
opg4j> var pgql =
...> "CREATE PROPERTY GRAPH bank_graph "
...> + "VERTEX TABLES ( bank_accounts AS Accounts "
...> + "KEY (id) "
...> + "LABEL Accounts "
...> + "PROPERTIES (id, name) "
...> + ") "
...> + "EDGE TABLES ( bank_transfers AS Transfers "
...> + "KEY (txn_id) "
...> + "SOURCE KEY (src_acct_id) REFERENCES Accounts (id) "
...> + "DESTINATION KEY (dst_acct_id) REFERENCES Accounts (id) "
...> + "LABEL Transfers "
...> + "PROPERTIES (src_acct_id, dst_acct_id, amount, description) "
...> + ") OPTIONS (PG_PGQL) "
opg4j> pgqlStmt.execute(pgql)
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;


/*
 * This example shows how to create a PGQL property graph.
 */
public class PgqlCreate
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;

    try {
      //Get a jdbc connection
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);

      // Create a PGQL Statement
      pgqlStmt = pgqlConn.createStatement();
      // Execute PGQL Query
      String pgql =
        "CREATE PROPERTY GRAPH " + graph + " " +
        "VERTEX TABLES ( bank_accounts as Accounts " +
        "KEY (id) " +
        "LABEL \"Accounts\"" +
        "PROPERTIES (id, name)" +
        ") " +
        "EDGE TABLES ( bank_transfers as Transfers " +
        "KEY (txn_id) " +
        "SOURCE KEY (src_acct_id) REFERENCES Accounts (id) " +
        "DESTINATION KEY (dst_acct_id) REFERENCES Accounts (id) " +
        "LABEL \"Transfers\"" +
        "PROPERTIES (src_acct_id, dst_acct_id, amount, description)" +
        ") OPTIONS (PG_PGQL) ";

      // Print the results
      pgqlStmt.execute(pgql);
    }
    finally {
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}
>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "jdbc:oracle:thin:@localhost:1521/orclpdb")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql = """
...         CREATE PROPERTY GRAPH bank_graph
...         VERTEX TABLES (
...           bank_accounts as Accounts
...             LABEL Accounts
...             PROPERTIES (id, name)
...         )
...         EDGE TABLES (
...           bank_transfers as Transfers
...             KEY (txn_id)
...             SOURCE KEY (src_acct_id) REFERENCES Accounts(id)
...             DESTINATION KEY (dst_acct_id) REFERENCES Accounts (id)
...             LABEL TRANSFERS
...             PROPERTIES (src_acct_id, dst_acct_id, amount, description)
...         ) OPTIONS(PG_PGQL)
... """
>>> pgql_statement.execute(pgql)
False

You can verify the PGQL property graph creation by checking the metadata tables that get created in the database.

Executing PGQL SELECT Queries

You can run PGQL SELECT queries as described in the following examples.

Example: Running a Simple SELECT Query Using PgqlStatement and PgqlResultSet

In the following example, PgqlConnection is used to obtain a PgqlStatement. Then, it calls the executeQuery method of PgqlStatement, which returns a PgqlResultSet object. PgqlResultSet provides a print() method, which displays results in a tabular mode.

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> pgqlConn.setGraph("BANK_GRAPH")
opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
opg4j> String s = "SELECT n.* FROM MATCH (n:Accounts) LIMIT 3"
opg4j> var resultSet = pgqlStmt.executeQuery(s)
opg4j> resultSet.print() //Prints the query result set
+---------------+
| ID | NAME     |
+---------------+
| 1  | Account1 |
| 2  | Account2 |
| 3  | Account3 |
+---------------+
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;

/*
 * This example shows how to execute a SELECT query on a PGQL property graph.
 */
public class PgqlExample1
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {
      //Get a jdbc connection
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PGQL Statement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL Query
      String query = "SELECT n.* FROM MATCH (n:Accounts) LIMIT 5";
      rs = pgqlStmt.executeQuery(query);

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
         rs.close();
         }
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}
>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "<jdbcUrl>")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql_conn.set_graph("BANK_GRAPH")
>>> s = "SELECT n.* FROM MATCH (n:Accounts) LIMIT 3"
>>> pgql_statement.execute_query(s)
>>> pgql_result_set = pgql_statement.execute_query(s)
>>> pgql_result_set.print()
+---------------+
| ID | NAME     |
+---------------+
| 1  | Account1 |
| 2  | Account2 |
| 3  | Account3 |
+---------------+
>>> pgql_result_set
PgqlResultSet(java_pgql_result_set: oracle.pg.rdbms.pgql.PgqlResultSet, # of results: 3)

Also, you can convert the PGQL result set obtained in the preceding code to a Pandas dataframe using the to_pandas() method.

Note: The pandas package must be installed in your system to successfully execute the call to to_pandas(). This package is automatically installed at the time of the Python client installation for Python 3.9. However, if your call to to_pandas() fails, verify if the pandas module is installed in your system. In case the module is found missing or your Python version differs from the earlier mentioned versions, then install the pandas package manually.

Example: Running a SELECT Query Using PgqlPreparedStatement

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> pgqlConn.setGraph("BANK_GRAPH");
opg4j> String s = "SELECT n.* FROM MATCH (n:Accounts) LIMIT ?"
opg4j> var ps = pgqlConn.prepareStatement(s, 0 /* timeout */, 4 /* parallel */, 2 /* dynamic sampling */, -1 /* max results */, null /* match options */, null /* options */)
opg4j> ps.setInt(1, 3)
opg4j> var rs = ps.executeQuery()
opg4j> rs.print() //Prints the query result set
+---------------+
| ID | NAME     |
+---------------+
| 1  | Account1 |
| 2  | Account2 |
| 3  | Account3 |
+---------------+
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.*;

public class PgqlExample2
{
  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {
      //Get a jdbc connection
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);


      // Execute PGQL Query
      String s = "SELECT n.* FROM MATCH (n:Accounts) LIMIT ?";
      PgqlPreparedStatement pStmt = pgqlConn.prepareStatement(s, 0, 4 , 2 , -1 , null , null);
      pStmt.setInt(1,3);
      rs = pStmt.executeQuery();

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
         rs.close();
         }
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}
>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "<jdbcUrl>")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql_conn.set_graph("BANK_GRAPH")
>>> s = "SELECT n.* FROM MATCH (n:Accounts) LIMIT ?"
>>> ps = pgql_conn.prepare_statement(s, timeout=0, parallel=4, dynamicSampling=2, maxResults=-1, matchOptions=None, options=None)
>>> ps.set_int(1,3)
>>> ps.execute_query().print()
+---------------+
| ID | NAME     |
+---------------+
| 1  | Account1 |
| 2  | Account2 |
| 3  | Account3 |
+---------------+

Example: Running a SELECT Query with Grouping and Aggregation

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> pgqlConn.setGraph("BANK_GRAPH")
opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
opg4j> String query = "SELECT v1.id, COUNT(v2) AS numTxns "+
...>         "FROM MATCH (v1)-[e IS Transfers]->(v2) "+
...>         "GROUP BY v1 "+
...>         "ORDER BY numTxns DESC "+
...>         "LIMIT 3"
opg4j> var resultSet = pgqlStmt.executeQuery(query)
opg4j> resultSet.print() //Prints the query result set
+---------------+
| ID  | NUMTXNS |
+---------------+
| 687 | 6       |
| 195 | 5       |
| 192 | 5       |
+---------------+
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;


/*
 * This example shows how to execute a SELECT query with aggregation .*/
public class PgqlExample3
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {
      //Get a jdbc connection
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PGQL Statement
      pgqlStmt = pgqlConn.createStatement();

      // Execute PGQL Query
      String query =
        "SELECT v1.id, COUNT(v2) AS numTxns "+
        "FROM MATCH (v1)-[e IS Transfers]->(v2) "+
        "GROUP BY v1 "+
        "ORDER BY numTxns DESC";

      rs = pgqlStmt.executeQuery(query);
      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
         rs.close();
         }
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}
>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "<jdbcUrl>")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql_conn.set_graph("BANK_GRAPH")
>>> query = """
...          SELECT v1.id, COUNT(v2) AS numtxns
...          FROM MATCH (v1)-[e IS Transfers]->(v2)
...          GROUP BY v1
...          ORDER BY numtxns DESC
...          LIMIT 3
...          """
>>> pgql_statement.execute_query(query).print()
+---------------+
| ID  | NUMTXNS |
+---------------+
| 687 | 6       |
| 195 | 5       |
| 192 | 5       |
+---------------+

Example: Showing a PGQL Path Query

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>");
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> pgqlConn.setGraph("BANK_GRAPH")
opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
opg4j> String query = "PATH onehop AS ()-[IS transfers]->() "+
...>         "SELECT v1.id FROM MATCH (v1)-/:onehop/->(v2) "+
...>         "WHERE v2.id = 365"
opg4j> var resultSet = pgqlStmt.executeQuery(query)
opg4j> resultSet.print() //Prints the query result set
+-----+
| ID  |
+-----+
| 132 |
| 435 |
| 296 |
| 327 |
| 328 |
| 399 |
| 684 |
| 919 |
| 923 |
| 771 |
+-----+
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlResultSet;
import oracle.pg.rdbms.pgql.PgqlStatement;


/*
 * This example shows how to execute a PGQL PATH query.*/
public class PgqlExample4
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];


    Connection conn = null;
    PgqlStatement pgqlStmt = null;
    PgqlResultSet rs = null;

    try {
      //Get a jdbc connection
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);


      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);
      pgqlConn.setGraph(graph);

      // Create a PGQL Statement
      pgqlStmt = pgqlConn.createStatement();

     // Execute PGQL Query
      String query =
                 "PATH onehop AS ()-[IS transfers]->() "+
                 "SELECT v1.id FROM MATCH (v1)-/:onehop/->(v2) "+
                 "WHERE v2.id = 365";
      rs = pgqlStmt.executeQuery(query);

      // Print the results
      rs.print();
    }
    finally {
      // close the result set
      if (rs != null) {
         rs.close();
         }
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}
>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "<jdbcUrl>")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql_conn.set_graph("BANK_GRAPH")
>>> query = """
...                  PATH onehop AS ()-[IS transfers]->()
...                  SELECT v1.id FROM MATCH (v1)-/:onehop/->(v2)
...                  WHERE v2.id = 365
...         """
>>> pgql_statement.execute_query(query).print()
+-----+
| ID  |
+-----+
| 132 |
| 435 |
| 296 |
| 327 |
| 328 |
| 399 |
| 684 |
| 919 |
| 923 |
| 771 |
+-----+

Executing PGQL Queries to Modify PGQL Property Graphs

You can execute PGQL INSERT, UPDATE and DELETE queries against PGQL property graphs using the OPG4J Java shell, OPG4Py Python shell or through a Java or Python application.

It is important to note that unique IDs are not auto generated when inserting vertices or edges in a graph. Therefore, you must ensure that the key column values are either present in the graph properties or they are auto generated by the database (through SEQUENCE and TRIGGERS or implemented with auto increment functionality using IDENTITY column).

The following example inserts two new vertices and also adds an edge relationship between the two vertices.

opg4j> String pgql =
...>     "INSERT VERTEX v1 LABELS (Person) PROPERTIES (v1.name= 'ABC', v1.height=1.6, v1.birthdate = to_date('13/06/1963', 'DD/MM/YYYY')) "+
...>     "     , VERTEX v2 LABELS (Person) PROPERTIES (v2.name= 'XYZ', v2.height=1.75, v2.birthdate = to_date('19/06/1963', 'DD/MM/YYYY')) "+
...>     "     , EDGE e BETWEEN v1 AND v2 LABELS (friendof) PROPERTIES ( e.meeting_date = to_date('19/06/2021', 'DD/MM/YYYY')) "
pgql ==> "INSERT VERTEX v1 LABELS (Person) PROPERTIES (v1.name= 'ABC', v1.height=1.6, v1.birthdate = to_date('13/06/1963', 'DD/MM/YYYY'))      , VERTEX v2 LABELS (Person) PROPERTIES (v2.name= 'XYZ', v2.height=1.75, v2.birthdate = to_date('19/06/1963', 'DD/MM/YYYY'))      , EDGE e BETWEEN v1 AND v2 LABELS (friendof) PROPERTIES ( e.meeting_date = to_date('19/06/2021', 'DD/MM/YYYY')) "
opg4j> pgqlStmt.execute(pgql)
$14 ==> false
String pgql =
...>     "INSERT VERTEX v1 LABELS (Person) PROPERTIES (v1.name= 'ABC', v1.height=1.6, v1.birthdate = to_date('13/06/1963', 'DD/MM/YYYY')) "+
...>     "     , VERTEX v2 LABELS (Person) PROPERTIES (v2.name= 'XYZ', v2.height=1.75, v2.birthdate = to_date('19/06/1963', 'DD/MM/YYYY')) "+
...>     "     , EDGE e BETWEEN v1 AND v2 LABELS (friendof) PROPERTIES ( e.meeting_date = to_date('19/06/2021', 'DD/MM/YYYY')) ";
pgqlStmt.execute(pgql);
>>> pgql = """
...     INSERT VERTEX v1 LABELS (Person) PROPERTIES (v1.name= 'ABC', v1.height=1.6, v1.birthdate = to_date('13/06/1963', 'DD/MM/YYYY'))
...     , VERTEX v2 LABELS (Person) PROPERTIES (v2.name= 'XYZ', v2.height=1.75, v2.birthdate = to_date('19/06/1963', 'DD/MM/YYYY'))
...     , EDGE e BETWEEN v1 AND v2 LABELS (friendof) PROPERTIES ( e.meeting_date = to_date('19/06/2021', 'DD/MM/YYYY'))
... """
>>> pgql_statement.execute(pgql)
False

The following example executes an UPDATE query to modify the edge property that was inserted in the preceding example and subsequently verifies the update operation through a SELECT query.

opg4j> String pgql = "UPDATE e SET (e.meeting_date = to_date('12/02/2022', 'DD/MM/YYYY')) "+
...>     "FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) "+
...>     "WHERE v1.person_id = 27 AND v2.person_id = 28"
pgql ==> "UPDATE e SET (e.meeting_date = to_date('12/02/2022', 'DD/MM/YYYY')) FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) WHERE v1.person_id = 27 AND v2.person_id = 28"
opg4j> pgqlStmt.execute(pgql)
$40 ==> false
opg4j>pgqlStmt.executeQuery("SELECT e.meeting_date FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) WHERE v1.person_id = 27").print()
+-----------------------+
| MEETING_DATE          |
+-----------------------+
| 2022-02-12 00:00:00.0 |
+-----------------------+
String pgql ="UPDATE e SET (e.meeting_date = to_date('12/02/2022', 'DD/MM/YYYY')) "+
"FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) "+
"WHERE v1.person_id = 27 AND v2.person_id = 28";
pgqlStmt.execute(pgql);
>>> pgql = """
...     UPDATE e SET (e.meeting_date = to_date('12/02/2022', 'DD/MM/YYYY'))
...     FROM MATCH (v1:Person)-[e:friendof]->(v2:Person)
...     WHERE v1.person_id = 27 AND v2.person_id = 28
... """
>>> pgql_statement.execute(pgql)
False
>>> pgql_statement.execute_query("SELECT e.meeting_date FROM MATCH(v1:Person)-[e:friendof]->(v2:Person) WHERE v1.person_id = 27").print()
+-----------------------+
| MEETING_DATE          |
+-----------------------+
| 2022-02-12 00:00:00.0 |
+-----------------------+

A DELETE query allows deleting of vertices and edges in a graph. The following example executes a DELETE query to delete an edge in the graph.

opg4j> pgqlStmt.execute("DELETE e FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) WHERE v.person_id=27")
$14 ==> false
pgqlStmt.execute("DELETE e FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) WHERE v.person_id=27");
>>> pgql_statement.execute("DELETE e FROM MATCH (v1:Person)-[e:friendof]->(v2:Person) WHERE v1.person_id=27")
False

Dropping a PGQL Property Graph

You can use the PGQL DROP PROPERTY GRAPH statement to drop a PGQL property graph. Note that all the metadata tables for the PGQL property graph are dropped.

Example: Dropping a PGQL Property Graph

opg4j> var jdbcUrl="jdbc:oracle:thin:@<host_name>:<port>/<db_service>"
opg4j> var conn = DriverManager.getConnection(jdbcUrl,"<username>","<password>")
opg4j> var pgqlConn = PgqlConnection.getConnection(conn)
opg4j> var pgqlStmt = pgqlConn.createStatement() //create a PGQL Statement
opg4j> pgqlStmt.execute("DROP PROPERTY GRAPH <graph>")
$9 ==> false
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import oracle.pg.rdbms.pgql.PgqlConnection;
import oracle.pg.rdbms.pgql.PgqlStatement;
/**
 * This example shows how to drop a PGQL property graph.
 */
public class DropPgqlGraph
{

  public static void main(String[] args) throws Exception
  {
    int idx=0;
    String jdbcUrl            = args[idx++];
    String username           = args[idx++];
    String password           = args[idx++];
    String graph              = args[idx++];

    Connection conn = null;
    PgqlStatement pgqlStmt = null;

    try {
      //Get a jdbc connection
      conn = DriverManager.getConnection(jdbcUrl, username, password);
      conn.setAutoCommit(false);

      // Get a PGQL connection
      PgqlConnection pgqlConn = PgqlConnection.getConnection(conn);

	  // Create PGQL Statement
      pgqlStmt = pgqlConn.createStatement();

      String query = "DROP PROPERTY GRAPH " +graph;
      pgqlStmt.execute(query);

    }
    finally {
      // close the statement
      if (pgqlStmt != null) {
         pgqlStmt.close();
         }
      // close the connection
      if (conn != null) {
         conn.close();
         }
      }
  }
}
>>> pgql_conn = opg4py.pgql.get_connection("<username>","<password>", "jdbc:oracle:thin:@localhost:1521/orclpdb")
>>> pgql_statement = pgql_conn.create_statement()
>>> pgql = "DROP PROPERTY GRAPH <graph>"
>>> pgql_statement.execute(pgql)
False