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
- LISTAGG
- 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
- Assuming a table has a JSON column with values such as,
- Selecting all properties of vertices or edges is
supported through
SELECT v.*
clause, wherev
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 account616
: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 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
andout_degree
functions.
- Use of bind variables in path expressions.
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