Search code examples
databasepostgresqlgraph-databasesapache-age

How to Access Index of Array in the properties of AGE Vertex When passed to PG_Catalog Function

I have created a vertex student:

test=# SELECT * FROM cypher('graph', $$
CREATE (s:student{name:"Muneeb", courses: ["OOP", "DS", "Programming", "Android"]})
 RETURN s
$$) as (student agtype);

----------------------------------------------------------------------------------------

     student    
----------------------------------------------------------------------------------------     

{"id": 3377699720527873, "label": "student", "properties": {"name": "Muneeb", "courses": ["OOP", "DS", "Programming", "Android"]}}::vertex
    (1 row)

It contains courses in its property, which has list of courses in it.

I have created a function that should return 2nd index of the List, in PG_Catalog.

test=# CREATE OR REPLACE FUNCTION pg_catalog.second_in_list(list agtype)
RETURNS agtype AS $$
BEGIN
   return list[1];
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION

When I call this function:

test=# SELECT * FROM cypher('graph', $$
Match (s:student) Return pg_catalog.second_in_list(s.courses)
$$) as (student agtype);

I am getting this error:

ERROR:  cannot subscript type agtype because it is not an array
CONTEXT:  SQL statement "SELECT list[1]"
PL/pgSQL function second_in_list(agtype) line 3 at RETURN

I have tried passing list agtype[] as argument to pg_catalog function but it also not work.

Solution

  • You can achieve that through accessing the agtype list using the -> operator

    test=# CREATE OR REPLACE FUNCTION pg_catalog.second_in_list(list agtype)
RETURNS agtype AS $$
BEGIN
   return list->1;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION

    While keeping rest of your code as it

    In AGE, -> is the operator used to access elements of an AGE array by index or keys of an AGE JSON object.

    The reason your function doesn't work is that it tries to use the subscript operator [] to access the first element (index 1) of an agtype object, which is not an array. That operation is invalid and leads to the error you saw.

    In contrast, using the -> operator, which is used to navigate the graph and retrieve values from a vertex or an edge property in Apache AGE. In this case, when you call list->1, it returns the value associated with the key 1 from the agtype object. The -> operator is the correct operator to use when you want to retrieve a value from a dictionary-like structure like an agtype.

    So, in summary, -> is the right operator to retrieve a value from a dictionary-like structure, as long as that agtype is non-array structure, which is invalid to use [] at SQL query.

    References: