[Data Casting] 'agtype' Casting

[cho2hhun]

Problem and My comments

• 'agtype' cannot cast to other Data Type

1. 'agtype' cannot cast to string like 'text'. It could only cast to 'varchar'

• the error for casting to 'text from 'agtype' is like that

ERROR:  agtype argument must resolve to a scalar value

actually it don't matter to casting other string data type. but I Think the user who tring to use AGE initially could confused by cating 'agtype' to string data type, and it could takes lots of time to recognize that 'agtype' can only cast to 'varchar' among string data type.

3. 'agtype' cannot cast to 'json' or 'jsonb', it is casted to json or jsonb but only written as one string for objects in brace({})

• Of course 'agtype' could extract the objects values from agtype column like select json_key ->> 'json_value' like 'jsonb' in PG but The matter is that it is read as string in Python, Java enviroments. So I couldn't help casting string(from agtype) to json in Python, Java enviroments.
• I've tried many PG functions which convert json data type like 'to_json()', 'to_jsonb()', but it is only read just ONE STRING
• the method that I've devised was first casting to 'varchar' string and use regular expression to get rid off unnecessary escape, and then casting to jsonb using 'to_jsonb()' function the problem is that we've got to face one more unnecessary casting ; 'varchar' for casting to json, so compare to json casting in Python Java, the method i mentioned above is more efficient, but still have one more unnecessary casting. So, If you guys knows any casting method for agtype to json then please let me know and share it!

Environment (please complete the following information):

• Version: 13

[Allison-E]

Hi, @cho2hhun. Please tell me how I can reproduce the errors you got.

[KamleshKumar427]

Your concern looks right that the user who tried to use AGE initially could confused by casting 'agtype' to string data type, and it could takes lots of time to recognize that 'agtype' can only cast to 'varchar' among string data type.

But most of the casting methods are given in the documentation, I don't think there is any specific method to cast agtype to json.

https://age.apache.org/age-manual/master/intro/types.html

I hope this helps

[cho2hhun]

Hi @Allison-E I've tried various Code but Problems are like that

1. agtype only possible to cast as 'varchar' for string Data, not 'text' etc
2. actually agtype is normally used to treat 'key-value' data like 'json', 'jsonb' type, but cannot parsing as 'key-value' data in python, Java enviroments..... only parsed as string like "{a : 1, b : 2}" not like {a : 1, b : 2} indeed in PG enviroment, it doesn't matter to use agtype because it can be extracted 'key' and 'value' like 'jsonb'. So I think someone have to control it to possible parse in other enviroment and to cast into Other data type like 'text'

[cho2hhun]

@KamleshKumar427 Thank you for your advice!

I'de read many times the document that you linked, But in that Document, I Think There are problems also :(

1. First The Cypher 'List' only printed 'agtype' and with that result, there are nothing to do in select query, in a nutshell the 'List' that extracted from Cypher doesn't need and play a role as a list or array For example, to use list that extracted from Cypher, first we may cast it into 'varchar', second we need to cast it into 'array' With the example query of that document, the query describe extracting list

SELECT *
FROM cypher('graph_name', $$
    WITH [null] as lst
    RETURN lst
$$) AS (lst agtype);

if u run this query, then list column 'lst' will be extracted. but to utilize in SQL query, it is nothing....... it is not array or list even not any string!!!! So to use this, maybe need to two-times casting like below

SELECT string_to_array(lst::varchar, ',')
FROM cypher('graph_name', $$
    WITH [null] as lst
    RETURN lst
$$) AS (lst agtype);

actually to use list that extracted cypher need two-times casting for using in SQL, SO I dont think it is efficient..... Hence, automatical 'agtype' casting to JSON, ARRAY, any string is really needed

2. Second the query that descirbed in document you sent is like that

SELECT *
FROM cypher('graph_name', $$
    WITH {listKey: [{inner: 'Map1'}, {inner: 'Map2'}], mapKey: {i: 0}} as m
    RETURN m
$$) AS (m agtype);

but as I mentioned, It has same problem ; two-times casting after extracting the JSON data from Cypher, then we might cast it into 'varchar' and 'json'(actually casting to proper json from agtype is complex.,....you guys need use regular expression also to properly parse to json...., ), and It's cost is too high to cast two-times...

P.S. in the case of agtype that formed Json, I normally use the below method replace(regexp_replace(**col**::varchar, '::[a-zA-Z0-9\[\]]+', '', 'g'), E'\"', '"')::jsonb; I've researched for casting agtype into other datatype, but until now, It is at least fine to cast json....

So I think agtype need many improvements in casting problem.

[jrgemignani]

@cho2hhun Your issue is with agtype after it is returned from a cypher command and then casting it into other types?

[cho2hhun]

@jrgemignani Actually The matter ws it couldn't cast JSON, because most of the agtype is used to key-value, but cannot casting it to proper Key-value JSON(B) type... it is only casted just one value if I casting it to JSON

If I cast it to JSON after it is returned from cyphper like below, select to_jsonb(a) from cypher('car', $$ match (a : vt_car) return a $$) as (a varchar);

then, its results are like below

"{\"id\": 1125899906842625, \"label\": \"vt_car\", \"properties\": {\"carline\": \"ENCLAVE FWD\", \"mfr_name\": \"General Motors\", \"car_division\": \"Buick\"}}::vertex"

In a nutshell, It is casted as one string value with converting to JSONB

[cho2hhun]

Of course I Know that it is possible to use other PG's JSONB Value indexing like ->>, but the problem is that agtype cannot be parsed as key-value in Java, Python enviroments. So want to be cast to JSON or JSONB to parse it properly in advance

[jrgemignani]

@cho2hhun Maybe this will be helpful -

psql-15.4-5432-pgsql=# SELECT * FROM cypher('graph_name', $$ WITH [null] as lst RETURN ag_catalog.agtype_out(lst) $$) AS (result text);
 result
--------
 [null]
(1 row)

psql-15.4-5432-pgsql=# SELECT pg_typeof(result) FROM cypher('graph_name', $$ WITH [null] as lst RETURN ag_catalog.agtype_out(lst) $$) AS (result text);
 pg_typeof
-----------
 text
(1 row)

psql-15.4-5432-pgsql=# SELECT to_json(result) FROM cypher('graph_name', $$ WITH [null] as lst RETURN ag_catalog.agtype_out(lst) $$) AS (result text);
 to_json
----------
 "[null]"
(1 row)

psql-15.4-5432-pgsql=# SELECT to_jsonb(result) FROM cypher('graph_name', $$ WITH [null] as lst RETURN ag_catalog.agtype_out(lst) $$) AS (result text);
 to_jsonb
----------
 "[null]"
(1 row)

psql-15.4-5432-pgsql=#

[jrgemignani]

@cho2hhun When one outputs something from the cypher() function one needs to specify what type it is, as you already know.

For the case of agtype, it generally has to be the type agtype. This is because PG doesn't know how to generally cast agtype to certain other types. Especially, since agtype is a composite and can be many different types -

psql-15.4-5432-pgsql=# SELECT * FROM cypher('graph_name', $$ WITH [1,2,3] as lst RETURN lst[1] $$) AS (result agtype);
 result
--------
 2
(1 row)

psql-15.4-5432-pgsql=# SELECT * FROM cypher('graph_name', $$ WITH [1,2,3] as lst RETURN lst[1] $$) AS (result integer);
 result
--------
      2
(1 row)

psql-15.4-5432-pgsql=# SELECT * FROM cypher('graph_name', $$ WITH [1,2,3] as lst RETURN lst $$) AS (result agtype);
  result
-----------
 [1, 2, 3]
(1 row)

psql-15.4-5432-pgsql=# SELECT * FROM cypher('graph_name', $$ WITH [1,2,3] as lst RETURN lst $$) AS (result _int4);
 result
---------
 {1,2,3}
(1 row)

However, if inside the cypher command you convert agtype to something else, say a cstring, then you can utilize PG's casts to generate something else -

psql-15.4-5432-pgsql=# SELECT to_json(result) FROM cypher('graph_name', $$ WITH [1,2,3] as lst RETURN ag_catalog.agtype_out(lst) $$) AS (result text);
   to_json
-------------
 "[1, 2, 3]"
(1 row)

psql-15.4-5432-pgsql=# SELECT pg_typeof(to_json(result)) FROM cypher('graph_name', $$ WITH [1,2,3] as lst RETURN ag_catalog.agtype_out(lst) $$) AS (result text);
 pg_typeof
-----------
 json
(1 row)

psql-15.4-5432-pgsql=#

It would probably be a good idea for our team to add in some additional casts to make this easier for others.

Hope this is helpful.

[github-actions[bot]]

This issue is stale because it has been open 45 days with no activity. Remove "Abondoned" label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for further 7 days with no activity.

[jccampagne]

Hi, I am also trying to do something with list values of type agtype in SQL (outside Cypher).

@jrgemignani in your example:

psql-15.4-5432-pgsql=# SELECT to_json(result) FROM cypher('graph_name', $$
WITH [1,2,3] as lst RETURN ag_catalog.agtype_out(lst)
$$) AS (result text);
   to_json
-------------
 "[1, 2, 3]"
(1 row)

psql-15.4-5432-pgsql=# SELECT pg_typeof(to_json(result)) FROM cypher('graph_name', $$ 
WITH [1,2,3] as lst RETURN ag_catalog.agtype_out(lst)
$$) AS (result text);
 pg_typeof
-----------
 json
(1 row)

The conversion is not what we expect. It's indeed JSON, but a string in JSON...

For comparison, a JSON LIST of integers (not one string) in postgres is:

jc=# select '[1,2,3]'::json ;
  json   
---------
 [1,2,3]
(1 row)

jc=# select pg_typeof('[1,2,3]'::json) ;
 pg_typeof 
-----------
 json
(1 row)

As per the documentation here https://www.postgresql.org/docs/current/functions-json.html

Note the lack of quote (") in the result.

It's not clear what one can do with list values. Especially with list of edges or vertices.

Also I could not find information about this function ag_catalog.agtype_out() in the documentation. What does it do?

I managed to make the above example work as intended with cast(... as ...):

# SELECT cast(result as json) FROM cypher('graph_name', $$
WITH [1,2,3] as lst RETURN ag_catalog.agtype_out(lst)
$$) AS (result text);

  result   
-----------
 [1, 2, 3]
(1 row)

It seems to work on this simple example, but if one tries to same cast on a list of vertices... the parsing fails because of the ::vertex at the end...

For example, the documentation example (https://age.apache.org/age-manual/master/functions/list_functions.html#data-setup) 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 varchar, b varchar);

will cause this error:

psql:sql/graph3.sql:48: 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"}}:...

with to_json() it will succeed, but as mentioned earlier, it's not what we want:

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 varchar, b varchar);

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"

Here, we clearly see the ::vertex preventing the cast to work...

Note that, if the result is a list of scalars, the returned value can work like jsonb; for example this will work:

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