Use Select AI with Property Graphs

10.7 Use Select AI with Property Graphs

Provides examples of how to use Select AI with property graphs.

Topics:

Example: Select AI for Property Graphs
This example shows how you can use the DBMS_CLOUD_AI.GENERATE procedure and a natural language prompt to generate PGQ graph queries to query graph data.
Example: Sample Prompts for Property Graphs
These examples show how to create sample data and display the generated SQL using the showsql action for a given prompt.

Parent topic: Examples Using Select AI

10.7.1 Example: Select AI for Property Graphs

This example shows how you can use the DBMS_CLOUD_AI.GENERATE procedure and a natural language prompt to generate PGQ graph queries to query graph data.

Before You Begin

Review Perform Prerequisites for Select AI.

Example: Create Property Graph Tables

The following example creates sample tables and a property graph.

-- Create tables
CREATE TABLE Customers (
    ID NUMBER,
    NAME VARCHAR2(10)
);
INSERT INTO Customers VALUES(1, 'Kate');
INSERT INTO Customers VALUES(2, 'Mark');
COMMIT;

CREATE TABLE Products (
    ID NUMBER,
    NAME VARCHAR2(10)
);

INSERT INTO Products VALUES(1, 'Dress');
COMMIT;

CREATE TABLE Buys (
   ID NUMBER,
   CUST NUMBER,
   PROD NUMBER
);
INSERT INTO Buys VALUES(1,1,1);
COMMIT;

-- Create property graph
CREATE PROPERTY GRAPH G
VERTEX TABLES(
  PRODUCTS KEY(ID),
  CUSTOMERS KEY(ID)
)
EDGE TABLES(
  BUYS KEY(ID)
  SOURCE KEY(CUST) REFERENCES CUSTOMERS(ID) 
  DESTINATION KEY(PROD) REFERENCES PRODUCTS(ID)
  NO PROPERTIES 
);

Example: Create an AI Profile with Single Property Graph Object

The following example shows creating an AI profile and supplying property graph object in the object_list parameter.

--oci provider, default model
SQL> BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
      profile_name =>'OPGAI',
      attributes   =>'{"provider": "oci",
        "credential_name": "OCI_CRED",
        "object_list": [{"owner": "ADB_USER", "name": "G"}],
        "oci_compartment_id" : "ocid1.tenancy.oc1..aaaa..."
       }');
END;
/
   
PL/SQL procedure successfully completed.

-- openai provider, gpt-4o model
SQL> BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
      profile_name =>'OPENAI',
      attributes   =>'{"provider": "openai",
        "model": "gpt-4o",
        "credential_name": "OPENAI_CRED",
        "object_list": [{"owner": "ADB_USER", "name": "G"}]
       }');
END;
/

PL/SQL procedure successfully completed.

Example: Query Using DBMS_CLOUD_AI.GENERATE Procedure

The following examples show how to run a natural language query using the DBMS_CLOUD_AI.GENERATE procedure with the AI profile named OPGAI, which includes a defined property graph. The examples show the showsql and narrate actions.

-- showsql action
SQL> SELECT DBMS_CLOUD_AI.GENERATE(prompt       => 'how many customers are there',
                                   profile_name => 'OPGAI',
                                   action       => 'showsql')
FROM dual;
 
DBMS_CLOUD_AI.GENERATE(PROMPT=>'HOWMANYCUSTOMERS',PROFILE_NAME=>'OPGAI',ACTION=>
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM GRAPH_TABLE ( G
  MATCH (c IS CUSTOMERS)
  COLUMNS (1 AS dummy_value)
)


1 row selected.

-- narrate action
SELECT DBMS_CLOUD_AI.GENERATE(prompt       => 'how many products are there',
                              profile_name => 'OPGAI',
                              action       => 'narrate')
FROM dual;
 SQL>   
DBMS_CLOUD_AI.GENERATE(PROMPT=>'HOWMANYPRODUCTS',PROFILE_NAME=>'OPGAI',ACTION=>'
--------------------------------------------------------------------------------
There is 1 product.

1 row selected.

Example: Query with a Prompt in SQL Command Line

The following example shows how to run a natural language query in SQL command line using the AI profile named OPENAI, which includes a defined property graph. This example uses select ai <prompt>. The default action is runsql.

First, set the active AI profile, and then issue a SELECT AI statement. The runsql action is used by default.

SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(profile_name => 'OPENAI');

PL/SQL procedure successfully completed.

SQL> select ai who bought a dress;

CUSTOMER_N
----------
Kate

1 row selected.

Example: Running Different Select AI Actions on a Property Graph

This example shows how the LLM defined in your AI profile interprets the same natural language query: how many customers are there, using different actions. Each action shows how Select AI translates natural language queries into graph queries with the GRAPH_TABLE operator.

--runsql action

SQL> SELECT AI RUNSQL how many customers are there;

CUSTOMER_COUNT
--------------
	     2

1 row selected.

SQL> SELECT AI how many customers are there;

CUSTOMER_COUNT
--------------
	     2

1 row selected.


--showsql action

SQL> SELECT AI SHOWSQL how many customers are there;

RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM GRAPH_TABLE(G
  MATCH (c IS CUSTOMERS)
  COLUMNS (1 AS dummy_value))


1 row selected.


--explainsql action

SQL> SELECT AI EXPLAINSQL how many customers are there;

RESPONSE
--------------------------------------------------------------------------------
```sql
SELECT COUNT(*) AS customer_count
FROM GRAPH_TABLE("G"
MATCH (v IS "CUSTOMERS")
COLUMNS(1 as dummy_value))
```

**Explanation**
To find the number of customers, we use the GRAPH_TABLE operator to access the g
raph data. In the MATCH clause, we specify the pattern to match vertices with th
e label "CUSTOMERS". Since we don't need any specific properties, we use a dummy
 value (1) in the COLUMNS clause. Finally, we use the COUNT(*) function outside
of the GRAPH_TABLE operator to count the number of matched vertices, which repre
sents the total number of customers.


1 row selected.

--narrate action

SQL> SELECT AI NARRATE how many customers are there;

RESPONSE
--------------------------------------------------------------------------------
There are 2 customers.

1 row selected.

--showprompt
SQL> SELECT AI SHOWPROMPT how many customers are there;

SQL> SELECT AI SHOWPROMPT how many customers are there;
--shows the truncated response for showprompt action
[
  {
    "role" : "system",
    "content" : "# Role and Objective\nYou are an Oracle SQL/PGQ expert.\nSQL/PGQ …"
.
.
.
  }
]

Example: Using Conversation Context to Query a Property Graph

This example shows how Select AI retains conversation context and queries the property graph during:

Session-based short-term conversations: when the conversation parameter is set to true in your AI profile.
Customizable long-term conversations: when you use conversation APIs.

See Example: Enable Conversations in Select AI for more details.

-- Create tables
SQL> CREATE TABLE Customers (
    	 ID NUMBER,
    	 NAME VARCHAR2(10),
    	 AGE NUMBER
    );

Table created.

SQL> INSERT INTO Customers VALUES(1, 'Kate', 25);

1 row created.

SQL> INSERT INTO Customers VALUES(2, 'Mark', 30);

1 row created.

SQL> INSERT INTO Customers VALUES(3, 'Alex', 25);

1 row created.

SQL> COMMIT;

Commit complete.

 
SQL> CREATE TABLE Products (
  2  	 ID NUMBER,
  3  	 NAME VARCHAR2(10)
  4  );

Table created.

 
SQL> INSERT INTO Products VALUES(1, 'Dress');

1 row created.

SQL> INSERT INTO Products VALUES(2, 'Socks');

1 row created.

SQL> INSERT INTO Products VALUES(3, 'Shirt');

1 row created.

SQL> INSERT INTO Products VALUES(4, 'Pants');

1 row created.

SQL> COMMIT;

Commit complete.


SQL> CREATE TABLE Buys (
    	ID NUMBER,
    	CUST NUMBER,
   	    PROD NUMBER,
    	PRICE NUMBER
    );

Table created.

SQL> INSERT INTO Buys VALUES(1,1,1,50);

1 row created.

SQL> INSERT INTO Buys VALUES(2,1,2,20);

1 row created.

SQL> INSERT INTO Buys VALUES(3,2,3,40);

1 row created.

SQL> INSERT INTO Buys VALUES(4,3,4,50);

1 row created.

SQL> COMMIT;

Commit complete.

 
-- Create property graph
SQL> CREATE PROPERTY GRAPH G
    VERTEX TABLES(
      PRODUCTS KEY(ID),
      CUSTOMERS KEY(ID)
    )
    EDGE TABLES(
      BUYS KEY(ID)
      SOURCE KEY(CUST) REFERENCES CUSTOMERS(ID)
      DESTINATION KEY(PROD) REFERENCES PRODUCTS(ID)
      PROPERTIES(PRICE)
   );

Property graph created.

-- Create profile with conversation set to TRUE
SQL> BEGIN
      DBMS_CLOUD_AI.CREATE_PROFILE(
    	   profile_name =>'OPGAI',
    	   attributes   =>'{"provider": "openai",
    	     "model": "gpt-4o",
    	     "credential_name": "OPENAI_CRED",
    	     "object_list": [{"owner": "ADB_USER", "name": "G"}],
    	     "conversation": "TRUE"}');
    END;
   /

PL/SQL procedure successfully completed.

 
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPGAI');

PL/SQL procedure successfully completed.

 
SQL> select ai what are the total number of customers;

TOTAL_CUSTOMERS
---------------
	      3

1 row selected.

SQL> select ai has any of them bought a shirt;

CUSTOMERS_BOUGHT_SHIRT
----------------------
		     1

1 row selected.

-- LONG TERM CONVERSATION --
BEGIN
      DBMS_CLOUD_AI.DROP_PROFILE(
          profile_name =>'OPGAI');
    END;
    /

PL/SQL procedure successfully completed.

BEGIN
      DBMS_CLOUD_AI.CREATE_PROFILE(
          profile_name =>'OPGAI',
          attributes   =>'{"provider": "openai",
            "model": "gpt-4o",
            "credential_name": "OPENAI_CRED",
        "object_list": [{"owner": "ADB_USER", "name": "G"}]}');
    END;
    /

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPGAI');

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION;

CREATE_CONVERSATION
--------------------------------------------------------------------------------
4309AAED-0EE7-3C23-E063-77634664063F

1 row selected.

SQL> EXEC DBMS_CLOUD_AI.SET_CONVERSATION_ID('4309AAED-0EE7-3C23-E063-77634664063F');

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_CLOUD_AI.GET_CONVERSATION_ID;

GET_CONVERSATION_ID
--------------------------------------------------------------------------------
4309AAED-0EE7-3C23-E063-77634664063F

1 row selected.

SQL> SELECT AI Who is the oldest customer;

CUSTOMER_N CUSTOMER_AGE
---------- ------------
Mark		     30

1 row selected.

SQL> SELECT AI Show his age only;

CUSTOMER_AGE
------------
	  30

1 row selected.

Example: Specify Multiple Graphs in your AI Profile

This example shows how to define multiple property graphs in your AI profile, including a sample query and its output.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
      profile_name =>'OPENAI',
      attributes   =>'{"provider": "openai",
        "model": "gpt-4o",
        "credential_name": "OPENAI_CRED",
        "object_list": [{"owner": "ADB_USER", "name": "LDBC_GRAPH"},
                        {"owner": "ADB_USER", "name": "G"}]
       }');                                                                  
END;                                                                         
/

SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(profile_name => 'OPENAI');

PL/SQL procedure successfully completed.

SQL> select ai who bought a dress;

CUSTOMER_N
----------
Kate

1 row selected.

Parent topic: Use Select AI with Property Graphs

10.7.2 Example: Sample Prompts for Property Graphs

These examples show how to create sample data and display the generated SQL using the showsql action for a given prompt.

Before You Begin

Review Perform Prerequisites for Select AI.

Example: Create Property Graph Table

The following example creates sample tables and a property graph.

CREATE TABLE Person
(
    id         NUMBER PRIMARY KEY,
    firstName  VARCHAR2(20 CHAR),
    lastName   VARCHAR2(20 CHAR),
    age        NUMBER,
    jsonProp   VARCHAR2(40 CHAR)
);

CREATE TABLE Post
(
    id         NUMBER PRIMARY KEY,
    content    VARCHAR2(20 CHAR)
);

CREATE TABLE personLikesPost
(
    idPerson NUMBER REFERENCES Person (id),
    idPost   NUMBER REFERENCES Post (id)
);

CREATE TABLE personKnowsPerson
(
    idPerson1 NUMBER REFERENCES Person (id),
    idPerson2 NUMBER REFERENCES Person (id)
);

CREATE PROPERTY GRAPH person_graph
  VERTEX TABLES (
    Person KEY (id) LABEL Person
      PROPERTIES (firstName, lastName, age, jsonProp),
    Post KEY (id) LABEL Post
      PROPERTIES(content)
  )
  EDGE TABLES (
    personLikesPost
      KEY(idPerson, idPost)
      SOURCE KEY (idPerson) REFERENCES Person (id)
      DESTINATION KEY (idPost) REFERENCES POST (id)
      LABEL Likes NO PROPERTIES,
    personKnowsPerson
      KEY(idPerson1, idPerson2)
      SOURCE KEY (idPerson1) REFERENCES Person (id)
      DESTINATION KEY (idPerson2) REFERENCES Person (id)
      LABEL Knows NO PROPERTIES
  );

insert into Person values (1, 'John', 'Doe',23, '{"key1":"value1","key2":"value2"}');
insert into Person values (2, 'Scott', 'Tiger', 25, '{"key1":"value3","key2":"value4"}');
insert into Person values (3, 'Max', 'Power', 27, '{"key1":"value5","key2":"value6"}');
insert into Person values (4, 'Jane', 'Doe', 22, '{"key1":"value7","key2":"value8"}');
insert into Person (id, Firstname, age) values (5, 'Hans', 23);
insert into Person (id, Firstname, age) values (6, 'Franz', 24);
 
INSERT INTO Post VALUES (10, 'Lorem ipsum...');
INSERT INTO Post VALUES (11, 'Nulla facilisi...');
INSERT INTO Post VALUES (12, 'Vestibulum eget ..');
INSERT INTO Post VALUES (13, 'Sed fermentum...');
INSERT INTO Post VALUES (14, 'Fusce at ...');
INSERT INTO Post VALUES (15, 'Pellentesque sit ...');
INSERT INTO Post VALUES (16, 'Integer...');
INSERT INTO Post VALUES (17, 'Curabitur luctus ...');
INSERT INTO Post VALUES (18, 'Nam in ...');
INSERT INTO Post VALUES (19, 'Etiam ac ...');
 
insert into personKnowsPerson values (1, 2);
insert into personKnowsPerson values (2, 3);
insert into personKnowsPerson values (3, 4);
insert into personKnowsPerson values (4, 5);
insert into personKnowsPerson values (5, 6);
insert into personKnowsPerson values (6, 2);
insert into personKnowsPerson values (5, 3);
 
INSERT INTO personLikesPost VALUES (1, 10);
INSERT INTO personLikesPost VALUES (1, 11);
INSERT INTO personLikesPost VALUES (1, 12);
INSERT INTO personLikesPost VALUES (2, 10);
INSERT INTO personLikesPost VALUES (2, 13);
INSERT INTO personLikesPost VALUES (2, 14);
INSERT INTO personLikesPost VALUES (3, 11);
INSERT INTO personLikesPost VALUES (3, 15);
INSERT INTO personLikesPost VALUES (3, 16);
INSERT INTO personLikesPost VALUES (4, 12);
INSERT INTO personLikesPost VALUES (4, 17);
INSERT INTO personLikesPost VALUES (4, 18);
INSERT INTO personLikesPost VALUES (5, 13);
INSERT INTO personLikesPost VALUES (5, 14);
INSERT INTO personLikesPost VALUES (5, 19);
INSERT INTO personLikesPost VALUES (6, 15);
INSERT INTO personLikesPost VALUES (6, 16);
INSERT INTO personLikesPost VALUES (6, 17);
INSERT INTO personLikesPost VALUES (1, 18);
INSERT INTO personLikesPost VALUES (2, 19);

commit;

Example: Matching of Vertices Without Labels

Prompt: Find all the people IDs

SELECT person_id
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (v IS "PERSON")
    COLUMNS (VERTEX_ID(v) AS person_id))

Example: Matching of Edges Without Labels

Prompt: Find all the edge IDs

SELECT edge_id
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (v1) -[e]-> (v2)
    COLUMNS (EDGE_ID(e) AS edge_id))

Example: Matching of Edges With Labels

Prompt: Find all the knows relationship IDs

SELECT knows_id
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p1 IS "PERSON") -[e IS "KNOWS"]-> (p2 IS "PERSON")
    COLUMNS (EDGE_ID(e) AS knows_id))

Example: Matching of Path Patterns Including One or Multiple Hops

Prompt: List all people who know someone who liked the post 'Sed fermentum...'

SELECT person_id, person_firstname, person_lastname
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p1 IS "PERSON") -[e1 IS "KNOWS"]-> (p2 IS "PERSON") -[e2 IS "LIKES"]-> (post IS "POST")
    WHERE UPPER(post."CONTENT") = UPPER('Sed fermentum...')
    COLUMNS (VERTEX_ID(p1) AS person_id, p1."FIRSTNAME" AS person_firstname, p1."LASTNAME" AS person_lastname))

Example: Accessing Vertex and Edge Properties

Prompt: Find first name and last name of all people

SELECT first_name, last_name
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p IS "PERSON")
    COLUMNS (p."FIRSTNAME" AS first_name, p."LASTNAME" AS last_name))

Example: Filtering data

Prompt: Find post contents liked by John Doe

SELECT post_content
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p IS "PERSON") -[e IS "LIKES"]-> (post IS "POST")
    WHERE UPPER(p."FIRSTNAME") = UPPER('John') AND UPPER(p."LASTNAME") = UPPER('Doe')
    COLUMNS (post."CONTENT" AS post_content))

Example: Functions and Expressions

Prompt: Show all people (full name) and display their key1 value from jsonProp property

SELECT person_fullname, json_key1
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p IS "PERSON")
    COLUMNS (
	(p."FIRSTNAME" || ' ' || p."LASTNAME") AS person_fullname,
	JSON_QUERY(p."JSONPROP", '$.key1') AS json_key1
    )
)

Example: Sorting Data

Prompt: Find friends of Scott Tiger ordered by their last name

SELECT friend_firstname, friend_lastname
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p IS "PERSON") -[e IS "KNOWS"]-> (f IS "PERSON")
    WHERE UPPER(p."FIRSTNAME") = UPPER('Scott') AND UPPER(p."LASTNAME") = UPPER('Tiger')
    COLUMNS (f."FIRSTNAME" AS friend_firstname, f."LASTNAME" AS friend_lastname)
)
ORDER BY friend_lastname

Example: Row Limiting

Prompt: Find all people ordered by first name. Skip one result and return 2 results only

SELECT person_firstname
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p IS "PERSON")
    COLUMNS (p."FIRSTNAME" AS person_firstname))
ORDER BY person_firstname
OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY

Unsupported Queries

For the following queries certain LLMs generate valid NL2SQL, but the resulting SQL uses features that are not yet supported in Oracle AI Database 26ai.

Example: Queries Requiring not to Match a Certain Pattern

Prompt: Find people that do not know Scott.

EXISTS subquery is not supported.

SELECT person_id, first_name, last_name
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p1 IS "PERSON")
    WHERE NOT EXISTS (
	SELECT 1
	FROM GRAPH_TABLE("ADB_USER"."PERSONGRAPH"
	    MATCH (p2 IS "PERSON") -[e IS "PERSONKNOWSPERSON"]-> (p3 IS "PERSON"
)
	    WHERE p2."ID" = p1."ID" AND UPPER(p3."FIRSTNAME") = UPPER('Scott')
	    COLUMNS (1 AS dummy_value))
    )
    COLUMNS (p1."ID" AS person_id, p1."FIRSTNAME" AS first_name, p1."LASTNAME" A
S last_name))

Example: Queries Requiring to Optionally Match a Certain Pattern

Prompt: Show all people and how many posts they have liked (show people even if they have not liked a post).

OPTIONAL match is not supported.

SELECT person_id, person_firstname, person_lastname, liked_post_ids
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
MATCH (p is "PERSON") OPTIONAL MATCH (p) -[l is "PERSONLIKESPOST"]-> (post is "POST")
COLUMNS(
    VERTEX_ID(p) as person_id,
    p."FIRSTNAME" as person_firstname,
    p."LASTNAME" as person_lastname,
    JSON_ARRAYAGG(VERTEX_ID(post)) as liked_post_ids
))

Example: Unbounded Recursive Path Patterns

Prompt: Find all people that Scott can reach.

Queries that use unbounded quantifiers are not supported.

SELECT person_id, person_firstname, person_lastname
FROM GRAPH_TABLE("ADB_USER"."PERSONGRAPH"
    MATCH (src IS "PERSON") -[e IS "PERSONKNOWSPERSON"]->* (dst IS "PERSON")
    WHERE src."FIRSTNAME" = 'Scott'
    COLUMNS (
	VERTEX_ID(dst) AS person_id,
	dst."FIRSTNAME" AS person_firstname,
	dst."LASTNAME" AS person_lastname
    )
)

Intermittent Queries

LLMs have been shown to struggle when translating queries that require more than one GRAPH_TABLE operator. The following are such examples:

Prompt: Show people who have liked all the same posts as Hans

SELECT person_id, person_name
FROM GRAPH_TABLE("PERSON_GRAPH"
  MATCH (hans is "PERSON") -[likes_hans is "PERSONLIKESPOST"]-> (post is "POST"),
	(other_person is "PERSON") -[likes_other is "PERSONLIKESPOST"]-> (post)
  WHERE hans."FIRSTNAME" = 'Hans'
  COLUMNS (VERTEX_ID(other_person) as person_id, other_person."FIRSTNAME" AS person_name)
)
WHERE NOT EXISTS (
  SELECT 1
  FROM GRAPH_TABLE("PERSONGRAPH"
    MATCH (hans is "PERSON") -[likes_hans is "PERSONLIKESPOST"]-> (post is "POST")
    WHERE hans."FIRSTNAME" = 'Hans'
    COLUMNS (VERTEX_ID(post) as post_id)
  ) hans_posts
  LEFT JOIN GRAPH_TABLE("PERSONGRAPH"
    MATCH (other_person is "PERSON") -[likes_other is "PERSONLIKESPOST"]-> (post
 is "POST")
    COLUMNS (VERTEX_ID(post) as post_id)
  ) other_posts
  ON hans_posts.post_id = other_posts.post_id
  WHERE other_posts.post_id IS NULL
)

Example: Matching of Recursive Path Patterns with Defined Bounds.

Prompt: Find all names of the people that can be reached in a 1 to 3 edge path

SELECT person_name
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (start_person IS "PERSON") -[e IS "KNOWS"]->{1,3} (end_person IS "PERSON")
    COLUMNS (end_person."FIRSTNAME" AS person_name))

Example: Filtering Data for Nodes Along a Recursive Path

Prompt: Find all names of the people that can be reached in a 1 to 3 edge path where each person is younger than the next one

SELECT person_name
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (start_person IS "PERSON") ((v1 IS "PERSON") -[e IS "KNOWS"]-> (v2 IS"PERSON") WHERE v1."AGE" < v2."AGE"){1,3} (end_person IS "PERSON")
    COLUMNS (end_person."FIRSTNAME" AS person_name))

Example: Grouping and Aggregation

LLMs often struggle with translating queries that require grouping and aggregation. A common mistake is placing aggregations in the COLUMNS clause instead of the SELECT clause.

Prompt: Find the average number of posts liked by all the users

SELECT AVG(COUNT(post)) AS average_liked_count
FROM GRAPH_TABLE("PERSON_GRAPH"
MATCH (p IS "PERSON") -[e IS "PERSONLIKESPOST"]-> (post IS "POST")
COLUMNS (VERTEX_ID(p) AS person, VERTEX_ID(post) AS post))
GROUP BY person;

Parent topic: Use Select AI with Property Graphs