The following PGQL features are supported in PGQL property graphs:

• Recursive queries are supported for the following variable-length path finding goals:
  • Reachability
  • ANY
  • ANY SHORTEST
  • TOP k SHORTEST
• Recursive queries are supported for the following horizontal aggregations:
  • LISTAGG

    SELECT LISTAGG(src.first_name || ' ' || src.last_name, ',')
    FROM MATCH TOP 2 SHORTEST ( (n:Person) ((src)-[e:knows]->)* (m:Person) )
    WHERE n.id = 1234

  • SUM

    SELECT SUM(e.weight + 3)
    FROM MATCH TOP 2 SHORTEST ( (n:Person) -[e:knows]->* (m:Person) )
    WHERE n.id = 1234

  • COUNT

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

  • AVG

    SELECT AVG(dst.age)
    FROM MATCH TOP 2 SHORTEST ( (n:Person) (-[e:knows]->(dst))* (m:Person) )
    WHERE n.id = 1234

  • MIN (Only for property value or CAST expressions)

    SELECT MIN(CAST(dst.age + 5 AS INTEGER))
    FROM MATCH TOP 2 SHORTEST ( (n:Person) (-[e:knows]->(dst))* (m:Person) )
    WHERE n.id = 1234

  • MAX (Only for property value or CAST expressions)

    SELECT MAX(dst.birthday)
    FROM MATCH TOP 2 SHORTEST ( (n:Person) (-[e:knows]->(dst))* (m:Person) )
    WHERE n.id = 1234

• The following quantifiers are supported in recursive queries:

  Table 13-3 Supported Quantifiers in PGQL SELECT Queries

  Syntax	Description
  *	zero or more
  +	one or more
  ?	zero or one
  {n}	exactly n
  {n,}	n or more
  {n,m}	between n and m (inclusive)
  {,m}	between zero and m (inclusive)

• Data type casting with precision and scale is supported:

  SELECT CAST(v.id AS VARCHAR2(10)) || '→' || CAST(w.id AS VARCHAR2(10)) AS friendOf
  FROM MATCH (v) -[:friendOf]->(w)

  SELECT CAST(e.mval AS NUMBER(5,2)) AS mval
  FROM MATCH () -[e:knows]->()
  WHERE e.mval = '342.5'

• Both built-in Oracle Database functions and user defined functions (UDFs) are supported.

  For example:

  • Assuming a table has a JSON column with values such as, {"name":"John", "age": 43}:

    SELECT JSON_VALUE(p.attributes, '$.name') AS name
    FROM MATCH (p:Person)
    WHERE JSON_VALUE(p.attributes, '$.age') > 35

  • Assuming an Oracle Text index exists on a text column in a table:

    SELECT n.text
    FROM MATCH (n)
    WHERE CONTAINS(n.text, 'cat', 1) > 0

  • Assuming a UDF updated_id is registered with the graph server (PGX):

    SELECT my.updated_id(n.ID) FROM MATCH(n) LIMIT 10

• Selecting all properties of vertices or edges is supported through SELECT v.* clause, where v is the variable whose properties are selected. The following example retrieves all the edge properties of a graph:

  SELECT label(e), e.* FROM MATCH (n)-[e]->(m) ON bank_graph LIMIT 3

  On execution, the preceding query retrieves all the properties that are bound to the variable e as shown:

  +--------------------------------------------------------------+
  | label(e)  | AMOUNT | DESCRIPTION | FROM_ACCT_ID | TO_ACCT_ID |
  +--------------------------------------------------------------+
  | TRANSFERS | 1000   | transfer    | 178          | 921        |
  | TRANSFERS | 1000   | transfer    | 178          | 462        |
  | TRANSFERS | 1000   | transfer    | 179          | 688        |
  +--------------------------------------------------------------+
  

  A PREFIX can be specified to avoid duplicate column names in cases where you select all properties using multiple variables. For example:

  SELECT n.* PREFIX 'n_', e.* PREFIX 'e_', m.* PREFIX 'm_' 
  FROM MATCH (n:Accounts) -[e:transfers]-> (m:Accounts) 
  ON bank_graph LIMIT 3

  The query output is as follows:

  +--------------------------------------------------------------------------------------------+
  | n_ID | n_NAME  | e_AMOUNT | e_DESCRIPTION | e_FROM_ACCT_ID | e_TO_ACCT_ID | m_ID | m_NAME  |
  +--------------------------------------------------------------------------------------------+
  | 178  | Account | 1000     | transfer      | 178            | 921          | 921  | Account |
  | 178  | Account | 1000     | transfer      | 178            | 462          | 462  | Account |
  | 179  | Account | 1000     | transfer      | 179            | 688          | 688  | Account |
  +--------------------------------------------------------------------------------------------+
  

  Label expressions can be used such that only properties that belong to the specified vertex or edge labels are selected:

  SELECT LABEL(n), n.* FROM MATCH (n:Accounts) ON bank_graph LIMIT 3

  The preceding query output is as shown:

  +-----------------------+
  | LABEL(n) | ID | NAME  |
  +-----------------------+
  | ACCOUNTS | 1  | User1 |
  | ACCOUNTS | 2  | User2 |
  | ACCOUNTS | 3  | User3 |
  +-----------------------+
  

• Support for ALL path finding goal to return all the paths between a pair of vertices. However, to avoid endless cycling, only the following quantifiers are supported:
  • ?
  • {n}
  • {n.m}
  • {,m}

  For example, the following PGQL query finds all the transaction paths from account 284 to account 616 :

  SELECT LISTAGG(e.amount, ' + ') || ' = ', SUM(e.amount) AS total_amount
  FROM MATCH ALL (a:Accounts) -[e:Transfers]->{1,4}(b:Accounts)
  WHERE a.id = 284 AND b.id = 616
  ORDER BY total_amount

  On execution, the query produces the following result:

  +--------------------------------------------------+
  | LISTAGG(e.amount, ' + ') || ' = ' | TOTAL_AMOUNT |
  +--------------------------------------------------+
  | 1000 + 1000 + 1000 =              | 3000         |
  | 1000 + 1500 + 1000 =              | 3500         |
  | 1000 + 1000 + 1000 + 1000 =       | 4000         |
  +--------------------------------------------------+
  $16 ==> oracle.pg.rdbms.pgql.pgview.PgViewResultSet@4f38acf
  

• Scalar subqueries which return exactly one column and one row is supported.

  For example:

  SELECT p.name AS name , (
    SELECT SUM(t.amount)      
    FROM MATCH (a) <-[t:transaction]- (:Account)  
  ) AS sum_incoming , (
    SELECT SUM(t.amount)      
    FROM MATCH (a) -[t:transaction]-> (:Account)  
  ) AS sum_outgoing , (
    SELECT COUNT(DISTINCT p2)      
    FROM MATCH (a) -[t:transaction]- (:Account) -[:owner]-> (p2:Person)    
    WHERE p2 <> p  
  ) AS num_persons_transacted_with , (
    SELECT COUNT(DISTINCT c)      
    FROM MATCH (a) -[t:transaction]- (:Account) -[:owner]-> (c:Company)  
  ) AS num_companies_transacted_with   
  FROM MATCH (p:Person) <-[:owner]- (a:Account)
  ORDER BY sum_outgoing + sum_incoming DESC

• EXISTS and NOT EXISTS subqueries are supported. Such queries yield TRUE or FALSE depending on whether the query produces at least one results given the bindings of the outer query.

  For example:

  SELECT fof.name, COUNT(friend) AS num_common_friends 
  FROM MATCH (p:Person) -[:knows]-> (friend:Person) -[:knows]-> (fof:Person)
  WHERE NOT EXISTS (   
    SELECT * FROM MATCH (p) -[:knows]-> (fof) 
  )

• PGQL LATERAL subqueries are supported. For example:

  SELECT recipient, COUNT(*) AS num_large_transactions
  FROM LATERAL ( SELECT m.id AS recipient
                 FROM MATCH (n IS accounts) -[e IS transfers]-> (m IS accounts)
                 WHERE n.id = 772
                 ORDER BY e.amount DESC )
  GROUP BY recipient
  ORDER BY num_large_transactions DESC

• PGQL GRAPH_TABLE operator is supported. For example:

  SELECT * FROM GRAPH_TABLE ( bank_graph
    MATCH (a IS accounts) -[e IS transfers]-> (b IS accounts)
    COLUMNS ( a.id as from_ac, e.amount as amount, b.id as to_ac  )
  ) FETCH FIRST FIVE ROWS ONLY

• The source (IS [NOT] SOURCE OF) and destination (IS [NOT] DESTINATION OF) predicates to verify if a vertex is a source or destination of an edge are supported. This is useful when an edge is matched through an any directed edge pattern (-[e]-). Note that this PGQL feature is supported only in Oracle Database 23ai. For example:

  SELECT e.amount, CASE WHEN n IS SOURCE OF e THEN 'Outgoing transaction' ELSE 'Incoming transaction' END AS type
  FROM MATCH (n:Accounts) -[e:transfers]- (m:Accounts)
  WHERE n.id = 284
  ORDER BY type, e.amount

  The preceding query produces the following result:

  +-------------------------------+
  | AMOUNT | TYPE                 |
  +-------------------------------+
  | 1000   | Incoming transaction |
  | 1200   | Outgoing transaction |
  | 1300   | Outgoing transaction |
  +-------------------------------+

• JSON_ARRAYAGG function (see JSON_ARRAYAGG in Oracle Database SQL Language Reference) to aggregate values into a JSON array is supported.

  SELECT JSON_ARRAY_AGG(n.id) AS txn_from
  FROM MATCH (n:Accounts) -[e:transfers]- (m:Accounts)
  WHERE m.id = 616

  On execution, the query produces the following result:

  +-------------------------------------------+
  | TXN_FROM                                  |
  +-------------------------------------------+
  | [202,582,650,108,744,756,801,674,710,764] |
  +-------------------------------------------+

• Built-in graph validation function pg.validate() to check if vertex and edge keys are unique, and if the sources and destinations of edges exist.

  pgqlStmt.execute("CALL pg.validate('BANK_TXN_GRAPH')")
  $1 ==> false

  Exceptions are raised for invalid keys or edges having missing vertices as shown:

  pgqlStmt.execute("CALL pg.validate('COUNTRIES')")
  opg4j> pgqlStmt.execute("CALL pg.validate('COUNTRIES')")
  |  Exception oracle.pg.rdbms.pgql.PgqlToSqlException: Invalid vertex key 60 for edge NO in edge table CTY_REG with destination key column(s) "REGION_ID" referencing REGIONS ( "REGION_ID" )

• Unnesting of paths using the ONE ROW PER STEP clause is supported in the PGQL GRAPH_TABLE operator query.

  SELECT *
  FROM GRAPH_TABLE ( financial_transactions
         MATCH
           (a IS account) -[IS transaction]->+ (a) 
         KEEP SHORTEST 5 SIMPLE PATHS
         WHERE a.number = 10039
         ONE ROW PER STEP ( v1, e, v2 )  
         COLUMNS( MATCHNUM() AS matchnum,
                  ELEMENT_NUMBER(e) AS elemnum,
                  v1.number AS account1,
                  v2.number AS account2, e.amount))
  ORDER BY matchnum, elemnum

  As seen in the preceding example, the ONE ROW PER STEP clause declares an iterator vertex variable, an iterator edge variable, and another iterator vertex variable. The query produces one row per step (a step is a vertex-edge-vertex triple) as shown:

  +---------------------------------------------------+
  | matchnum | elemnum | account1 | account2 | amount |
  +---------------------------------------------------+
  | 0        | 2       | 10039    | 8021     | 1000.0 |
  | 0        | 4       | 8021     | 1001     | 1500.3 |
  | 0        | 6       | 1001     | 2090     | 9999.5 |
  | 0        | 8       | 2090     | 10039    | 9900.0 |
  | 1        | 2       | 10039    | 8021     | 1000.0 |
  | 1        | 4       | 8021     | 1001     | 3000.7 |
  | 1        | 6       | 1001     | 2090     | 9999.5 |
  | 1        | 8       | 2090     | 10039    | 9900.0 |
  +---------------------------------------------------+

  The preceding output shows two paths, each having 4 edges.

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

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_txns AS Transfers "
...> + "KEY (txn_id) "
...> + "SOURCE KEY (from_acct_id) REFERENCES Accounts (id) "
...> + "DESTINATION KEY (to_acct_id) REFERENCES Accounts (id) "
...> + "LABEL Transfers "
...> + "PROPERTIES (from_acct_id, to_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.jdbc.PgqlJdbcRdbmsDriver;
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
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      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_txns as Transfers " +
        "KEY (txn_id) " +
        "SOURCE KEY (from_acct_id) REFERENCES Accounts (id) " +
        "DESTINATION KEY (to_acct_id) REFERENCES Accounts (id) " +
        "LABEL \"Transfers\"" +
        "PROPERTIES (from_acct_id, to_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_txns as Transfers
...             KEY (txn_id)
...             SOURCE KEY (from_acct_id) REFERENCES Accounts(id)
...             DESTINATION KEY (to_acct_id) REFERENCES Accounts (id)
...             LABEL TRANSFERS
...             PROPERTIES (from_acct_id, to_acct_id, amount, description)
...         ) OPTIONS(PG_PGQL)
... """
>>> pgql_statement.execute(pgql)
False

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

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.jdbc.PgqlJdbcRdbmsDriver;
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
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      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)

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.jdbc.PgqlJdbcRdbmsDriver;
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
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      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 |
+---------------+

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.jdbc.PgqlJdbcRdbmsDriver;
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
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      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       |
+---------------+

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.jdbc.PgqlJdbcRdbmsDriver;
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
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      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 |
+-----+

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

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 |
+-----------------------+

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

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.

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.jdbc.PgqlJdbcRdbmsDriver;
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
      DriverManager.registerDriver(new PgqlJdbcRdbmsDriver());
      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