How to return vertices from different graphs on a single query with ApacheAGE?

Question

I wanted to retrieve all the vertices from two different graphs I've created (one is called family_tree and the other is taxonomy_biology) and used the following command to do so :

SELECT * FROM cypher ('family_tree' AND 'taxonomy_biology', $$
MATCH (v)
RETURN v
$$) as (vertex agtype);

But then, the terminal returns this error :

ERROR:  invalid input syntax for type boolean: "family_tree"
LINE 1: SELECT * FROM cypher('family_tree' AND 'taxonomy_biology', $...

Guessing how ApacheAGE works, it is going to search all the available graphs and return true if there is an available graph with the same name or false if it doesn't. Yet, passing two graphs that do exist will cause an error.

By the way, typing the same query, but for each graph, returned this :

SELECT * FROM cypher('taxonomy_biology', $$
MATCH (v)
RETURN v
$$) as (VERTEX agtype);
                                         vertex
-----------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Kingdom", "properties": {"name": "Animalia"}}::vertex
 {"id": 1125899906842625, "label": "Phylum", "properties": {"name": "Cnidaria"}}::vertex
(2 rows)

SELECT * FROM cypher('family_tree', $$                                                                                                                      MATCH (v)
RETURN v
$$) as (VERTEX agtype);
                                                                                                       vertex

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Person", "properties": {"name": "Louis", "titles": ["Emperor of the Carolingian Empire", "King of the Franks", "King of Aquitaine"], "year_born": 778, "year_died": 840}}::vertex
 {"id": 844424930131970, "label": "Person", "properties": {"name": "Hildegard", "titles": ["Frankish queen consort"], "year_born": 754, "year_died": 783}}::vertex
 {"id": 844424930131971, "label": "Person", "properties": {"name": "Charlemagne", "titles": ["Holy Roman Emperor", "King of the Franks", "King of the Lombards"], "year_born": 747, "year_died": 814}}::vertex
(3 rows)

So, how can I return vertices from both of these graphs in a single query?

Answer 1

The UNION clause should work just fine, and another alternative would be using a JOIN clause, just like an example in the Apache docs

In your case, the code should be something like this using UNION:

SELECT * 
FROM cypher ('family_tree', $$
    MATCH (v)
    RETURN v
$$) as (vertex agtype)
UNION 
SELECT * 
FROM cypher ('taxonomy_biology', $$
    MATCH (v)
    RETURN v
$$) as (vertex agtype);

Answer 2

The query returns an error because the syntax for the cypher() function doesn't allow combination of two graphs.

To query multiple graphs, the JOIN and ON clauses are used according to the AGE documentation.

Answer 3

After a table has been returned from AGE, it is the same as any Postgress table. You can use all the operations which is available on Postgres, like Join, Union, Intersect, and Except. Official Doc

This code will work for your purpose.

SELECT * FROM cypher ('family_tree', $$
    MATCH (v)
    RETURN v
$$) as (vertex agtype)
JOIN 
SELECT * FROM cypher ('taxonomy_biology', $$
    MATCH (v)
    RETURN v
$$) as (vertex agtype);

Answer 4

According to the documentation, you can query multiple graphs using the JOIN clause. For example, you can return vertices from family_tree and taxonomy_biology with the following query:

SELECT family_tree, taxonomy_biology
FROM cypher('family_tree', $$
MATCH (v)
RETURN v
$$) as family_tree(vertex agtype)
JOIN cypher('taxonomy_biology', $$
MATCH (n)
RETURN n
$$) as taxonomy_biology(vertex agtype)
ON family_tree = taxonomy_biology;

Answer 5

You cannot make queries on two graphs using cypher queries. However, a normal SQL query will visit this documentation for more information.

Answer 6

you need to modify the query accordingly

SELECT * FROM cypher('family_tree', $$
MATCH (v)
RETURN v
$$) as (vertex agtype)
UNION ALL
SELECT * FROM cypher('taxonomy_biology', $$
MATCH (v)
RETURN v
$$) as (vertex agtype);

Answer 7

No, unfortunately, you cannot use the cypher function to query multiple graphs in a single query like that. The first argument to the cypher function must be a single graph name, not a list of graph names. A possible solution as provided by Wendel would be using the UNION clause.

Another approach you could try is to write a custom function or stored procedure that retrieves all the vertices from both graphs and returns them as a single result set. For eg:

CREATE OR REPLACE FUNCTION get_all_vertices()
RETURNS TABLE(vertex agtype)
LANGUAGE plpgsql
AS $BODY$
BEGIN
    LOAD 'age';
    SET search_path TO ag_catalog;

    RETURN QUERY
        SELECT * FROM cypher('family_tree', $$ MATCH (n) RETURN n $$) as (n agtype)
        UNION
        SELECT * FROM cypher('taxonomy_biology', $$ MATCH (n) RETURN n $$) as (n agtype);
END
$BODY$;

Once you have created this function, you can use it in a SELECT statement to retrieve all the vertices from both graphs using:

SELECT * FROM get_all_vertices();