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

• 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