Skip to main content

Graph Queries

Goose supports graph queries via the DuckPGQ community extension, which implements the SQL/PGQ syntax from the SQL:2023 standard.

Graph queries allow you to find patterns and paths in connected data, such as social networks, financial transactions, or knowledge graphs, using a visual, intuitive syntax.

Warning DuckPGQ is a community extension and is still under active development. Some features may be incomplete. See the DuckPGQ website for the latest status.

Installing DuckPGQ​

INSTALL duckpgq FROM community;
LOAD duckpgq;

Creating a Property Graph​

A property graph consists of vertices (nodes) and edges (relationships). You create one as a layer on top of existing tables:

CREATE TABLE Person (id BIGINT, name VARCHAR);
CREATE TABLE Knows (person1_id BIGINT, person2_id BIGINT, since DATE);

INSERT INTO Person VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO Knows VALUES (1, 2, '2020-01-01'), (2, 3, '2021-06-15');

CREATE PROPERTY GRAPH social_network
VERTEX TABLES (
    Person
)
EDGE TABLES (
    Knows
        SOURCE KEY (person1_id) REFERENCES Person (id)
        DESTINATION KEY (person2_id) REFERENCES Person (id)
);

Pattern Matching​

Use the GRAPH_TABLE function with MATCH to find patterns. The syntax uses () for nodes and [] for edges:

FROM GRAPH_TABLE (social_network
    MATCH (a:Person)-[k:Knows]->(b:Person)
    COLUMNS (a.name AS person1, b.name AS person2, k.since)
);
person1person2since
AliceBob2020-01-01
BobCharlie2021-06-15

Path Finding​

Find paths of variable length using quantifiers like {1,5} (1 to 5 hops) or + (one or more):

FROM GRAPH_TABLE (social_network
    MATCH p = ANY SHORTEST (a:Person)-[k:Knows]->{1,3}(b:Person)
    WHERE a.name = 'Alice' AND b.name = 'Charlie'
    COLUMNS (a.name AS start_person, b.name AS end_person, path_length(p) AS hops)
);
start_personend_personhops
AliceCharlie2

Graph Algorithms​

Warning Graph algorithm functions may currently fail due to a known issue and return the csr_cte does not exist error.

DuckPGQ includes built-in graph algorithms:

FunctionDescription
pagerank(graph, vertex_label, edge_label)Computes PageRank centrality scores
local_clustering_coefficient(graph, vertex_label, edge_label)Measures how connected a node's neighbors are
weakly_connected_component(graph, vertex_label, edge_label)Identifies connected components

Example:

FROM pagerank(social_network, Person, Knows);

Use Case: Financial Fraud Detection​

Graph queries excel at finding suspicious patterns in financial data.

Cleanup​

To remove a property graph:

DROP PROPERTY GRAPH social_network;

Further Reading​