6.9.2.1 PGQL Features Supported in Property Graph Views

The following PGQL features are supported in property graph views:

• 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 6-7 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_view 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_view 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_view 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
  

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

• The following PGQL SELECT features are not supported:
  • Use of bind variables in path expressions.

    If you attempt to use a bind variable, it will result in an error as shown:

    opg4j> String s = "SELECT id(a) FROM MATCH ANY SHORTEST (a) -[e]->* (b) WHERE id(a) = ?";
    s ==> "SELECT id(a) FROM MATCH ANY SHORTEST (a) -[e]->* (b) WHERE id(a) = ?"
     
    opg4j> PgqlPreparedStatement ps = pgqlConn.prepareStatement(s);
    ps ==> oracle.pg.rdbms.pgql.PgqlExecution@7806db3f
     
    opg4j> ps.setString(1, "PERSON(3)");
     
    opg4j> ps.executeQuery();
    |  Exception java.lang.UnsupportedOperationException: Use of bind variables for path queries is not supported

  • Using subqueries.
  • in_degree and out_degree functions.

Note:

• See Supported PGQL Features and Limitations for a complete list of supported and unsupported PGQL features for PGQL on RDBMS (PG_VIEW option)
• See Performance Considerations for PGQL Queries for details on recommended practices to enhance query performance for recursive queries.