Apache AGE Full text search of graph data

apache_age_graph_search.sql

CREATE EXTENSION IF NOT EXISTS age;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

LOAD 'age';
SET search_path = ag_catalog, "$user", public;

-- Apache AGE graph_search
CREATE OR REPLACE FUNCTION public.graph_search(graph agtype, label agtype, property agtype, search agtype) RETURNS SETOF agtype AS $$
DECLARE
    fts TEXT;
BEGIN
    -- generate dynamic search query
    fts := FORMAT($sql$
        SELECT id, rank, similarity
        FROM %s."%s",
            websearch_to_tsquery('english', '%s') as tsquery,
            to_tsvector('english', agtype_access_operator(properties, '"%s"'::agtype)::text) as document,
            ts_rank(document, tsquery) AS rank,
            similarity('%s', agtype_access_operator(properties, '"%s"'::agtype)::text) as similarity
        WHERE document @@ tsquery OR similarity > 0
        ORDER BY rank DESC NULLS LAST, similarity DESC NULLS LAST
    $sql$, graph::text, label::text, search::text, property::text, search::text, property::text);

    -- run the query above
    RETURN QUERY EXECUTE FORMAT($sql$
        WITH query AS (%s) 
        SELECT agtype_build_list(query) 
        FROM query
    $sql$, fts);
END
$$ LANGUAGE plpgsql VOLATILE;


-- sample optional indexes for better search performance

-- fts index
CREATE INDEX IF NOT EXISTS ts_idx ON movie_recommendation."Movie" USING GIN (
    to_tsvector('english', ag_catalog.agtype_access_operator(properties, '"name"'::ag_catalog.agtype)::text)
);    
-- similarity index
CREATE INDEX IF NOT EXISTS name_idx ON movie_recommendation."Movie" USING GIN (
    (ag_catalog.agtype_access_operator(properties, '"name"'::ag_catalog.agtype)::text) gin_trgm_ops
);

-- sample search of movie data
SELECT * FROM cypher('movie_recommendation', $$
    WITH public.graph_search('movie_recommendation', 'Movie', 'name', 'ocean') AS rows
    UNWIND rows as row
    MATCH (v)
    WHERE id(v)=row.id
    RETURN v, row.rank, row.similarity
$$) AS (v agtype, rank agtype, similarity agtype);

-- result
                                                       v                                                       |        rank         |     similarity      
---------------------------------------------------------------------------------------------------------------+---------------------+---------------------
 {"id": 1125899906842629, "label": "Movie", "properties": {"id": "movie5", "name": "Oceans Thirteen"}}::vertex | 0.06079271::numeric | 0.29411766::numeric
 {"id": 1125899906842628, "label": "Movie", "properties": {"id": "movie4", "name": "Army of Thieves"}}::vertex | 0::numeric          | 0.04761905::numeric
(2 rows)