PGQL Features Supported in Property Graph Views

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

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) 
)
```

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

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.

Parent topic: Executing PGQL Queries Against Property Graph Views