Is agtype like json? Casting agtype to json - list of vertices or edges?

[jccampagne]

Is agtype like json? as suggested by the documentation (https://age.apache.org/age-manual/master/intro/types.html#data-types-an-introduction-to-agtype )?

AGE uses a custom data type called agtype, which is the only data type returned by AGE. Agtype is a superset of Json and a custom implementation of JsonB.

That suggests that we can use PostgreSQL's json capabilities on the Cypher result.

Using the documentation example ( https://age.apache.org/age-manual/master/functions/list_functions.html#data-setup ), we can do this:

SELECT a, b ->> 1 from cypher('graph_name', $$
	MATCH (a)
	WHERE a.name = 'Alice'
	RETURN keys(a), keys(a)
$$) as (a agtype, b agtype);

            a            | ?column? 
-------------------------+----------
 ["age", "eyes", "name"] | eyes

(using the ->> operator as described here https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-OP-TABLE )

However, trying to cast the result to json fails:

SELECT a, cast(b as json) from cypher('graph_name', $$
	MATCH (a)
	WHERE a.name = 'Alice'
	RETURN ag_catalog.agtype_out(a), ag_catalog.agtype_out(a)
$$) as (a agtype, b agtype);

errors with:

psql:sql/bug.sql:39: ERROR:  cannot cast type agtype to json
LINE 1: SELECT a, cast(b as json) from cypher('graph_name', $$

Trying to return values as TEXT:

SELECT a, cast(b as json) from cypher('graph_name', $$
	MATCH (a)
	WHERE a.name = 'Alice'
	RETURN ag_catalog.agtype_out(a), ag_catalog.agtype_out(a)
$$) as (a text, b text);

will fail also:

psql:sql/bug.sql:39: ERROR:  invalid input syntax for type json
DETAIL:  Expected end of input, but found ":".
CONTEXT:  JSON data, line 1: ...: {"age": 38, "eyes": "brown", "name": "Alice"}}:...

Using to_json() works, but does not do what we want (suggested here #1225 (comment) ):

SELECT a, to_json(b) from cypher('graph_name', $$
	MATCH (a)
	WHERE a.name = 'Alice'
	RETURN ag_catalog.agtype_out(a), ag_catalog.agtype_out(a)
$$) as (a text, b text);

results in:

                                                        a                                                        |                                                               to_json                                                               
-----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Person", "properties": {"age": 38, "eyes": "brown", "name": "Alice"}}::vertex | "{\"id\": 844424930131969, \"label\": \"Person\", \"properties\": {\"age\": 38, \"eyes\": \"brown\", \"name\": \"Alice\"}}::vertex"
(1 row)

It returns a string in JSON, not an object in JSON, moreover there's ::vertex in the output value. This is what is causing the error when trying cast(... as json)

This is related to this issue #1225 (comment)
But that issue was closed already when I commented on it - not sure it's visible.

[MuhammadTahaNaveed]

@jccampagne PR #2075 has been merged to address this issue. You can test it by pulling the latest dev docker image dev_snapshot_master or by building master branch.

issue1996=# SELECT * FROM cypher('issue1996', $$ CREATE (a:NODE {key1: "prop1", key2:"prop2"}) $$) as (a agtype);
 a 
---
(0 rows)

issue1996=# SELECT * FROM cypher('issue1996', $$ MATCH (a) return a$$) as (a json);
                                             a                                              
--------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "NODE", "properties": {"key1": "prop1", "key2": "prop2"}}
(1 row)
issue1996=# SELECT cast(a as json) FROM cypher('issue1996', $$ MATCH (a) return a$$) as (a agtype);
                                             a                                              
--------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "NODE", "properties": {"key1": "prop1", "key2": "prop2"}}
(1 row)

issue1996=# SELECT pg_typeof(a) FROM cypher('issue1996', $$ MATCH (a) return a$$) as (a json);
 pg_typeof 
-----------
 json
(1 row)