SQL Graph Queries

You can query a SQL property graph using the GRAPH_TABLE operator to express graph pattern matching queries.

Graph pattern matching allows you to define a set of path patterns and match it against a graph to obtain a set of solutions. You must provide the graph to be queried as an input to the GRAPH_TABLE operator along with the MATCH clause containing the graph patterns to be searched as shown:

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
  (a IS person) -[e IS friends]-> (b IS person WHERE b.name = 'Mary')
  WHERE a.name='John'
  COLUMNS (a.name AS person_a, b.name AS person_b)
);

A basic SQL graph query is made up of the following components:

• FROM clause: It includes the GRAPH_TABLE operator which takes the input graph name as the first parameter.

• MATCH clause: It expresses the graph element patterns (vertex or edge pattern) to be searched on the SQL property graph. It can optionally include an element pattern WHERE clause as seen in the preceding example ((b IS person WHERE b.name = 'Mary')) query. This in-line WHERE clause can access any matched variable.

• WHERE clause: This is an optional out-of-line WHERE clause. Similar to the element pattern WHERE clause, it has access to all the graph pattern variables and expresses a predicate that applies to the entire pattern in the MATCH clause.

• ONE ROW PER clause: An optional clause to limit the number of output rows. See Using ONE ROW PER Clause in a SQL Graph Query for more information.

• COLUMNS clause: This contains the query output columns.

Note: It is recommended that you create indexes on the underlying database tables for better SQL property graph query performance.

See Also: GRAPH_TABLE Operator in Oracle AI Database SQL Language Reference

The following sections explain SQL graph queries in detail:

• About Graph Pattern
  The GRAPH_TABLE operator in a SQL graph query contains a graph pattern.

• Variable Length Path Patterns
  Variable length graph patterns provide advanced querying support for SQL property graphs.

• Complex Path Patterns
  You can query a SQL property graph using complex path patterns.

• Vertex and Edge Identifiers
  You can uniquely identify each vertex and edge in a SQL property graph with the VERTEX_ID and EDGE_ID functions, respectively, in a SQL graph query.

• Using ONE ROW PER Clause in a SQL Graph Query
  You can use the ONE ROW PER optional clause in a SQL graph query to determine the number of rows to be returned by the query.

• Using Aggregate Functions in SQL Graph Queries
  You can use aggregate functions in a SQL graph query to obtain an aggregated output.

• Selecting All Properties in the COLUMNS Clause
  You can select all the vertex or edge properties based on the element type (vertex or edge) and any label expression specified for the element in the COLUMNS clause of a SQL graph query.

• Using the SOURCE and DESTINATION Predicates
  You can determine if a vertex is a source or destination using the IS [NOT] SOURCE OF or IS [NOT] DESTINATION OF predicate in a value expression inside a WHERE or COLUMNS clause.

• Running SQL Graph Queries at a Specific SCN
  You can run a SQL graph query at a given System Change Number (SCN) or timestamp value.

• Privileges to Query a SQL Property Graph
  You must have the READ or SELECT object privilege to query a SQL property graph.

• Examples for SQL Graph Queries
  This section contains a few examples for querying a SQL property graph with fixed-length and variable-length graph pattern matching queries.

• Supported Features and Limitations When Querying a SQL Property Graph
  This section provides the list of supported and unsupported features when querying a SQL Property Graph.

• Tuning SQL Property Graph Queries
  You can tune a SQL graph query using the EXPLAIN PLAN statement.

• Type Compatibility Rules for Determining Property Types
  When using shared property names that are union compatible, the property type is determined by certain type compatibility rules.

• Viewing and Querying SQL Property Graphs Using SQL Developer
  Using SQL Developer 23.1, you can view all the SQL property graphs existing in your database schema by expanding SQL Property Graphs under the Property Graph node in the Connections navigator.